|
View previous topic :: View next topic
|
| Author |
Message |
John Poulakos
Active User
Joined: 13 Jun 2012
Posts: 178
Location: United States
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
John Poulakos
Active User
Joined: 13 Jun 2012
Posts: 178
Location: United States
|
|
|
|
| I was mostly concerned about what the inner and outer joins were doing. I never was able to use them. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
John Poulakos
Active User
Joined: 13 Jun 2012
Posts: 178
Location: United States
|
|
|
|
| Cool! Thanks. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome 
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
