Joins with ON and Where Clause



IBM's flagship relational database management system

Joins with ON and Where Clause

Postby rajadasgupta » Tue Feb 02, 2010 12:08 pm

Hi All,

Till now i have seen codes where Joins have ON clause associated with them. But herewith i am posting a query where both ON and Where clause are mentioned in a Join.
Please explain me the functioning of the query. Also i have been asked to fine tune the query by my shop. If any one could help me in this regard.

SELECT_021: PROC;

EXEC SQL
SELECT K1.TKKEY ,K1.BOERSE ,K1.WHRG ,
K.VERFALL, K1.GENERATION,
DATE(COALESCE(CHAR(K1.KURSDATUM),'01.01.0001')) AS KDAT1,
DATE(COALESCE(CHAR(K2.KURSDATUM),'01.01.0001')) AS KDAT2,
DATE(COALESCE(CHAR(K3.KURSDATUM),'01.01.0001')) AS KDAT3,
DATE(COALESCE(CHAR(K4.KURSDATUM),'01.01.0001')) AS KDAT4,
DATE(COALESCE(CHAR(K5.KURSDATUM),'01.01.0001')) AS KDAT5,
K1.rowid as rowid1 ,
k2.rowid as rowid2 ,
k3.rowid as rowid3 ,
k4.rowid as rowid4 ,
k5.rowid as rowid5
INTO :VFI021A1P:IVFI021A1
FROM VFI010A1 K
INNER JOIN
VFI020A1 K1 ON K.TKKEY = K1.TKKEY
AND K.BOERSE = K1.BOERSE
AND K.WHRG = K1.WHRG
AND K.VERFALL = K1.VERFALL /* #C02 */
AND K.GENERATION = K1.GENERATION
AND K1.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K2 ON K1.TKKEY = K2.TKKEY
AND K1.BOERSE = K2.BOERSE
AND K1.WHRG = K2.WHRG
AND K1.VERFALL = K2.VERFALL /* #C02 */
AND K1.GENERATION = K2.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K3 ON K1.TKKEY = K3.TKKEY
AND K1.BOERSE = K3.BOERSE
AND K1.WHRG = K3.WHRG
AND K1.VERFALL = K3.VERFALL /* #C02 */
AND K1.GENERATION = K3.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K4 ON K1.TKKEY = K4.TKKEY
AND K1.BOERSE = K4.BOERSE
AND K1.WHRG = K4.WHRG
AND K1.VERFALL = K4.VERFALL /* #C02 */
AND K1.GENERATION = K4.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.KURSDATUM > K4.KURSDATUM
AND K4.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K5 ON K1.TKKEY = K5.TKKEY
AND K1.BOERSE = K5.BOERSE
AND K1.WHRG = K5.WHRG
AND K1.VERFALL = K5.VERFALL /* #C02 */
AND K1.GENERATION = K5.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.KURSDATUM > K4.KURSDATUM
AND K4.KURSDATUM > K5.KURSDATUM
AND K5.STATUS = 'A'
WHERE
K.STATUS = 'A'
AND K1.STATUS = 'A'
AND (K2.STATUS = 'A' OR K2.STATUS is NULL)
AND (K3.STATUS = 'A' OR K3.STATUS is NULL)
AND (K4.STATUS = 'A' OR K4.STATUS is NULL)
AND (K5.STATUS = 'A' OR K5.STATUS is NULL)
AND (K1.KURSDATUM IS NULL OR
K1.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.STATUS = 'A')
)
AND (K2.KURSDATUM IS NULL OR
K2.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K1.KURSDATUM
AND X.STATUS = 'A')
)
AND (K3.KURSDATUM IS NULL OR
K3.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K2.KURSDATUM
AND X.STATUS = 'A')
)
AND (K4.KURSDATUM IS NULL OR
K4.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K3.KURSDATUM
AND X.STATUS = 'A')
)
AND (K5.KURSDATUM IS NULL OR
K5.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K4.KURSDATUM
AND X.STATUS = 'A')
)

AND K.VERFALL = (SELECT MAX(Y.VERFALL)
FROM VFI010A1 Y
WHERE Y.TKKEY = K1.TKKEY
AND Y.BOERSE = K1.BOERSE
AND Y.WHRG = K1.WHRG
AND Y.VERFALL = K1.VERFALL /* #C02 */
AND Y.GENERATION = K1.GENERATION
AND Y.STATUS = 'A')
AND K.TKKEY = :FI0210A
AND K.BOERSE = :FI0210B
AND K.WHRG = :FI0210C
AND K.GENERATION = :FI0210D
AND K.VERFALL = :FI0210E /* #C02 */
FETCH FIRST 1 ROW ONLY
;

END SELECT_021;
rajadasgupta
 
Posts: 3
Joined: Tue Feb 02, 2010 12:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joins with ON and Where Clause

Postby GuyC » Tue Feb 02, 2010 7:28 pm

It would help if you explained/understood what the query is supposed to return.
If I can read correctly this wil
for a certain (TKKEY,BOERSE, WHRG, GENERATION,VERFALL) on table VFI010A1
it will return the last 5 KURSDATUM & rowid on table VFI020A1 where STATUS = 'A'

Now I'll need to know which Version of DB2 you've got and in what language you are developing. (order by Fetch first in a subselect is only possible in DB 9 NFM)
Does it have to be in one SQLstatement or are you allowed to use a cursor and successive Fetches ? because a cursor , Fetch first 5 record will be a lot faster.
Is (TKKEY,BOERSE, WHRG, GENERATION,VERFALL) a unique index on VFI010A1 ?
Is (TKKEY,BOERSE, WHRG, GENERATION,VERFALL,STATUS,KURSDATUM) a unique index on VFI020A1 ?
Do you think you can handle recursive SQL ? this is also a possibility.

