Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 SQL usage of CASE/IF to condition out DATE parameter

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 SQL usage of CASE/IF to condition out DATE parameter
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: 1013
Location: India

PostPosted: Thu Sep 23, 2010 1:01 am    Post subject:
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    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri Sep 24, 2010 4:48 am    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri Sep 24, 2010 5:34 am    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri Sep 24, 2010 7:29 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DUMMY When do I need add a DCB parameter Martylin JCL & VSAM 3 Fri Dec 08, 2017 7:20 am
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts how can i move s9(9) to s9(9) usage comp HARENDRA CHOUDHARY COBOL Programming 3 Mon Nov 06, 2017 12:10 am
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us