January 22, 2008 at 10:56 am
--SQL 6.5 version code
-- SQL 6.5
SELECT bucket_id_ml = min(T.exception_category_id),
msg_id = X.msg_id
INTO #ework2
FROM #Excp_Key REGN,
#Excp_Key CAMV,
#Excp_Key COMP,
#Excp_Key MKT,
#Excp_Key RTYP,
#Excp_Key LEVL,
#ework X,
exception_translation_wil T (NOLOCK), exception_define_wil E (NOLOCK)
WHERE REGN.msg_id = X.msg_id
AND REGN.code = 'REGN'
AND CAMV.msg_id = X.msg_id
AND CAMV.code = 'CAMV'
AND COMP.msg_id =* X.msg_id
AND COMP.code = 'COMP'
AND MKT.msg_id =* X.msg_id
AND MKT.code = 'MKT'
AND RTYP.msg_id =* X.msg_id
AND RTYP.code = 'RTYP'
AND LEVL.msg_id =* X.msg_id
AND LEVL.code = 'LEVL'
AND X.system_origin_ml = 'CAPS'
AND X.system_sub_origin_ml = 'OPGS'
AND X.bucket_id_ml = 0
AND CONVERT (CHAR(40), CONVERT(CHAR(4) , REGN.val) +
CONVERT(CHAR(4) , CAMV.val) +
ISNULL (CONVERT(CHAR(12), COMP.val),SPACE (12)) +
ISNULL (CONVERT(CHAR(12), MKT.val), SPACE (12)) +
ISNULL (CONVERT(CHAR(4) ,RTYP.val),SPACE (4)) +
ISNULL (CONVERT(CHAR(4) , LEVL.val),SPACE (4))) LIKE CONVERT (CHAR(40), T.exception_info)
AND SUBSTRING (T.exception_info, 1, 1 ) NOT IN ( ' ', '_' )
AND E.exception_category_id = T.exception_category_id
AND E.datawindow_id > -1 --Avoid assigning to a folder
GROUP BY X.msg_id
--To modifiy the above code with ANSI SQL using LEFT and RIGHT JOIN
instead of using *= or =* join operator.
SELECT bucket_id_ml = min(T.exception_category_id)
, msg_id = X.msg_id
INTO #ework2
FROM #Excp_Key REGN
JOIN #ework X
ON REGN.msg_id = X.msg_id
JOIN #Excp_Key CAMV
ON CAMV.msg_id = X.msg_id
LEFT JOIN #Excp_Key COMP
ON COMP.msg_id = X.msg_id
LEFT JOIN #Excp_Key MKT
ON MKT.msg_id = X.msg_id
LEFT JOIN #Excp_Key RTYP
ON RTYP.msg_id = X.msg_id
LEFT JOIN #Excp_Key LEVL
ON LEVL.msg_id = X.msg_id
exception_translation_wil T WITH(NOLOCK)
JOIN exception_define_wil E WITH (NOLOCK)
ON E.exception_category_id = T.exception_category_id
WHERE REGN.code = 'REGN'
AND CAMV.code = 'CAMV'
AND COMP.code = 'COMP'
AND MKT.code = 'MKT'
AND RTYP.code = 'RTYP'
AND LEVL.code = 'LEVL'
AND X.system_origin_ml = 'CAPS'
AND X.system_sub_origin_ml = 'OPGS'
AND X.bucket_id_ml = 0
AND CONVERT (CHAR(40), CONVERT(CHAR(4) , REGN.val) +
CONVERT(CHAR(4) , CAMV.val) +
ISNULL (CONVERT(CHAR(12), COMP.val),SPACE (12)) +
ISNULL (CONVERT(CHAR(12), MKT.val), SPACE (12)) +
ISNULL (CONVERT(CHAR(4) ,RTYP.val),SPACE (4)) +
ISNULL (CONVERT(CHAR(4) , LEVL.val),SPACE (4))) LIKE CONVERT (CHAR(40), T.exception_info)
AND SUBSTRING (T.exception_info, 1, 1 ) NOT IN ( ' ', '_' )
AND E.datawindow_id > -1
GROUP BY X.msg_id
in the above modified code there is no relationship b/w tables #eworks2 and exception_translation_wil as T. How should I modify the code? it should work same as previous query?
Can someone please help me?
January 22, 2008 at 2:16 pm
I don't think these will necessarily return the same results, but I'd have to go back and study up to really be sure. You've maintained all of the logical part of the join criteria, but I think moving the criteria from the WHERE clause to the FROM (or back) can change the results you see when using outer joins.
Look at just one specific join connection in the where clause (COMP.msg_id =* X.msg_id AND COMP.code = 'COMP') assuming X has one row (msg_id=1 ) and COMP has one row (msg_id-1, code='CAMV'). If the optimizer evaluates the code='COMP' first, then that row is eliminated from COMP, but when it is LEFT-JOINED (is that a verb?) with X, the record from X will still be returned with NULLS for all the values in COMP (so you get a row back). If the join is done first, it will link up the row in X and in COMP on the ID, but when it evalutes code='COMP' and comes back false, it will remove the entire joined row from the result set (so you get no rows back). So in once case you the X row with NULLs for COMP, with the other, you get no row at all.
By putting the criteria in the FROM clause, you have clearly stated what you want to have happen. The LEFT JOIN is based on the msg_id (period!). whether or not the joined row is returned with the result set is determined by the COMP.code value. There is less ambiguity and you are much more certain of what you will get back.
I _BELIEVE_ that the optimizer generally takes the where clause statements in order. If that is the case, you should get the same results for both since the =* join is always before the code comparison... but it really makes me nervous and I doubt you could guarantee it would always work right...
January 22, 2008 at 2:34 pm
On that final join, when you say there's no relation between them, do you mean you want a "cross join", where you get all rows in those final two tables joined to all rows in the prior tables?
(I.e.: If there are 5 rows in the first set of joins, and 2 rows in the last set, you'll get 10 total rows. If 3 in the first set and 2 in the last set, you'll get 6. And so on.)
In a cross join, if the Where clause sets the last set to only show 1 row, then you'll get the rows for the first set of joins.
Is that what you're looking for? (Am I being clear enough?)
If so, then set it as a Cross Join. That's the usual for tables with no key relationship being queried together.
It looks to me like the second query will work with that. Set it up on a set of test data, run both, and see if you get the same rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2008 at 11:26 am
SELECT bucket_id_ml = min(T.exception_category_id)
, msg_id = X.msg_id
INTO #ework2
FROM #Excp_Key REGN
JOIN #ework X
ON REGN.msg_id = X.msg_id
JOIN #Excp_Key CAMV
ON CAMV.msg_id = X.msg_id
LEFT JOIN #Excp_Key COMP
ON COMP.msg_id = X.msg_id
LEFT JOIN #Excp_Key MKT
ON MKT.msg_id = X.msg_id
LEFT JOIN #Excp_Key RTYP
ON RTYP.msg_id = X.msg_id
LEFT JOIN #Excp_Key LEVL
ON LEVL.msg_id = X.msg_id
JOIN exception_translation_wil T WITH(NOLOCK)
ON CONVERT (CHAR(40), CONVERT(CHAR(4) , REGN.val) +
CONVERT(CHAR(4) , CAMV.val) +
ISNULL (CONVERT(CHAR(12), COMP.val),SPACE (12)) +
ISNULL (CONVERT(CHAR(12), MKT.val), SPACE (12)) +
ISNULL (CONVERT(CHAR(4) ,RTYP.val),SPACE (4)) +
ISNULL (CONVERT(CHAR(4) , LEVL.val),SPACE (4))) LIKE CONVERT (CHAR(40), T.exception_info)
JOIN exception_define_wil E WITH (NOLOCK)
ON E.exception_category_id = T.exception_category_idWHERE REGN.code = 'REGN'
AND CAMV.code = 'CAMV'
AND COMP.code = 'COMP'
AND MKT.code = 'MKT'
AND RTYP.code = 'RTYP'
AND LEVL.code = 'LEVL'
AND X.system_origin_ml = 'CAPS'
AND X.system_sub_origin_ml = 'OPGS'
AND X.bucket_id_ml = 0
AND SUBSTRING (T.exception_info, 1, 1 ) NOT IN ( ' ', '_' )
AND E.datawindow_id > -1
GROUP BY X.msg_id
Is the above query will retrieve the correct result? I cant able to test the previous code and modified code. They use lot of temp tables.
January 24, 2008 at 8:37 am
It looks like it should work, but the only way to be sure is to test it. If you can't, you're just guessing. Always test. Work out some way to test it and do so. Otherwise, if it goes wrong, you have no way of knowing until it has already caused problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply