Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Build SQL query using SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Mon Feb 18, 2019 9:56 am    Post subject: Build SQL query using SORT
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2242
Location: NY,USA

PostPosted: Mon Feb 18, 2019 10:55 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Mon Feb 18, 2019 11:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1323
Location: Israel

PostPosted: Tue Feb 19, 2019 5:42 pm    Post subject: Reply to: Build SQL query using SORT
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Tue Feb 19, 2019 11:04 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2242
Location: NY,USA

PostPosted: Wed Feb 20, 2019 8:43 pm    Post subject:
Reply with quote

Balaji K, How many records do you anticipate like this ? In the example you have shown you only said 2?
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1053
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Wed Feb 20, 2019 9:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2242
Location: NY,USA

PostPosted: Wed Feb 20, 2019 9:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Feb 20, 2019 9:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Wed Feb 20, 2019 9:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Wed Feb 20, 2019 10:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Feb 20, 2019 11:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Thu Feb 21, 2019 2:30 am    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Feb 21, 2019 4:07 am    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Thu Feb 21, 2019 5:01 am    Post subject:
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1323
Location: Israel

PostPosted: Thu Feb 21, 2019 12:50 pm    Post subject: Reply to: Build SQL query using SORT
Reply with quote

36_8_12.gif
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 Query runs very long John F Dutcher DB2 21 Wed Aug 21, 2019 11:44 pm
No new posts Query to Truncate value in table bhaskar_kanteti DB2 2 Mon Aug 12, 2019 3:26 pm
No new posts need assistance on a sort rajiv rengasamy SYNCSORT 11 Fri Jul 26, 2019 8:19 pm
No new posts need help with a tricky sort - Continued rajiv rengasamy SYNCSORT 8 Tue Jul 16, 2019 1:26 pm
No new posts Sort records within a data block rajiv rengasamy DFSORT/ICETOOL 0 Fri Jul 12, 2019 3:25 pm

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