November 11, 2010 at 7:42 am
This statement does not work correctly but I am not sure why. If I split it up and run each individually it works fine. However, I would really like to combine all three in one statement. Any ideas?
SELECT CO.ID, UDFVALUEFORCUSTOMS.STRING_VAL AS [VALUE FOR CUSTOMS], UDFPNINFO.STRING_VAL AS [PN INFO], UDFEXCHANGESN.STRING_VAL AS [EXCHANGE SN]
FROM CUSTOMER_ORDER CO
LEFT OUTER JOIN USER_DEF_FIELDS UDFVALUEFORCUSTOMS ON CO.ID = UDFVALUEFORCUSTOMS.DOCUMENT_ID
LEFT OUTER JOIN USER_DEF_FIELDS UDFPNINFO ON CO.ID = UDFPNINFO.DOCUMENT_ID
LEFT OUTER JOIN USER_DEF_FIELDS UDFEXCHANGESN ON CO.ID = UDFEXCHANGESN.DOCUMENT_ID
WHERE UDFVALUEFORCUSTOMS.ID = 'UDF-0000061' AND UDFPNINFO.ID = 'UDF-0000062' AND UDFEXCHANGESN.ID = 'UDF-0000063'
November 11, 2010 at 8:05 am
Hi,
What exactly does it do wrong or what do you expected it to do?
Is it perhaps the AND between each item in your WHERE statement, it is looking for cases where all three statements are true for the entire statement to evaluate to true?
WHERE UDFVALUEFORCUSTOMS.ID = 'UDF-0000061' AND UDFPNINFO.ID = 'UDF-0000062' AND UDFEXCHANGESN.ID = 'UDF-0000063'
Not sure but you might get a similar result with using one left join,3 case statement and WHERE ID IN('UDF-0000061','UDF-0000062' ,'UDF-0000063' ). But then again maybe I'm just barking up the wrong tree 😛
Jinx.
November 11, 2010 at 8:17 am
The "AND" in the Where Clause was the culprit. Thanks for all your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply