|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I need to build SQL Select Query based on the dates from the input file.
Input file is of LRECL=80, RECFM=FB.
Input format :-
| Code: |
----------------------------------------------
DATE1 DATE2
----------------------------------------------
2019-01-01 2019-01-05
2019-01-10 2019-01-15
|
SQL Query:-
| Code: |
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 |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
There are many similar examples if you search and why UNION ? You can use OR too in the same query if there are no performance issues.
All the best!! |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Rohit,
Sales data available from either one of the tables or from both the tables , hence union is used to get it .
Thanks
Balaji K |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
It took me a couple of minutes to write and test:
| Code: |
//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;') |
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
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 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Balaji K, How many records do you anticipate like this ? In the example you have shown you only said 2? |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1216
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".
Garry. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| 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? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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;') |
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Rohit,
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 .
Thanks
Balaji K |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Rohit Umarjikar wrote: |
| 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.
Thanks
Balaji K |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Arun Raj wrote: |
| 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. |
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.
Thanks
Balaji K |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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;') |
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Arun Raj wrote: |
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.
Thanks
Balaji K |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
