|
View previous topic :: View next topic
|
| Author |
Message |
saurabh516
New User
Joined: 27 Nov 2006
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
saurabh516
New User
Joined: 27 Nov 2006
Posts: 8
Location: Bangalore
|
|
|
|
| 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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Saurabh,
Can you please mention your entire query you have executed using Case logic
Regards,
Chandan |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
saurabh516
New User
Joined: 27 Nov 2006
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
|
|
|
