Select 'AS' - 2 steps ??

[rwaldron]

Hi all,
can someone please help..
I am trying to query a database DB2..

I want to get back the last 4months (120 Days) of Despatch data using the CURRENT DATE register in sysibm/sysdummy1
I am trying to compare DESPATCH_DATE with CURRENT DATE

SELECT CURRENT DATE AS TODAY ,zxdsdt as DESPATCH_DATE
FROM sysibm/sysdummy1 , mvxamodsfc/mzimhs
WHERE DESPATCH_DATE >= TODAY - 120

Error Despatch_Date not in specified tables..

I am using AS to make columns more readable.
if I leave out the WHERE I get columns returned OK as named but I need the 4month option.

Any help with the correct way to do this please..
Ray..

 

[BrianTyler]

There are two points to make.

1. You cannot use the AS COLNAME within a query. The construct is only used to identify a column in the final RESULTS TABLE.

2. You need not use sysibm/sysdummy1 as current date is a known value.

select xxxxx from mvxamodsfc/mzimhs where zxdsdt >= (current date - 120 days)

 

[rwaldron]

Ah I see,
ThX for the Reply...
What I am trying to do is a bit more complicated..
My column zxdsdt has data in the form yyyyddmm eg:20060502
I have managed to use the SUBSTR command to make this a proper date data type using the code below but I need the last 4 months of this converted data ? Can you help.
I currently get ZXDSDT returned as A column called date but when I compare this using a WHERE I get an error that Column DATE not in specified tables

SELECT
DATE((SUBSTR(CHAR(zxdsdt),1,4)
||'-'||
SUBSTR(CHAR(zxdsdt),5,2)
||'-'||
SUBSTR(CHAR(zxdsdt),7,2)))
from mvxamodsfc/mzimhs
WHERE DATE >= (current date - 120 days)

I know understand as you said before that AS is only for a final results table...I think I have 2 steps here that I need to make into 1??

Ray..

 

[MarcLodge]

Ray,
Include your date calc in the where statement. eg
SELECT
DATE((SUBSTR(CHAR(zxdsdt),1,4)
||'-'||
SUBSTR(CHAR(zxdsdt),5,2)
||'-'||
SUBSTR(CHAR(zxdsdt),7,2)))
from mvxamodsfc/mzimhs
WHERE DATE((SUBSTR(CHAR(zxdsdt),1,4)
||'-'||
SUBSTR(CHAR(zxdsdt),5,2)
||'-'||
SUBSTR(CHAR(zxdsdt),7,2)))
>= (current date - 120 days)

Marc

 

[rwaldron]

Thx very Much Marc that worked a treat.
I then encorporated the entire code into an openquery from SQL.

Now since the one code all gets run on the remote machine ( AS400 ) and then returned to SQL my Query now takes 3 Mins to run instaed of 6Mins...

Thx a Million..

Ray...

 

[ddiamond]

Is there any difference between current date and curdate()?

 

[ddiamond]

Here is another version of Marc's query that uses a sub-query to get around the field alias problem.

SELECT DESPATCH_DATE
FROM (
  SELECT                            
       DATE((SUBSTR(CHAR(zxdsdt),1,4)    
            ||'-'||                           
            SUBSTR(CHAR(zxdsdt),5,2)         
            ||'-'||                           
            SUBSTR(CHAR(zxdsdt),7,2))) AS DESPATCH_DATE       
   from mvxamodsfc/mzimhs) X      
WHERE DESPATCH_DATE >= (current date - 120 days)

Marc's query may perform faster, however.