February 13, 2009 at 11:01 am
I have a query
SELECT
mst.CICS_DIM.CUST_KEY
FROM
mst.CICS_DIM
, stg.STG_CUSTOMER_INTEG
, SOURCE_SYSTEM_REF
, stg.CONTROL_RUN
where
(STG_CUSTOMER_INTEG.CUST_CIS_ID = mst.CICS_DIM.CUST_CICS_ID
and
mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY and SOURCE_SYSTEM_REF.SRC_SYS_ID = 'CIS')
or
(STG_CUSTOMER_INTEG.CUST_CRD_ID = mst.CICS_DIM.CUST_CICS_ID
and
mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY and SOURCE_SYSTEM_REF.SRC_SYS_ID = 'CRD')
or
(STG_CUSTOMER_INTEG.CUST_CSS_ID = mst.CICS_DIM.CUST_CICS_ID
and
mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY and SOURCE_SYSTEM_REF.SRC_SYS_ID = 'CSS')
or
(STG_CUSTOMER_INTEG.CUST_WST_ID = mst.CICS_DIM.CUST_CICS_ID
and
mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY and SOURCE_SYSTEM_REF.SRC_SYS_ID = 'WST')
or
(STG_CUSTOMER_INTEG.CUST_VSA_ID = mst.CICS_DIM.CUST_CICS_ID
and
mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY and SOURCE_SYSTEM_REF.SRC_SYS_ID = 'VSA')
and
mst.CICS_DIM.TRANS_EFF_DT <= CONTROL_RUN.PROC_EFF_DT
and
mst.CICS_DIM.TRANS_END_DT = CONTROL_RUN.PROC_END_DT
I am trying to re-write it using INNER JOIN
but not sure how to use "OR" within JOIN criteria.
Can anybody give me an idea?
February 13, 2009 at 11:53 am
Please try:
SELECT mst.CICS_DIM.CUST_KEY
FROM mst.CICS_DIM
INNER JOIN stg.STG_CUSTOMER_INTEG
ON mst.CICS_DIM.CUST_CICS_ID = STG_CUSTOMER_INTEG.CUST_CIS_ID
INNER JOIN SOURCE_SYSTEM_REF
ON mst.CICS_DIM.SRC_SYS_KEY = SOURCE_SYSTEM_REF.SRC_SYS_KEY
AND SOURCE_SYSTEM_REF.SRC_SYS_ID IN ('CIS','CRD','CSS','WST','VSA')
INNER JOIN stg.CONTROL_RUN
ON mst.CICS_DIM.TRANS_END_DT = CONTROL_RUN.PROC_END_DT
AND mst.CICS_DIM.TRANS_EFF_DT <= CONTROL_RUN.PROC_EFF_DT
February 13, 2009 at 12:59 pm
NULL,
How about my main issue? INNER JOIN OR...
That's where the problem is.
Your query handles only one scenario.
STG_CUSTOMER_INTEG.CUST_CIS_ID = mst.CICS_DIM.CUST_CICS_ID
I have 5 scenarios.
I need to do something like
...
INNER JOIN
ON STG_CUSTOMER_INTEG.CUST_CIS_ID = mst.CICS_DIM.CUST_CICS_ID
OR
ON STG_CUSTOMER_INTEG.CUST_CRD_ID = mst.CICS_DIM.CUST_CICS_ID
OR
ON STG_CUSTOMER_INTEG.CUST_CSS_ID = mst.CICS_DIM.CUST_CICS_ID
OR
ON STG_CUSTOMER_INTEG.CUST_WST_ID = mst.CICS_DIM.CUST_CICS_ID
OR
ON STG_CUSTOMER_INTEG.CUST_VSA_ID = mst.CICS_DIM.CUST_CICS_ID
...
In your code this is missing.
Sorry.
Plus the original query returns more than 5,000 records
Your version returns 0.
February 13, 2009 at 1:09 pm
You want something like this:
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON ((STG_CUSTOMER_INTEG.CUST_CIS_ID = CICS_DIM.CUST_CICS_ID)
OR (STG_CUSTOMER_INTEG.CUST_CRD_ID = CICS_DIM.CUST_CICS_ID)
OR (STG_CUSTOMER_INTEG.CUST_CSS_ID = CICS_DIM.CUST_CICS_ID)
OR (STG_CUSTOMER_INTEG.CUST_WST_ID = CICS_DIM.CUST_CICS_ID)
OR (STG_CUSTOMER_INTEG.CUST_VSA_ID = CICS_DIM.CUST_CICS_ID))
February 13, 2009 at 2:12 pm
Lynn Pettis is closer to the truth.
I am running it now.
It's already 25 min and not finished yet.
But it could be wrong indexes.
mst.CICS_DIM has 745,752 records
stg.STG_CUSTOMER_INTEG has 28,000 records
Lynn,
Can i change it a bit so that I filter by SOURCE_SYSTEM_REF.SRC_SYS_NAME
as well?
Something like....
SELECT mst.CICS_DIM.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (
(stg.STG_CUSTOMER_INTEG.CUST_CIS_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CIS')
OR (stg.STG_CUSTOMER_INTEG.CUST_CRD_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CRD')
OR (stg.STG_CUSTOMER_INTEG.CUST_CSS_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CSS')
OR (stg.STG_CUSTOMER_INTEG.CUST_WST_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'WST')
OR (stg.STG_CUSTOMER_INTEG.CUST_VSA_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'VSA')
)
February 13, 2009 at 2:21 pm
Have you considered using a UNION instead?
....
from
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (STG_CUSTOMER_INTEG.CUST_CIS_ID = CICS_DIM.CUST_CICS_ID)
UNION
...
from
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (STG_CUSTOMER_INTEG.CUST_CRD_ID = CICS_DIM.CUST_CICS_ID)
UNION
...
from
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (STG_CUSTOMER_INTEG.CUST_CSS_ID = CICS_DIM.CUST_CICS_ID)
UNION
...
from
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (STG_CUSTOMER_INTEG.CUST_WST_ID = CICS_DIM.CUST_CICS_ID)
UNION
...
from
STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (STG_CUSTOMER_INTEG.CUST_VSA_ID = CICS_DIM.CUST_CICS_ID)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2009 at 2:38 pm
I'd also look at Matt's suggestion about UNION'ed select's.
Also, I would suggest getting away from writing queries like this:
SELECT mst.CICS_DIM.CUST_KEY -- 4- and 3- part names here is being depreciated
FROM
stg.STG_CUSTOMER_INTEG
INNER JOIN mst.CICS_DIM
ON (
(stg.STG_CUSTOMER_INTEG.CUST_CIS_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CIS')
OR (stg.STG_CUSTOMER_INTEG.CUST_CRD_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CRD')
OR (stg.STG_CUSTOMER_INTEG.CUST_CSS_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CSS')
OR (stg.STG_CUSTOMER_INTEG.CUST_WST_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'WST')
OR (stg.STG_CUSTOMER_INTEG.CUST_VSA_ID = CICS_DIM.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'VSA')
)
You should start writing your queries more like this (formatting is mine):
SELECT
CicsD.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG CustInt
INNER JOIN mst.CICS_DIM CicsD
ON (
(CustInt.CUST_CIS_ID = CicsD.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CIS')
OR (CustInt.CUST_CRD_ID = CicsD.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CRD')
OR (CustInt.CUST_CSS_ID = CicsD.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'CSS')
OR (CustInt.CUST_WST_ID = CicsD.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'WST')
OR (CustInt.CUST_VSA_ID = CicsD.CUST_CICS_ID AND SOURCE_SYSTEM_REF.SRC_SYS_NAME = 'VSA')
)
I would have made changes to SOURCE_SYSTEM_REF, but I don't see it anywhere else in the query.
February 13, 2009 at 3:07 pm
Lynn,
Of course final version would look like you suggested.
It's just a raw code that I throw into the board.
So based on Matt's suggestion
here is my final version.
It runs 15 sec. Not bad.
Let me know if it's wrong please.
SELECT
mst.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG ci
INNER JOIN mst.CICS_DIM mst
ON (ci.CUST_CIS_ID = mst.CUST_CICS_ID)
INNER JOIN SOURCE_SYSTEM_REF ssr
ON mst.SRC_SYS_KEY = ssr.SRC_SYS_KEY AND ssr.SRC_SYS_ID = 'CIS'
UNION
SELECT
mst.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG ci
INNER JOIN mst.CICS_DIM mst
ON (ci.CUST_CRD_ID = mst.CUST_CICS_ID)
INNER JOIN SOURCE_SYSTEM_REF ssr
ON mst.SRC_SYS_KEY = ssr.SRC_SYS_KEY AND ssr.SRC_SYS_ID = 'CRD'
UNION
SELECT
mst.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG ci
INNER JOIN mst.CICS_DIM mst
ON (ci.CUST_CSS_ID = mst.CUST_CICS_ID)
INNER JOIN SOURCE_SYSTEM_REF ssr
ON mst.SRC_SYS_KEY = ssr.SRC_SYS_KEY AND ssr.SRC_SYS_ID = 'CSS'
UNION
SELECT
mst.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG ci
INNER JOIN mst.CICS_DIM mst
ON (ci.CUST_WST_ID = mst.CUST_CICS_ID)
INNER JOIN SOURCE_SYSTEM_REF ssr
ON mst.SRC_SYS_KEY = ssr.SRC_SYS_KEY AND ssr.SRC_SYS_ID = 'WST'
UNION
SELECT
mst.CUST_KEY
FROM
stg.STG_CUSTOMER_INTEG ci
INNER JOIN mst.CICS_DIM mst
ON (ci.CUST_VSA_ID = mst.CUST_CICS_ID)
INNER JOIN SOURCE_SYSTEM_REF ssr
ON mst.SRC_SYS_KEY = ssr.SRC_SYS_KEY AND ssr.SRC_SYS_ID = 'VSA'
February 13, 2009 at 3:09 pm
You have to tell us if it is right or wrong. Does it provide you with the results you are expecting and are those results correct?
February 17, 2009 at 10:10 am
The results look good to me.
I just wanted to make sure the final version does not contain any weird stuff...
By the way can you suggest any good site or book to
polish my query design skills ( I mean complex JOINS, etc.)?
Thank you very much for your help guys.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply