IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 SQL usage of CASE/IF to condition out DATE parameter


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
saurabh516

New User


Joined: 27 Nov 2006
Posts: 8
Location: Bangalore

PostPosted: Wed Sep 22, 2010 9:25 pm
Reply with quote

Hi All,

Need advice and help on the beloe requirement:

Join two table based on a common condition and extract records which have been actioned 1 day before TODAY, BUT the records are not actioned on Sundays.

In other words, the qyery should have two where clauses for Monday and other remaining days.
Code:

SELECT B.Record1_2,
A.Record1_1,
A.Record2_1
FROM Table1 A,Table2 B
WHERE
A.Col1_1=B.Col1_2 AND
A.Col2_1=B.Col2_2  AND
A.Col3_1=B.Col3_2  AND
--other conditions
DATE(A.Table1_Timestamp) = DATE(CURRENT TIMESTAMP)-1 DAY
GROUP BY B.Record1_2,A.Record1_1,A.Record2_1



This works okay for all days except Monday, as we dont have any records created with A.Table1_Timestamp on Sundays and if this query runs on monday, the result set is zero. Tried using DAYOFWEEK as 1 and 2 as Case expression but syntax errors.

pls help

Thanks, Sab
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Sep 23, 2010 1:01 am
Reply with quote

Saurabh,

Can you show us your query with DAYOFWEEK

This one is not tested
Code:
and dayofweek(current date - 1 day) > 1


Sushanth
Back to top
View user's profile Send private message
saurabh516

New User


Joined: 27 Nov 2006
Posts: 8
Location: Bangalore

PostPosted: Thu Sep 23, 2010 2:47 pm
Reply with quote

sushanth bobby wrote:
Saurabh,

Can you show us your query with DAYOFWEEK

This one is not tested
Code:
and dayofweek(current date - 1 day) > 1


Sushanth


Sushanth,

This query will be run via JCl and the scheduler runs this except Sundays. So, the query needs to have a different condition on Mondays, ie -2 days.

I tried to add the following but received error :
10:16:02 [SELECT - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] [IBM][CLI Driver][DB2] SQL0104N An unexpected token "=" was found following "". Expected tokens may include: "MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601

Code:

case when dayofweek(CURRENT TIMESTAMP)=2
then (DATE(Column_TS) = (DATE(CURRENT TIMESTAMP)-2 DAY))

else (DATE(A.BColumn_TS) = (DATE(CURRENT TIMESTAMP)-1 DAY))

end 
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Sep 24, 2010 4:48 am
Reply with quote

Hi Saurabh,

Can you please mention your entire query you have executed using Case logic

Regards,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Sep 24, 2010 5:34 am
Reply with quote

Hi Saurbh,

Also just a thought to execute this query..This is Untested as i am not having access to mainframes while posting this

Can you have a union of two Selects

Code:
1st Select with where clause as
dayofweek(CURRENT TIMESTAMP)=2  and
(DATE(Column_TS) = (DATE(CURRENT TIMESTAMP)-2 DAY)
             UNION ALL
2nd select with where clause as
dayofweek(CURRENT TIMESTAMP) <> 2  and
(DATE(Column_TS) = (DATE(CURRENT TIMESTAMP)-1 DAY)


Thanks,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Sep 24, 2010 7:29 pm
Reply with quote

Also you can have above two where clause in a single query without Union with an OR clause as 1st one will execute for Monday and other for remaining days

Hope this helps

Regards,
Chandan
Back to top
View user's profile Send private message
saurabh516

New User


Joined: 27 Nov 2006
Posts: 8
Location: Bangalore

PostPosted: Mon Sep 27, 2010 5:25 pm
Reply with quote

Hi Chandan,

Thanks for your suggestion. The UNION ALL comamnd has worked okay and has given me desired results.

I have made two seperate Selects of the required conditions and tagged them together, as you suggested, with UNION all. Both these selects run and the one which satisfies the condition populates results. So, this will run on all days, including monday, and would extract required fields.

I will check the way this fetches data for the next 2 days and would let you know the results. Thanks again

Regards,Saurabh
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Need help to append a date&tsp at... DFSORT/ICETOOL 9
No new posts Using the Jobname parameter in a Qual... ABENDS & Debugging 1
Search our Forums:

Back to Top