MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Running a left outer join SQL through a BATCH JCL

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Jaya
Beginner


Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
PostPosted: Thu Aug 31, 2006 1:55 pm    Post subject: Running a left outer join SQL through a BATCH JCL Reply with quote
Hi,

I am running the SQL query using the following JCl

Code:
//STEP5   EXEC PGM=PTLDRIVM,
//            PARM='EP=UTLGLCTL/DB2P',
//            REGION=0M,
//            COND=(04,LT)
//*
//*
//*  ************************************************************
//*  A STEPLIB IS USED TO SATISFY REQUIREMENTS OF LOAD DATASETS
//*  ************************************************************
//*
//STEPLIB      DD  DSN=SYS0.XXX.LOADLIB,
//            DISP=SHR
//*
//UNLOD201     DD  DSN=STMP.TEST15A.D0831I,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(TRK,(100,20),RLSE)
//*        BE ELIGIBLE FOR SCRATCH WHEN IT BECOMES UNCATALOGED.    ***
//*
//CNTL0101     DD  DSN=STMP.TEST15B.D0831I,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(TRK,(15,15),RLSE)
//*  *** LAYOUT OF UNLOADED DATASET   ***
//*
//SYSTSIN      DD  UNIT=SYSDA,SPACE=(TRK,(100,10)),
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//*
//SYSTSPRT     DD  UNIT=SYSDA,SPACE=(TRK,(5,5)),
//            DCB=(RECFM=VBA,LRECL=137,BLKSIZE=3155)
//*
//SORTWK01     DD  UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK02     DD  UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK03     DD  UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK04     DD  UNIT=SYSDA,SPACE=(CYL,(100,50))
//*
//UTPRINT      DD  SYSOUT=*
//SYSPRINT     DD  SYSOUT=*
//PTILIB       DD  DSN=SYS0.XXX1.LOADLIB,
//            DISP=SHR
//PTIPARM      DD  DSN=SYS0.XXX2.PARMLIB,
//            DISP=SHR
//PTIIMSG      DD  SYSOUT=*
//PTIMSG       DD  SYSOUT=*
//SYSUDUMP     DD  SYSOUT=Z
//*
//SYSIN        DD  * *** CONSTANT CONTROL CARDS ***
             FASTUNLOAD
             LOAD-CONTROL  DB2LOAD
             CTLDDN CNTL01
             SHRLEVEL  REFERENCE
             UNLDDN    UNLOD2
             LIMIT  0099999991
             SORTNUM  012
             SORTDEVT SYSDA
             OUTPUT-FORMAT FIXED
  SELECT A.PLANT_CD,CHAR(','),A.PART_NUM,CHAR( ','),
         A.DTL_PART_NUM,CHAR(','),CHAR(A.DTL_PART_EXT_QTY),
         CHAR(','),CHAR(B.T_CD)
         FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
             ON A.PLANT_CD = B.PLANT_CD AND
                A.DTL_PART_NUM = B.PART_NUM AND
                A.CST_BOM_EXCL_IND <> 'Y' AND
                B.DELETE_IND IN ('N', ' ')
                        AND
                B.EFFEC_THRU_DATE >=
                       CURRENT DATE
                        AND
                B.EFFEC_FR_DATE <=
                       CURRENT DATE
         ORDER BY A.PLANT_CD,A.PART_NUM,A.DTL_PART_NUM ;
/*    END OF INPUT
//


The query runs fine and the query extract file looks like
Code:

AAA,12345                    ,PART1                    , 0000000001.0000,..M
AAB,23456                    ,PART2                    , 0000000001.0000,..M


There are 2 dots getting populated before T_CD
Code:
,..M

T_CD COLUMN in TABLE2 is with datatype CHAR(1).

Please advice on how to remove them by changing the query.Or else i need to use a sort step to strip them.

If i am running the SQL using IKJEFT01 using DSNTIAUL program
T_CD COLUMN is getting populated as

Code:
M.



Thanks,
Jaya.
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Aug 31, 2006 2:07 pm    Post subject: Reply with quote
Change your sql to the following.

Code:

 SELECT A.PLANT_CD
       ,CHAR(',')
       ,A.PART_NUM
       ,CHAR( ',')
       ,A.DTL_PART_NUM
       ,CHAR(',')
       ,CHAR(A.DTL_PART_EXT_QTY)
       ,CHAR(',')
       ,CHAR(VALUE(B.T_CD),' '))
   FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
     ON A.PLANT_CD         = B.PLANT_CD
    AND A.DTL_PART_NUM     = B.PART_NUM
    AND A.CST_BOM_EXCL_IND <> 'Y'
    AND B.DELETE_IND       IN ('N', ' ')
    AND B.EFFEC_THRU_DATE  >= CURRENT DATE
    AND B.EFFEC_FR_DATE    <=  CURRENT DATE
  ORDER BY A.PLANT_CD
          ,A.PART_NUM
          ,A.DTL_PART_NUM
     ;
/*    END OF INPUT



and read this topic entirely for the details as to what caused the extra bytes

http://www.mvsforums.com/helpboards/viewtopic.php?t=7014

Hope this helps...

Cheers

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

Last edited by kolusu on Fri Sep 01, 2006 5:49 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Jaya
Beginner


Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
PostPosted: Thu Aug 31, 2006 8:58 pm    Post subject: Reply with quote
Thanks a lot Kolusu! You have a brilliant answer as usual...

Code:

SELECT A.PLANT_CD
       ,CHAR(',')
       ,A.PART_NUM
       ,CHAR( ',')
       ,A.DTL_PART_NUM
       ,CHAR(',')
       ,CHAR(A.DTL_PART_EXT_QTY)
       ,CHAR(',')
       ,CHAR(VALUE(B.T_CD,' '))
   FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
     ON A.PLANT_CD         = B.PLANT_CD
    AND A.DTL_PART_NUM     = B.PART_NUM
    AND A.CST_BOM_EXCL_IND <> 'Y'
    AND B.DELETE_IND       IN ('N', ' ')
    AND B.EFFEC_THRU_DATE  >= CURRENT DATE
    AND B.EFFEC_FR_DATE    <=  CURRENT DATE
  ORDER BY A.PLANT_CD
          ,A.PART_NUM
          ,A.DTL_PART_NUM
     ;
/*    END OF INPUT


Thanks again,
Jaya.
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein
Back to top
View user's profile Send private message
Jaya
Beginner


Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
PostPosted: Tue Sep 05, 2006 9:54 am    Post subject: Reply with quote
Hi

I am running 2 queries

Query 1:
Code:
SELECT PLANT_CD,CHAR(','),PART_NUM,CHAR(','),
       DTL_PART_NUM,CHAR(','),CHAR(DTL_PART_EXT_QTY)
FROM TABLE1
WHERE CST_BOM_EXCL_IND <> 'Y'
ORDER BY PLANT_CD, PART_NUM, DTL_PART_NUM ;


Query 2:
Code:
SELECT     PART_NUM
                       , PLANT_CD
                       , T_CD
     FROM    TABLE2
     WHERE          DELETE_IND IN ('N', ' ')
                         AND
                         EFFEC_THRU_DATE >=
                         CURRENT DATE
                         AND
                         EFFEC_FR_DATE <=
                        CURRENT DATE;


I need all the rows in the result of QUERY- 1 . For every row i need to look up the result of QUERY-2 for matching rows with keys
Quote:
TABLE1.PLANT_CD = TABLE2.PLANT_CD AND
TABLE1.DTL_PART_NUM = TABLE2.PART_NUM


For that purpose i coded a left outer join
Code:
SELECT A.PLANT_CD
       ,CHAR(',')
       ,A.PART_NUM
       ,CHAR( ',')
       ,A.DTL_PART_NUM
       ,CHAR(',')
       ,CHAR(A.DTL_PART_EXT_QTY)
       ,CHAR(',')
       ,CHAR(VALUE(B.T_CD),' '))
   FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
     ON A.PLANT_CD         = B.PLANT_CD
    AND A.DTL_PART_NUM     = B.PART_NUM
    AND A.CST_BOM_EXCL_IND <> 'Y'
    AND B.DELETE_IND       IN ('N', ' ')
    AND B.EFFEC_THRU_DATE  >= CURRENT DATE
    AND B.EFFEC_FR_DATE    <=  CURRENT DATE
  ORDER BY A.PLANT_CD
          ,A.PART_NUM
          ,A.DTL_PART_NUM
     ;


This left outer join query results in no of rows greater than the QUERY1 resultant rows. I am missing something here..

Please advise.

Thanks,
Jaya.
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein
Back to top
View user's profile Send private message
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Tue Sep 05, 2006 10:29 am    Post subject: Reply with quote
Does Table 2 contain PLANT CD and PART NUM multiple times? It appears that table 1 has them unique and w/ table 2 it isn't the case.

Try doing a EQUI JOIN and if you get the same results, then LEFT OUTER JOIN is not the culprit and Table 2 is the culprit.
________
vapir no2

Last edited by coolman on Sat Feb 05, 2011 1:53 am; edited 1 time in total
Back to top
View user's profile Send private message
Jaya
Beginner


Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
PostPosted: Tue Sep 05, 2006 12:21 pm    Post subject: Reply with quote
Coolman,

Thanks for the reply....

TABLE1 and QUERY1 has duplicates on PLANT_CD and DTL_PART_NUM.

TABLE2 also has duplicates on PART_NUM and PLANT_CD .But the QUERY2
Code:
SELECT     PART_NUM
                       , PLANT_CD
                       , T_CD
     FROM    TABLE2
     WHERE          DELETE_IND IN ('N', ' ')
                         AND
                         EFFEC_THRU_DATE >=
                         CURRENT DATE
                         AND
                         EFFEC_FR_DATE <=
                        CURRENT DATE;
has no duplicates on PART_NUM and PLANT_CD.

I need all the rows in QUERY1 with matching T_CD value from QUERY2.

My current work around is
Code:
SELECT A.PLANT_CD,CHAR(','),A.PART_NUM,CHAR( ','),
        A.DTL_PART_NUM,CHAR(','),CHAR(A.DTL_PART_EXT_QTY),
        CHAR(','),CHAR(VALUE(B.MAKE_BUY_CD,' '))
 FROM
       (SELECT PLANT_CD,PART_NUM,
        DTL_PART_NUM,DTL_PART_EXT_QTY
        FROM TABLE1
        WHERE CST_BOM_EXCL_IND <> 'Y') A
     LEFT OUTER JOIN
       (SELECT PART_NUM, PLANT_CD, T_CD
        FROM    TABLE2
        WHERE   DELETE_IND IN ('N', ' ')
                     AND
                EFFEC_THRU_DATE >=
                     CURRENT DATE
                     AND
                EFFEC_FR_DATE <=
                     CURRENT DATE) B
       ON A.DTL_PART_NUM     = B.PART_NUM
       AND A.PLANT_CD        = B.PLANT_CD
 ORDER BY PLANT_CD, PART_NUM, DTL_PART_NUM ;


Please advise if this would have any flaws or any better query.

Thanks,
Jaya,
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein
Back to top
View user's profile Send private message
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Tue Sep 05, 2006 4:48 pm    Post subject: Reply with quote
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/1.3.5?DT=20010710165542

Look at this link and it exactly matches your scenario mentioned above - However, I'm still not able to conclude why it didn't work just by coding a LEFT OUTER JOIN (since you didn't have a WHERE clause out there)
________
grape ape

Last edited by coolman on Sat Feb 05, 2011 1:53 am; edited 1 time in total
Back to top
View user's profile Send private message
Jaya
Beginner


Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
PostPosted: Tue Sep 05, 2006 9:59 pm    Post subject: Reply with quote
Thanks for the link coolman!...I had also reffered the same for my current workaround. 8) .
With my recent query i am getting the same rows as my earlier QUERY1.

Thanks,
Jaya.
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group