I am executing below given queries using SPUFI and getting the same number !
JOIN Query:
Code:
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A,
DB01.ORDER_PERS B
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY = B.ORDER_KEY
WITH UR;
Is the below one a Sub-Query?
Code:
SELECT COUNT((A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY IN
(SELECT B.ORDER_KEY FROM DB01.ORDER_PERS B
WHERE A.ORDER_KEY = B.ORDER_KEY)
WITH UR;
Is the below one Correlated Sub-Query?
Code:
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND EXISTS
(SELECT B.ORDER_KEY FROM DB01.ORDER_PERS B
WHERE A.ORDER_KEY = B.ORDER_KEY)
WITH UR;
All of them are giving me the correct COUNT only. I would like to know which variety each one belongs to..... Please help.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Code:
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY IN
(SELECT ORDER_KEY FROM DB01.ORDER_PERS )