Hi experts,
I have one table :
CREATE TABLE HR.TABLE_Y
(
TYPE NUMBER(2),
ID NUMBER(2),
CALC NUMBER(5)
)
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(1, 1, 10);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(1, 2, 15);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(1, 3, 17);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(1, 4, 20);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(2, 2, 11);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(2, 1, 14);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(2, 3, 18);
Insert into HR.TABLE_Y
(TYPE, ID, CALC)
Values
(3, 1, 30);
COMMIT;
SQL> SELECT * FROM TABLE_Y ORDER BY 1,2;
TYPE ID CALC
---------- ---------- ----------
1 1 10
1 2 15
1 3 17
1 4 20
2 1 14
2 2 11
2 3 18
3 1 30
I have Query as below,
I'm passing a TYPE, and ID one value as condition to a Query,
SELECT TYPE, ID, CALC
FROM TABLE_Y
WHERE TYPE = :TYPE
AND ID IN CASE
WHEN :TYPE =1
THEN 2
WHEN :TYPE =2
THEN 1
End
;
When passing a TYPE = 1
Output
TYPE ID CALC
---------- ---------- ----------
1 2 15
When passing a TYPE = 2
Output
TYPE ID CALC
---------- ---------- ----------
2 1 14
I want passing a TYPE, and ID more values as condition to a Query,
I try,
SELECT TYPE, ID, VALUE
FROM TABLE_Y
WHERE TYPE = :TYPE
AND ID IN CASE
WHEN :TYPE =1
THEN (1,3)
WHEN :TYPE =2
THEN (1,2)
End
;
But give me error:
ORA-00907: missing right parenthesis
Output Desired,When passing a TYPE = 1
TYPE ID CALC
---------- ---------- ----------
1 1 10
1 3 17
Output Desired,When passing a TYPE = 2
TYPE ID CALC
---------- ---------- ----------
2 1 14
2 2 11
Thanks in advance
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 ; Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production