CONVERSION OF THE STRING TO DATE 4

[cristi22]

Hello All -

Really appreciate your help!!!

DB2 UDB 9

The work_period field is a CHAR "199802" CCYYMM.

I was trying to run the following:

SELECT 
	,MEMBER_WORK_PERIOD.MEMBER_KEY 
	,MEMBER_WORK_PERIOD.MVPOS 
	,MEMBER_WORK_PERIOD.WORK_PERIOD
	,MEMBER_WORK_PERIOD.EMPLOYER_NO

FROM   
	MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE
MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY AND  FUND='L522P' AND APP='HW'  
[red]
and date ( left( MEMBER_WORK_PERIOD.WORK_PERIOD,4) || '-' || right( MEMBER_WORK_PERIOD.WORK_PERIOD,2) || '-' || '01') < date('2005-07-01') 
and date ( left( MEMBER_WORK_PERIOD.WORK_PERIOD,4) || '-' || right( MEMBER_WORK_PERIOD.WORK_PERIOD,2) || '-' || '01') > date('2004-06-01')[/red]

& here is the message:

SQL0440N No authorized routine named "DATE" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

THANKS!!!

 

[blom0344]

What about:

SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4) AND

SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)

This would be independant of the length of the CHAR field!

Ties Blom
Information analyst

 

[juliane26]

I used translate when finding out about the last 4 days before:
JHMT being ddmmyyyy in char(8):
AND T.JHMT <= Translate('ABCDEFGH',char(CURRENT DATE - 4 DAYS),'ABCD-EF-GH') )

So for you having yyyymm check on:
MEMBER_WORK_PERIOD.WORK_PERIOD > translate ('ABCDEF','2005-07-01','ABCD-EF-01') ...

I prefer converting only one time the date to a string than every string in a resultset to a date.

Still, what are you actually trying to find with your SQL?
< date('2005-07-01') AND > date('2004-06-01') comparing with the same work period - isn't that always empty, since both cannot be true having only dates of the format YYYY-MM-01 and you have an AND ??

Juliane

 

[MarcLodge]

Try:

SELECT 
    ,MEMBER_WORK_PERIOD.MEMBER_KEY 
    ,MEMBER_WORK_PERIOD.MVPOS 
    ,MEMBER_WORK_PERIOD.WORK_PERIOD
    ,MEMBER_WORK_PERIOD.EMPLOYER_NO

FROM   
    MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE
MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY AND  FUND='L522P' AND APP='HW'  

AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
         SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') < DATE('2005-07-01') 
AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
         SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')

Hope this helps
Marc

 

[PruSQLer]

Right, if you combine what Ties wrote with

Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)) < Year('2005-7-1')

Or
(Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)) = Year('2005-7-1') and

Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)) < Month('2005-7-1') )

It should work fine.

 

[cristi22]

Thanks all for your help!!!

I am trying to extract records for the period from
07/01/2004 thru 06/30/2005.




Thanks a lot for all your help

 

[cristi22]

 AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') < DATE('2005-07-01') 

	AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')

WORKED!!!!

 

[cristi22]

Now I have to group it by qtr, any suggestions?
I REALLY APPRECIATE YOUR HELP!!!!
Cristi!

SELECT 
CASE  QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01'))
	WHEN  = 1 THEN "Q1"  WHEN  = 2 THEN "Q2" WHEN 3 THEN "Q3" WHEN 4 THEN "Q4" END) 

	,MEMBER_WORK_PERIOD.MEMBER_KEY 
	
	, SUM( MEMBER_WORK_PERIOD_FUND.QUANTITY )
	,SUM ( MEMBER_WORK_PERIOD_FUND.RATE    )

	,MEMBER_WORK_PERIOD_FUND.APP      
	,MEMBER_WORK_PERIOD_FUND.FUND   

FROM   
	MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD

GROUP BY
	,MEMBER_WORK_PERIOD.EMPLOYER_NO
 	
	,MEMBER_WORK_PERIOD.MEMBER_KEY 

	CASE  QUARTER(DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01') )
	WHEN  = 1 THEN "Q1"  WHEN  = 2 THEN "Q2" WHEN 3 THEN "Q3" WHEN 4 THEN "Q4" END

	,MEMBER_WORK_PERIOD_FUND.APP      
	,MEMBER_WORK_PERIOD_FUND.FUND   

WHERE
	MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
		AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY AND  FUND='L522P' AND APP='HW'  

	 AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') < DATE('2005-07-01') 

	AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
        	 SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')

ELECT ( CASE QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')) WHEN = 1 THEN "Q1" WHEN = 2 THEN "Q2" WHEN 3 THEN "Q3" WHEN 4 THEN "Q4" END) ) ,MEMBER_WORK_PERIOD.MEMBER_KEY , SUM( MEMBER_WORK_PERIOD_FUND.QUANTITY ) ,SUM ( MEMBER_WORK_PERIOD_FUND.RATE ) ,MEMBER_WORK_PERIOD_FUND.APP ,MEMBER_WORK_PERIOD_FUND.FUND FROM MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD GROUP BY ,MEMBER_WORK_PERIOD.EMPLOYER_NO ,MEMBER_WORK_PERIOD.MEMBER_KEY CASE QUARTER(DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01') ) WHEN = 1 THEN "Q1" WHEN = 2 THEN "Q2" WHEN 3 THEN "Q3" WHEN 4 THEN "Q4" END ,MEMBER_WORK_PERIOD_FUND.APP ,MEMBER_WORK_PERIOD_FUND.FUND WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY AND FUND='L522P' AND APP='HW' AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') < DATE('2005-07-01') AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01') AND MEMBER_WORK_PERIOD.MEMBER_KEY='010365411'
SQL0104N An unexpected token "DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIO" was
found following "CT ( CASE QUARTER (". Expected tokens may include:
"<space>". SQLSTATE=42601

ERROR:
=====
[RED]
SQL0104N An unexpected token "DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIO" was found following "CT ( CASE QUARTER (". Expected tokens may include: "<space>".
[/RED]

 

[blom0344]

The proper order is :

SELECT
FROM
WHERE
GROUP BY
(HAVING)

The CASE construction looks wrong to me to (or is this an enhancement in UDB9?

The following should work in any case:

SELECT CASE
WHEN(QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')) = 1 
THEN 'Q1'
WHEN(QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')) = 2 
THEN 'Q2'
WHEN(QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01')) = 3 
THEN 'Q3' ELSE 'Q4' END

Ties Blom
Information analyst

 

[MarcLodge]

Hi Cristi,
If I was doing it, I'd probably do something like:

SELECT  QTR,  EMPNO, SUM(QUNTITY), SUM(RTE), APP, FUND FROM
    (
      SELECT
          CASE  QUARTER (DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'||
                      SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01')
                      WHEN= 1 THEN "Q1"WHEN= 2 THEN "Q2" WHEN 3 THEN "Q3" WHEN 4 THEN "Q4" 
          END) AS QTR 

        , MEMBER_WORK_PERIOD.EMPLOYER_NO AS EMPNO
        , MEMBER_WORK_PERIOD.MEMBER_KEY AS KEYVAL
        , MEMBER_WORK_PERIOD_FUND.QUANTITY AS QUNTITY
        , MEMBER_WORK_PERIOD_FUND.RATE  AS RTE
        , MEMBER_WORK_PERIOD_FUND.APP  AS APP
        , MEMBER_WORK_PERIOD_FUND.FUND  AS FUND
      FROM MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD

      WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS
      AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY
      AND FUND='L522P' 
      AND APP='HW'
      AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
            SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') < DATE('2005-07-01') 
      AND DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'||
            SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01') > DATE('2004-06-01') 
    )

GROUP BY EMPNO, KEYVAL, QTR APP, FUND

Hope this works/helps

Marc

 

[juliane26]

Hi Christi,

what is your input for the SQL (from..to dates)? Are you using a register or parameter like current date or is it hardcoded?

For the WHERE condition:

If it is hardcoded: why not doing your comparisons on string level:
WHERE MEMBER_WORK_PERIOD.WORK_PERIOD > '200406'
AND MEMBER_WORK_PERIOD.WORK_PERIOD < '200507'

If it is a variable:

WHERE MEMBER_WORK_PERIOD.WORK_PERIOD > translate ('ABCDEF','2004-06-01','ABCD-EF-01')
AND MEMBER_WORK_PERIOD.WORK_PERIOD < translate ('ABCDEF','2004-07-01','ABCD-EF-01')

For the quarter function:
From what I see you would like to have 1st qtr for 06/2004 - 09/2004 and 2nd for 09/2004 - 12/2004 and so on, calculating quarters for your year which is from 01/07/2004 til 30/06/2005.

First of all, in all above case statements, use the '-' instead of the '>' - that should help and I think that is what you are missing. Otherwise it is not valid, also not in Ties solution.
Like:

SELECT CASE
WHEN(QUARTER ( DATE(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)|| '-'|| 
SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)|| '-01')[u] [b][COLOR=red]-[/color red][/b] [/u]DATE('2004-06-01')) = 1 
THEN 'Q1'

Still, also here I'd try putting the function on your input values again rather than the table strings. To give you a proper suggestion since that is not trivial with pure numeric, I'd like to know whether that is a monthly report or quaterly ?
Depending on that is how generic the solution has to be and how efficient it can be.
One option which should be valid:

SELECT CASE
WHEN INT(MEMBER_WORK_PERIOD.WORK_PERIOD)-INT(TRANSLATE ('ABCDEF','2004-06-01','ABCD-EF-01')) BETWEEN 1 AND 3 OR BETWEEN 89 AND 91 THEN 'Q1'
WHEN INT(MEMBER_WORK_PERIOD.WORK_PERIOD)-INT(TRANSLATE ('ABCDEF','2004-06-01','ABCD-EF-01')) BETWEEN 4 AND 6 OR BETWEEN 92 AND 94 THEN 'Q2'
WHEN INT(MEMBER_WORK_PERIOD.WORK_PERIOD)-INT(TRANSLATE ('ABCDEF','2004-06-01','ABCD-EF-01')) BETWEEN 7 AND 9 OR BETWEEN 95 AND 97 THEN 'Q3'
ELSE 'Q4'

It works fine without string functions on the table column.
the two different numbers are always for which month we actually have, e.g. in with 2004-06-01 the difference would return 1,2,3,4,5,6,95,96,97,98,99,100, for 2004-07-01 the results would be 1,2,3,4,5,94,95,96,97,98,99,100; that way I catch all of them.

Especially for reports using 100.000 rows of data and more I always prefer having minimum string functions on the table fields and rather work with the input to make it fit(in your case the dates from .. to for your calculation).


Juliane