MVSFORUMS.com :: View topic - Dynamic Compare date for SQL querry

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Dynamic Compare date for SQL querry

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

shiv_swami
Beginner

Joined: 29 Nov 2003
Posts: 68
Topics: 14

Posted: Thu Jul 01, 2004 4:41 pm Post subject: Dynamic Compare date for SQL querry

Hi ,

I have following querry that runs well if I run the job after midnight.

Code:

//JSTEP010 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(VFST)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXX.DB2.RUNLIB.LOAD')
/*
//SYSIN DD *
SELECT SUBSTR(CHAR(B.MEMBER_ID),1,10),
A.BILLING_ID,
A.BILL_ADDR_CODE,
B.MEMBER_REGION,
A.UPDATE_DATE
FROM BILL A,
XREF B
WHERE A.UPDATE_DATE =
(SELECT DATE( CURRENT DATE - 1 DAYS )FROM SYSIBM.SYSDUMMY1 ) AND A.UNIQUE_ID = B.OWNER_ID;
/*
//*

But I want to modify it so that :

Code:

if the job runs between 00:00 and 07:00
the update_date should be equated to ( current date.- 1 )
els the job runs between 07:01 and 23:59
the update_date should be equated to ( current date )

How can one do in the querry itself?
_________________
Regards,
Shivprakash

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose

Posted: Thu Jul 01, 2004 5:19 pm Post subject:

Shiv,

Try this

Code:

SELECT SUBSTR(CHAR(B.MEMBER_ID),1,10)
,A.BILLING_ID
,A.BILL_ADDR_CODE
,B.MEMBER_REGION
,A.UPDATE_DATE
FROM BILL A
,XREF B
WHERE A.UPDATE_DATE = (SELECT CASE WHEN HOUR(CURRENT TIME) BETWEEN 0 AND 6
THEN DATE(CURRENT DATE - 1 DAY)
ELSE CURRENT DATE END
FROM SYSIBM.SYSDUMMY1)
AND A.UNIQUE_ID = B.OWNER_ID
;

_________________
Kolusu
www.linkedin.com/in/kolusu

shiv_swami
Beginner

Joined: 29 Nov 2003
Posts: 68
Topics: 14

Posted: Thu Jul 01, 2004 5:25 pm Post subject:

Thanks Kolusu,
Query works great.

I was thinking on following lines..

Code:

SELECT DATE (CURRENT DATE -
(SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1
WHERE CURRENT TIME BETWEEN '00.00.00' AND
'07.00.00' )
Days
)

But it would not work..Is it possible to modify the query on these lines to ?
_________________
Regards,
Shivprakash

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose

Posted: Thu Jul 01, 2004 5:38 pm Post subject:

Shiv,

You can try this sql

Code:

SELECT DATE (CURRENT DATE - (COALESCE(COUNT(*),0) DAYS))
FROM SYSIBM.SYSDUMMY1
WHERE CURRENT TIME BETWEEN '00.00.00' AND '07.00.00'
;

Modified query for the unload:

Code:

SELECT SUBSTR(CHAR(B.MEMBER_ID),1,10)
,A.BILLING_ID
,A.BILL_ADDR_CODE
,B.MEMBER_REGION
,A.UPDATE_DATE
FROM BILL A
,XREF B
WHERE A.UPDATE_DATE = (SELECT CASE WHEN CURRENT TIME BETWEEN '00.00.00' AND '07.00.00'
THEN DATE(CURRENT DATE - 1 DAY)
ELSE CURRENT DATE END
FROM SYSIBM.SYSDUMMY1)
AND A.UNIQUE_ID = B.OWNER_ID
;

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum