CODEWITHSUNDEEP
oracle-database
user9497455
user9497455

Reputation:

Multiple conditions in oracle case statement

I have some trouble achieving my requirement. I have a table called accounts_invoice and data as mentioned below.

DB Table

AC_NO   VARCHAR2(20 BYTE)               
INV_NO  VARCHAR2(20 BYTE)           
CC  VARCHAR2(20 BYTE)               
FT  VARCHAR2(20 BYTE)           

AC_NO  INV_NO   CC      FT
-----------------------------
1   A   PTN INVOICE
1   A   PTN BDE
2   B   ABC INVOICE
2   B   PTN INVOICE
2   B   PTN BDE

I have written a code as below, but my output seems not right as per my requirement.

SELECT
    ac_no,
    CASE WHEN FT like '%INVOICE%' THEN 'AVAILABLE' else 'NOTAVAILABLE'
END AS INVOICE,
        CASE WHEN FT like '%BDE%' THEN 'AVAILABLE' else 'NOTAVAILABLE' END AS BDE
FROM Account_info
where CC='PTN';

Output 

1   A   Available   Notavailable
1   A   Notavailable    Available
2   B   Available   Notavailable
2   B   Notavailable    Available

I'm Expecting the Output as below: But Output should be

AC_NO    INVOICE         BDE
----------------------------------
1       AVAILABLE       AVAILABLE
2       AVAILABLE       AVAILABLE

Please help me, how can I achieve the above output.

Upvotes: 1

Views: 10908

Answers (2)

some1
some1

Reputation: 867

Here is another way of doing it, not sure which will have better performance. Also, FT='INVOICE' will definitely works better, but I am not sure if you data really needs FT like '%INVOICE%'. I have just keep it that way below.

SELECT 
  a1.AC_NO,
  NVL((select 'AVAILABLE' from ACCOUNT_INFO a
   where a.AC_NO=a1.AC_NO and a.FT like '%INVOICE%'
    and rownum=1
  ),'NOTAVAILABLE') as INVOICE,
  NVL((select 'BDE' from ACCOUNT_INFO a
   where a.AC_NO=a1.AC_NO and a.FT like '%BDE%'
    and rownum=1
  ),'NOTAVAILABLE') as BDE
FROM (
  SELECT DISTINCT AC_NO
  FROM ACCOUNT_INFO 
  WHERE CC='PTN'
) a1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

A slightly modified pivot query should work:

SELECT
    AC_NO,
    CASE WHEN COUNT(CASE WHEN FT = 'INVOICE' THEN 1 END) > 0
         THEN 'AVAILABLE' ELSE 'NOTAVAILABLE' END AS INVOICE,
    CASE WHEN COUNT(CASE WHEN FT = 'BDE' THEN 1 END) > 0
         THEN 'AVAILABLE' ELSE 'NOTAVAILABLE' END AS BDE
FROM yourTable
GROUP BY AC_NO

enter image description here

In this case, we wrap the counts in a second CASE expression to check for the presence/absence of invoices and bde.

Upvotes: 2

Related Questions