SELECT DEPT_NBR,SALES,SALES_DATE,SALES_HR_NBR
FROM TEST1.TABLE1
WHERE SALES_DATE BETWEEN 'DATE1' AND 'DATE2'
UNION
SELECT DEPT_NBR,SALES,SALES_DATE,SALES_HR_NBR
FROM TEST1.TABLE2
WHERE SALES_DATE BETWEEN 'DATE1' AND 'DATE2'
WITH UR
I need to add Select statement for each input record which has date1 and date2 using UNION and also need to complete with 'WITH UR' statement to mark it has end. I am able to find few related samples from our forum and also i am working from my end to accomplish this
//STEP001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
2019-01-01 2019-01-05
2019-01-10 2019-01-15
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL BUILD=(C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE1',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',/,
C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE2',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'WITH UR;')
Thanks for the base code . I need to use 'WITH UR' statement only for last input record till then i need to use 'UNION' for my select statements. Is there a way to identify the last record and merge 'WITH UR' statement accordingly
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
If I'm reading this correctly, TS wants one SELECT statement generated for each SORTIN record where each but the last ends with UNION and the last ends with "WITH UR".
Balaji K, What is the expectation that you have even after Marso provided you the solution? When I thought over it , you have no other way but to hardcode the query in the BUILD, because the table names aren’t sourced from any datasets or parm and being said that why don’t you just repeat AND twist the BUILD as per the input records when it can not be fully automated?
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
balaji81_k wrote:
Hi Marso,
Thanks for the base code . I need to use 'WITH UR' statement only for last input record till then i need to use 'UNION' for my select statements. Is there a way to identify the last record and merge 'WITH UR' statement accordingly
Thanks in advance ,
Balaji K
balaji81_k,
In Marso's example, simply move the 'WITH UR' from the BUILD to TRAILER1. Use REMOVECC to get rid of the carriage control. Good luck!
Code:
....
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C''''),
REMOVECC,TRAILER1=('WITH UR;')
Input ranges from 15 records and Date range can be picked from that to Qualify for building SQL statements is about max 5 but there is always possibility that i need to choose 15 too (max) as of now .
Balaji K, What is the expectation that you have even after Marso provided you the solution? When I thought over it , you have no other way but to hardcode the query in the BUILD, because the table names aren’t sourced from any datasets or parm and being said that why don’t you just repeat AND twist the BUILD as per the input records when it can not be fully automated?
Hi Rohit,
I tried with Marso solution and it works good and I added SELECT statements which does nothing after the last statement 'UNION' from the build and mark this dummy select as end to query.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
balaji81_k wrote:
I added SELECT statements which does nothing after the last statement 'UNION' from the build and mark this dummy select as end to query.
balaji81_k,
Why do you need a dummy select? Can you post here what you have done? I thought your problem was to add 'WITH UR' at the end of the entire SQL statement-once.
Maybe I am missing something.
I added SELECT statements which does nothing after the last statement 'UNION' from the build and mark this dummy select as end to query.
balaji81_k,
Why do you need a dummy select? Can you post here what you have done? I thought your problem was to add 'WITH UR' at the end of the entire SQL statement-once.
Maybe I am missing something.
Hi Arun,
I tried with your approach but it is not working as expected , here is the code that i used from Marso,
Code:
SORT FIELDS=COPY
OUTFIL BUILD=(C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE1',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',/,
C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE2',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',80:X)
and after i arrive with above build, i just merge with another file which
has dummy select as like below
Code:
SELECT DEPT_NBR,SALES,SALES_DATE
FROM TEST1.TABLE1
WHERE SALES_DATE IS NULL
ORDER BY DEPT_NBR,SALES_DATE
WITH UR
;
I tried with your solution but it not working as expected . code from your suggestion is
Code:
SORT FIELDS=COPY
OUTFIL BUILD=(C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE1',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',/,
C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE2',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C''''),
REMOVECC,TRAILER1=('WITH UR;')
Assume input is
Code:
2019-01-20 2019-01-25
2018-01-21 2018-01-26
OUTPUT I GOT:-
Code:
SELECT DEPT_NBR,SALES,SALES_DATE
FROM TEST1.TABLE1
WHERE SALES_DATE BETWEEN '2019-01-20' AND '2019-01-25'
UNION
SELECT DEPT_NBR,SALES,SALES_DATE
FROM TEST1.TABLE2
WHERE SALES_DATE BETWEEN '2019-01-20' AND '2019-01-25'
SELECT DEPT_NBR,SALES,SALES_DATE
FROM TEST1.TABLE1
WHERE SALES_DATE BETWEEN '2018-01-21' AND '2018-01-26'
UNION
SELECT DEPT_NBR,SALES,SALES_DATE
FROM TEST1.TABLE2
WHERE SALES_DATE BETWEEN '2018-01-21' AND '2018-01-26'
WITH UR;
Sorry Arun , I may miss something here from your suggestion.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
I see your problem now. You could try something like this.
Code:
//SYSIN DD *
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:5X,SEQNUM,8,ZD)),
IFTHEN=(WHEN=(86,8,ZD,GT,1),OVERLAY=(81:C'UNION'))
OUTFIL REMOVECC,BUILD=(C' SELECT DEPT_NBR,SALES,SALES_DATE',80:X,/,
C' FROM TEST1.TABLE1',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',/,
C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE2',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C''''),
SECTIONS=(86,8,HEADER3=(81,5)),
TRAILER1=('WITH UR;')
I see your problem now. You could try something like this.
Code:
//SYSIN DD *
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:5X,SEQNUM,8,ZD)),
IFTHEN=(WHEN=(86,8,ZD,GT,1),OVERLAY=(81:C'UNION'))
OUTFIL REMOVECC,BUILD=(C' SELECT DEPT_NBR,SALES,SALES_DATE',80:X,/,
C' FROM TEST1.TABLE1',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C'''',/,
C'UNION',/,
C' SELECT DEPT_NBR,SALES,SALES_DATE',/,
C' FROM TEST1.TABLE2',/,
C' WHERE SALES_DATE BETWEEN ''',
1,10,C''' AND ''',12,10,C''''),
SECTIONS=(86,8,HEADER3=(81,5)),
TRAILER1=('WITH UR;')
Arun, I tried with your code , it exactly working as expected and now i don't need any dummy select statements . Many Thanks for your code and suggestion.
