Very simple query: I want to select contracts, vendors, products and count how many times product was ordered and how many flags were used in these orders.
CREATE TABLE T1 AS
SELECT 'A1' ORD,1 LOCN,123 CONT,111 VNDR,'P' FLG, SYSDATE DT FROM DUAL
UNION SELECT 'A2',1,123,111,'P', SYSDATE FROM DUAL
UNION SELECT 'A3',1,123,101,'S', SYSDATE FROM DUAL
UNION SELECT 'A4',1,233,137,'P', SYSDATE FROM DUAL
UNION SELECT 'A5',1,233,137,'S', SYSDATE FROM DUAL
UNION SELECT 'A6',1,354,321,'S', SYSDATE FROM DUAL
UNION SELECT 'A7',1,776,656,'S', SYSDATE FROM DUAL
;
CREATE TABLE T2 AS
SELECT 123 CONT,111 VNDR,50 PROD FROM DUAL
UNION SELECT 123,111,60 FROM DUAL
UNION SELECT 123,111,65 FROM DUAL
UNION SELECT 233,137,60 FROM DUAL
UNION SELECT 233,137,11 FROM DUAL
UNION SELECT 354,321,44 FROM DUAL
UNION SELECT 776,656,14 FROM DUAL
UNION SELECT 123,191,49 FROM DUAL
;
WITH DUP AS
(
SELECT T1.CONT
,T1.VNDR
,T2.PROD
,COUNT(DISTINCT T1.FLG) FLG_CNT
,COUNT(*) PROD_CNT
FROM T1
,T2
WHERE T1.CONT = T2.CONT
AND T1.VNDR = T2.VNDR
GROUP BY T1.CONT
,T1.VNDR
,T2.PROD
HAVING COUNT(*) > 1
)
SELECT DISTINCT
T1.CONT
,T1.VNDR
,DUP.PROD
,T1.FLG
,T1.ORD
,T1.DT
,DUP.FLG_CNT
,DUP.PROD_CNT
FROM T1
,DUP
WHERE T1.CONT = DUP.CONT
AND T1.VNDR = DUP.VNDR
AND T1.LOCN = 1
ORDER BY DUP.PROD_CNT DESC
,DUP.FLG_CNT DESC
,T1.CONT
,T1.VNDR
,DUP.PROD
,T1.FLG
,T1.ORD
;
When I executed it I got
ORA-01791: not a SELECTed expression
01791. 00000 - "not a SELECTed expression"
*Cause:
*Action:
Error at Line: 25 Column: 27
Now lets remove DISTINCT from COUNT
Query works!!
Now lets put DISTINCT back to COUNT and remove DISTINCT from SELECT
Query still works!
Now put all DISTINCTs back and comment "AND T1.LOCN = 1"
Query works again!
Now put everything back an comment "ORDER BY"
Query works!
What a mess!
Why usage of DISTINCT leads to an error?