June 4, 2009 at 4:47 am
I am trying to rewrite an SQL2005 except construction to a outer join for the benefit of SQL2000 instances, but joining 2 tempsets is giving me a hard time.
I got the following:
SELECT LR.* FROM
(
(SELECT DISTINCT P.PROID,
0 AS PUBID1,
0 AS PUBID2,
TEMP.PUBID AS PUBTYPEID1,
TEMP2.PUBID AS PUBTYPEID2,
'-' AS MON_SO_PLUS,
'-' AS MON_SV_PLUS,
'-' AS MON_SB_PLUS,
'-' AS MON_EB_PLUS,
'-' AS MON_EP_PLUS
FROM TABLE1 P
CROSS JOIN
(SELECT PT.PUBID FROM TABLE2 PT WHERE PT.PUBID > -1999 ) TEMP
CROSS JOIN
(SELECT PT.PUBID FROM TABLE2 PT WHERE PT.PUBID > -1999 ) TEMP2) L
LEFT OUTER JOIN
(Select
P1.PROID AS PROID,
P1.PUBTYPEID AS PUBTYPEID1,
P2.PUBTYPEID AS PUBTYPEID2
from TABLE1 P1
INNER JOIN TABLE1 P2
ON P1.PROID = P2.PROID) R
ON
L.PROID = R.PROID AND L.PUBTYPEID1 = R.PUBTYPEID1 AND L.PUBTYPEID2 = R.PUBTYPEID2
WHERE R.PROID IS NULL
) LR
Can't get it to work. Is it something with aliasing the temp sets?
Appreciate any input
[the result is unioned with a third temp set as a final statement]
June 4, 2009 at 5:47 am
Never mind.. Forgot the explicit outer select :w00t:
June 4, 2009 at 3:01 pm
I'd suggest to use WHERE NOT EXISTS.
OUTER JOIN sucks in some situations.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply