Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies


ADVERTISEMENT

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


Code:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))



Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 2 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))


Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 3 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Convert Var Char Field To Date Field

Sep 27, 2005

Hi,

I need to convert a var char field to date field (DD/MM/YYYY)

Current convertion format:
CAST(Report_Date as DATETIME)

How can i convert to Date field with date format of DD/MM/YYYY?

Thanks.

Onn Onn

View 2 Replies View Related

Insert Previous Record In A Blank Or A Null Field

Nov 29, 2000

Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:

ONETWO
a1
2
3
b1
2
3

Would want:

ONETWO
a1
a2
a3
b1
b2
b3

View 5 Replies View Related

How To Pass Null Value To The Database If A Form Field Is Empty?

May 31, 2004

How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example:

Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection)
CmdUpdate.CommandType = CommandType.StoredProcedure

CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9)
CmdUpdate.Parameters("@User_fax").Value = fax.Text()

...

strConnection.open()
CmdUpdate.ExecuteNonQuery
strConnection.close()



And, the stored procedure inside Sql server:

USE market1
GO
ALTER PROC Form2_NewUser
@User_id bigint, @User_fax char(9),...

AS

SET NOCOUNT ON
UPDATE Users
SET User_fax = @User_fax, ...

WHERE User_id = @User_id
SET NOCOUNT OFF
GO



Thank you,
Cesar

View 4 Replies View Related

Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL

Jan 13, 2006

Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!

View 7 Replies View Related

VALUE IN FIELD NULL OR VOID STRING!?

Jul 23, 2005

Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter

View 3 Replies View Related

How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?

Nov 13, 2007

Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).

I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.

The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?

Please advise. Thanks.

Best.
K7

View 1 Replies View Related

How Do I Convert As/400 Time Field Being Seen As Dt_I8 To String

Apr 4, 2008



I am struggling getting a time field from as/400 into SQL 200 using SSIS. The time field for some reason is being seen as dt_I8, And every type of conversion I try to do with it, it gives me an error about truncation. Example of my time field 12.35.00

How do you convert time field from as/400 which sql is seeing as DT_I8 to a string so that I can combine it with a date field to put it into a database timestamp field.
Stacy

View 10 Replies View Related

Convert Int Data Into A Multi-field String

Jan 9, 2007

Trying to get a multi field grouping to print.

Client + Req Number + Status

Client and status are nvarchar fields

Req Number is an int.

Keep getting # Error for this field on the report. on the report.

It works in Access - should it be this difficult?

Thanks!

Terry

View 4 Replies View Related

How To Use BCP With Null Date Field

May 28, 2008

My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.

Is there a way to get BCP to accept empty date fields?

View 3 Replies View Related

Doing A 'between' On A NULL In A Date Field

Mar 29, 2007

Hi everyone,

I have two tables - caseinfo and steps.

Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.

Steps shows the steps the case has gone through and the dates of those particular steps.

I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.

If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).

Does anybody have any ideas?

Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.

Thank you for reading.


--- create sample data
set dateformat ymd

declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)

insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'

declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)

insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'

--- the main idea (with the wrong results)

select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate

--- expected result

declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)

insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'

select *
from @expresult

View 1 Replies View Related

Date Field Cannot Be Null!

May 30, 2006

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

View 6 Replies View Related

How To Convert Date-field To Date With Time Zone

Jul 4, 2014

I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.

ALTER PROCEDURE [dbo].[ByDateRange]

@Feed VARCHAR(50),

@CreatedDate DATETIME

select * from Date_table where Create_TM = @CreatedDate

View 1 Replies View Related

How To Insert Null Into Date Field?

Dec 11, 2007

hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount,  CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record:  (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null.  How could I do it?Please advice!    

View 5 Replies View Related

How To Convert String To Upper Case Using Field's Formula Property

Jul 20, 2005

Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max

View 3 Replies View Related

Urgent : Error When Convert Date Field With BCP.

Jun 18, 1999

I have a text file like this :

K045|13/03/1997

When I upload this text file to SQL Server 6.5, with the following fields :

Code Chraracter 4
Tdate Smalldatetime

I use BCP to upload this text file to SQL tables, but I got a wrong date,
like :

Text file :
K045|13/03/1997

SQL Tables data :
K045 Nov 19 1935 7:00PM

Can anyone on the net can help me ?

Thank's

View 1 Replies View Related

Convert Datetime Field To Display Date

Aug 9, 2006

I have a field that I would like to only display the date as mm/dd/yyyy. Current field shows mm/dd/yyyy hh:mm:ss AM.

View 10 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

Check For Null In Repeater Date Field

Jan 27, 2008

I'm about ready to pull my hair out.  I have a repeater control, and a date field.  If the field is a valid date, I'll use it to calculate and display the person's age.  Otherwise, I want to display "N/A".  My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date.  It makes sense for these records to leave the date Null.  This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then        Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)        Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label)        Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow)        If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then            LabelIcon.Visible = True        End If        If inmate.DATE_OF_BIRTH Is DBNull.Value Then            If IsDate(inmate.DATE_OF_BIRTH) Then                LblAge.Text = "Age: N/A"            Else                LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH)            End If        End If    End IfEnd Sub How can I resolve this?Diane 

View 7 Replies View Related

Setting A Date/time Field To Null

Mar 2, 2005

Hello!

I am using a Ms-Access DS which is accessed by a website's server-side scripts.

What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.

How can I accomplish this?

Thank you!

Dave

View 2 Replies View Related

How To Check Null Or '' In Date Field In Expression And ...............

Aug 16, 2006

I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns

The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'

I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'.
In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''





View 6 Replies View Related

Transact SQL :: Convert Local Date Time Field To GMT?

Jul 28, 2015

I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]

[URL]

View 5 Replies View Related

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?

Aug 29, 2007

Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View 9 Replies View Related

SQL Date Field To String

Mar 17, 2008

I am running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:

-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error.
Any suggestions? I would prefer not to have to change every stored procedure we have which may do this.
Thanks for any help.
MaryKate

View 6 Replies View Related

Convert Field From VarChar To Int With Speical Characters In Field

Aug 29, 2007

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

View 2 Replies View Related

Problem With Adding A Date Field To A Database When It Is Null

Jun 21, 2005

I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database:  '---------------------------------------------         ' name: Button_Click()         '---------------------------------------------        Sub Button_Click( s As Object, e As EventArgs )                        Dim strConnect As String              Dim objConnect As SQLConnection              Dim strInsert As String             Dim cmdInsert As SqlCommand            
             'Get connection string from Web.Config             strConnect = ConfigurationSettings.AppSettings("ConnectionString")
             objConnect = New SqlConnection(strConnect)                            strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )"             cmdInsert = New SqlCommand( strInsert, objConnect)             cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value )             cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text )             cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text )             cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text )
             objConnect.Open()             cmdINsert.ExecuteNonQuery()             objConnect.Close()                                  'Display the results "page"                  DisplayResults() 
        End Sub Here's the code for the form:         <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">         <div align="center">    <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">     <tr>      <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>      <td>&nbsp;</td>     </tr>              <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Client's Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <asp:dropdownlist id="dropClient" runat="server" />        </p>      </td>     </tr>    <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Domain Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />               </p>      </td>                         <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Registrar:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />       </p>      </td>     </tr>     <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Expiration Date:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />       </p>      </td>     </tr>               
     <TR>      <TD>      </TD>      <TD align="center">       <asp:Button                            Text="Submit"                            OnClick="Button_Click"                            Runat="Server" />      </TD>     </TR>    </table>  </form>  </div>

View 1 Replies View Related

Extract Date Part Of Timestamp Column And Convert It To Char Field

Nov 28, 2012

Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?

The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.

convert(char(15), process_date,112) as process_dt

View 3 Replies View Related

In A String Field Identify Not Date Records?

Apr 15, 2008

Hi All,

I have a table called Table1 with a field called DateString with a nvarchar type. The records are like this;

DateString
1/11/2007
9/27/2006
2/22/2008
1/10/2007
11/13/2007
8/21/2007
5/23/2007
2/16/2007
12345678900
11/20/2006

It may not have date records which are just string like in this example is 12345678900. I need a SQL script which can identify those records which are not of date type. Please help.

Thanks,

Zee

Thanks a million..

Zee

View 4 Replies View Related

Using Date Field In Search String To Bind To Repeater

Apr 5, 2004

i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:


sub page_load(sender as object, e as eventargs)
dtmDate=DateTime.Now.ToString("M")
con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")

cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)

con.open()
dtrBday = cmd.ExecuteReader()

rptBday.DataSource=dtrBday
rptBday.DataBind()
dtrBday.Close()



I know this isnt quite right. i get errors when it hits my repeater.

the error i am getting is :Syntax error converting string to smalldatetime data type.
if someone could give me a push in the right direction here it would be greatly appreciated.

View 2 Replies View Related

Convert To Null Where Data Is Blank

Apr 17, 2008



Hi, where I return the address details, in some of the fields they are blank, I need these blank fields as nulls, how do i do that ?

Thanks





Code Snippet
SELECT
a.[Account Name],
a.[Line Address 1],
a.[Line Address 2],
a.[Line Address 3],
a.[Line Address 4], b.*
FROM (
SELECT
abCUSA AS Company,
abCUSB AS Account,
0 AS DeliverTo,
abcnam AS [Account Name],
abcad1 AS [Line Address 1],
abcad2 AS [Line Address 2],
abcad3 AS [Line Address 3],
abcad4 AS [Line Address 4],
CONVERT(VARCHAR(20), abCUSA) + CAST(abCUSB AS VARCHAR(20)) AS [Customer Number]
FROM
LIVEAS400.S65C422B.WRFDTA.PARNADR
) a
Inner JOIN
(
SELECT
SLDATE as Date,
SLCUSA as [Company Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLTREF as [Trans Reference],
SLCNAM as [Deliver To Name],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) AS [Customer Number],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) + CAST(SLDELN AS VARCHAR(20)) AS [Full Deliver To Number],
-- SLTRT2 as [Trans type],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total,
SLDESC as [Trans Description]
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
) b ON a.[Customer Number] = b.[Customer Number]

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved