i have following sample data and require sql query to get values match in IN condition -im using toad.
select * from mytrans where code in (1,2,4) it gives result.
however when i am using parameter for eg: select * from mytrans where code in (:p_code) it raise invalid number (:p_code values im using are 1,2,3)
when i use following
select * from mytrans where to_char(code) in (:p_code) it gives null (:p_code values im using are 1,2,3 i also try '1','2','3')
i have to use multiple and many values in condition, please assist to resolve.
DATA CREATE TABLE MYTYPE ( CODE NUMBER, NAME VARCHAR2(50 BYTE) ); ALTER TABLE MYTYPE ADD ( CONSTRAINT MYTYPE_PK PRIMARY KEY (CODE); INSERT INTO MYTYPE (CODE, NAME) VALUES (1, 'Payable'); INSERT INTO MYTYPE (CODE, NAME) VALUES (2,'Receivable'); INSERT INTO MYTYPE (CODE, NAME) VALUES (3 ,'Assets'); INSERT INTO MYTYPE (CODE, NAME) VALUES (5, 'Capital'); CREATE TABLE MYTRANS ( CODE NUMBER, DESCRIPTION VARCHAR2(50 BYTE), MYTYPE_CODE NUMBER ); ALTER TABLE MYTRANS ADD CONSTRAINT MYTRANS_PK PRIMARY KEY (CODE); ALTER TABLE MYTRANS ADD ( CONSTRAINT MYTRANS_R01 FOREIGN KEY (MYTYPE_CODE) REFERENCES MYTYPE (CODE) ENABLE VALIDATE); INSERT INTO MYTRANS (CODE,DESCRIPTION,MYTYPE_CODE) VALUES ( 1, 'ny', 1); INSERT INTO MYTRANS (CODE,DESCRIPTION,MYTYPE_CODE) VALUES ( 2, 'la', 1); INSERT INTO MYTRANS (CODE,DESCRIPTION,MYTYPE_CODE) VALUES ( 3, 'sa', 2); INSERT INTO MYTRANS (CODE,DESCRIPTION,MYTYPE_CODE) VALUES ( 4, 'sd', 3); |
|---|