Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Help please!

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Fri Jun 22, 2012 1:16 am    Post subject: SQL Help please!
Reply with quote

I'm pretty good with how DB2 works, but no expert with SQL and this one has me completely stumped. It works now, but I don't understand what it's doing and I need to break it up due to system design changes. Can somebody please tell me what it's doing?


Code:
EXEC SQL DECLARE cFirstVals CURSOR FOR                 
  SELECT                                               
      CASE                                             
        WHEN C.SOURCE_PRIORITY IS NULL THEN 100         
        ELSE C.SOURCE_PRIORITY                         
      END                                               
     , B.MAPP_KNOTEN                                   
     ,CASE                                             
        WHEN D.TARGET_FACTOR IS NULL THEN 1.0E+0       
        ELSE D.TARGET_FACTOR                           
      END                                               
     ,B.VALUE_FROM                                     
     ,B.VALUE_TO                                       
     ,B.DC_FLAG                                         
     FROM G10.TG10318 D RIGHT OUTER JOIN               
        G10.TG10322 A                                   
       ON      A.MAPP_KNOTEN = D.MAPP_KNOTEN           
           AND A.MAPP_NAME   = D.MAPP_NAME             
           AND D.MAPPSYSTEM  = :sSystems.cTargetSystem 
     INNER JOIN G10.TG10323 B                           
     ON      A.MAPP_KNOTEN = B.MAPP_KNOTEN             
         AND A.MAPP_NAME   = B.MAPP_NAME               
         LEFT OUTER JOIN G10.TG10318 C                 
     ON      A.MAPP_KNOTEN = C.MAPP_KNOTEN             
         AND A.MAPP_NAME   = C.MAPP_NAME               
         AND C.MAPPSYSTEM  = :sSystems.cSourceSystem   
   WHERE                                               
         A.MAPP_NAME   = :DCLTG10323.MAPP-NAME         
     AND B.MAPP_ATTR_NAME = :sSystems.cFirstAttrName   
     AND B.MAPPSYSTEM  =  :sSystems.cSourceSystem       
     AND CURRENT TIMESTAMP                             
         BETWEEN  A.VALID_FROM AND A.VALID_TO           
   ORDER BY 1 DESC, 2                                   
END-EXEC.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 22, 2012 2:11 am    Post subject:
Reply with quote

Hello and welcome to the forum,

This query is gathering info from 4 tables. Rather than naming the entire table-name, the code assigns A thru D for the four tables.

To better understand the query, suggest you become more familiar with the underlying data. This should help with understanding the code.

Suggest you look at it a "bit at a time" rather than trying to eat the whole thing at once.

If you have specific questions about some part of the code, isolate this piece and someone should be able to help clarify.
Back to top
View user's profile Send private message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Fri Jun 22, 2012 2:48 am    Post subject: Reply to: SQL Help please!
Reply with quote

I was mostly concerned about what the inner and outer joins were doing. I never was able to use them.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 22, 2012 3:14 am    Post subject:
Reply with quote

Hello,

Suggest you look here:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqk10/4.2.2.2?ACTION=MATCHES&REQUEST=outer+join&TYPE=FUZZY&SHELF=&DT=20070125023435&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT

At the top of the page is a link to "IBM Manuals" which has many of the manuals folks need often.
Back to top
View user's profile Send private message
John Poulakos

New User


Joined: 13 Jun 2012
Posts: 49
Location: United States

PostPosted: Fri Jun 22, 2012 3:24 am    Post subject:
Reply with quote

Cool! Thanks.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 22, 2012 7:11 am    Post subject:
Reply with quote

You're welcome icon_smile.gif

Suggest as you learn, you parctice with joins using the tables (preferable the test tables rather than production) to expirement with some join of your own.

As time goes by, the larger query will make more and more sense.

If something strange happens, someone will be here.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 22, 2012 3:51 pm    Post subject:
Reply with quote

you shouldn't mess with right and left outer join in the same query , rewrite using all left or inners :

Code:
     FROM            G10.TG10322 A                                   
     INNER JOIN      G10.TG10323 B ON A.MAPP_KNOTEN = B.MAPP_KNOTEN AND A.MAPP_NAME   = B.MAPP_NAME               
     Left outer join G10.TG10318 D ON A.MAPP_KNOTEN = D.MAPP_KNOTEN AND A.MAPP_NAME   = D.MAPP_NAME AND D.MAPPSYSTEM  = :sSystems.cTargetSystem 
     LEFT OUTER JOIN G10.TG10318 C ON A.MAPP_KNOTEN = C.MAPP_KNOTEN AND A.MAPP_NAME   = C.MAPP_NAME AND C.MAPPSYSTEM  = :sSystems.cSourceSystem   

so what this query does is :
get data from A and B
then checks if data (SOURCE_PRIORITY) is available in C
then checks if data (TARGET_FACTOR) is available in D

if source_priority is available then it will use that, otherwise it will use 100 as default
if target_factor is available then it will use that, otherwise it will use 1.0E+0 as default

As an extra I want to point you to the function COALESCE() which would do the same as the "CASE when NULL" , but a little bit shorter.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:



Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us