==========================
Also
...
AND K.VERFALL = (SELECT MAX(Y.VERFALL)
FROM VFI010A1 Y
WHERE Y.TKKEY = K1.TKKEY
AND Y.BOERSE = K1.BOERSE
AND Y.WHRG = K1.WHRG
AND Y.VERFALL = K1.VERFALL /* #C02 */
AND Y.GENERATION = K1.GENERATION
AND Y.STATUS = 'A')
...

is kinda useless when you also have
...
AND K.VERFALL = :FI0210E /* #C02 */
...
unless you really want to test if the given :FI0210E is indeed the MAX(K.VERFALL)
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Joins with ON and Where Clause

Postby rajadasgupta » Tue Feb 02, 2010 10:44 pm

Hi GuyC,

Thanks for your reply. I will surely get back to you with all your questions.
Yes i am using DB2 9.
All the columns that you have asked for are indexed.
Even i thought of using a Cursor but i need to code it in one SQL statement.
Can you think of any other way to make the query more simpler.
rajadasgupta
 
Posts: 3
Joined: Tue Feb 02, 2010 12:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joins with ON and Where Clause

Postby dick scherrer » Wed Feb 03, 2010 1:07 am

Hello and welcome to the forum,

Even i thought of using a Cursor but i need to code it in one SQL statement.
What business requirement dictates this? This might be someone's preference, but is hard to imagine it is a business requirement.

Code should work correctly all of the time, be easily maintained, and not waste system resources. Getting by with the fewest SQL statements should not be an issue. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Joins with ON and Where Clause

Postby rajadasgupta » Wed Feb 03, 2010 2:53 pm

Hi Dick,

First thanks for replying..
yes its a business req..
just to add few points..
Then columns Guyc asked for are indexed and i am fetching only the first record and not the first 5 records, so in that case i think one SQL statement will do rather than using a cursor..
Infact i am also confused with the query..But i have been asked to make it more simpler and less I/O consuming one.
At present this code is taking too much resources..I get to know this after generating the Strobe report on the transaction.
I would appreciate if you could suggest some thing more simpler.
rajadasgupta
 
Posts: 3
Joined: Tue Feb 02, 2010 12:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joins with ON and Where Clause

Postby dick scherrer » Thu Feb 04, 2010 12:39 am

Hello,

yes its a business req..
Please explain how this is required by the business. . . Someone in Information Technology might want this, but that is not a business requirement. . .

FETCH FIRST 1 ROW ONLY
I know of no way to issue a "fetch first" without using a cursor. If there is a way, i'll get to learn a new thing here :)

Have you run an EXPLAIN of the query? Have you talked with the dba?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Joins with ON and Where Clause

Postby GuyC » Fri Feb 05, 2010 8:39 pm

Well dick, time to freshen up your db2 knowledge.


following should work in DB2 9 NFM. Unfortenately I'm still 6 weeks away from NFM, so I can't test it myself.

ok : I'll go step by step :
first you'll need to get to the 5 highest kursdatum (in separate rows, but we'll fix that later)
and you'll need to number them from 1to 5 :

select K.TKKEY ,K.BOERSE ,K.WHRG , K.VERFALL, K.GENERATION
, k1.Kursdatum, k1.rowid
, row_number() over order by kursdatum desc) as rn
FROM VFI010A1 K ,   VFI020A1 K1
where K.TKKEY      = :FI0210A
  AND K.BOERSE     = :FI0210B
  AND K.WHRG       = :FI0210C
  AND K.VERFALL    = :FI0210E
  AND K.GENERATION = :FI0210D
  AND K.STATUS     = 'A'

  and K.TKKEY = K1.TKKEY
  AND K.BOERSE = K1.BOERSE
  AND K.WHRG = K1.WHRG
  AND K.VERFALL = K1.VERFALL /* #C02 */
  AND K.GENERATION = K1.GENERATION
  AND K1.STATUS = 'A'
order by kursdatum desc
fetch first 5 rows only


once you have this (verify if these are the 5 rows you need),
you'll use that as a subquery to group those 5 rows into 1 row :

select TKKEY ,BOERSE ,WHRG , VERFALL, GENERATION
,min(case when r=1 then kursdatum end) as kdat1
,min(case when r=2 then kursdatum end) as kdat2
,min(case when r=3 then kursdatum end) as kdat3
,min(case when r=4 then kursdatum end) as kdat4
,min(case when r=5 then kursdatum end) as kdat5
,min(case when r=1 then rowid end) as rowid1
,min(case when r=2 then rowid end) as rowid2
,min(case when r=3 then rowid end) as rowid3
,min(case when r=4 then rowid end) as rowid4
,min(case when r=5 then rowid end) as rowid5

from  ( insert_text_of_query_from_above_here ) A
group by TKKEY ,BOERSE ,WHRG , VERFALL, GENERATION
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Joins with ON and Where Clause

Postby GuyC » Fri Feb 05, 2010 8:40 pm

oops :
row_number() over order by kursdatum desc) as rn
should be
row_number() over (order by kursdatum desc) as rn
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Joins with ON and Where Clause

Postby dick scherrer » Sat Feb 06, 2010 1:34 am

Thanks Guy,

None of the places i've been have installed/upgraded to V9. . .

Maybe by 2012. . . ;)
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Joins with ON and Where Clause

Postby GuyC » Mon Feb 08, 2010 2:55 pm

another typo in the query : I messed up rn and r
since I defined row_number() as rn
I should have used rn in all when clauses of the case

when r=1 then
should be
when rn=1 then

thx rajadasgupta for pointing that out.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post