CODEWITHSUNDEEP
sql
user3789200
user3789200

Reputation: 1186

Multiple columns in, IN clause

I was unable to run this query in Oracle as it doesn't allow to use multiple column name inside IN clause.

CURSOR temp_3((VOUCHER_NO IN NUMBER) IS 
           SELECT * 
           FROM INVOICE_ITEM_TAB 
           WHERE INVOICE_ID, PO_REF_NUMBER IN (
              SELECT INVOICE_ID, PO_REF_NUMBER, 
              FROM INVOICE_TAB 
              WHERE VOUCHER_NO_REF IN (
                 SELECT VER_NO 
                 FROM WORK_ORDER_CODING_TAB 
                 WHERE VER_NO=VOUCHER_NO
              )
            )

Can someone please help me

Upvotes: 0

Views: 47

Answers (2)

Nani
Nani

Reputation: 153

I thing you want something like this

select * from table where col in (select Id from table where column ='xyz')

**one more way **

select * from table where col in ('aa','bb','cc')

Upvotes: 0

Magnus
Magnus

Reputation: 46909

Use exists instead.

   SELECT * 
   FROM INVOICE_ITEM_TAB iit
   WHERE exists
      SELECT null, 
      FROM INVOICE_TAB it
      WHERE 
         iit.INVOICE_ID = it.INVOICE_ID and
         iit.PO_REF_NUMBER = it.PO_REF_NUMBER and
         it.VOUCHER_NO_REF IN (
            SELECT VER_NO 
            FROM WORK_ORDER_CODING_TAB 
            WHERE VER_NO=VOUCHER_NO
      )
    )

Upvotes: 2

Related Questions