CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Riyan
Riyan

Reputation: 117

Postgres WHEN case with Select query

I have a Postgres WHEN case query error. How can I fix the query?

SELECT CASE
            WHEN  AccountStatus='Open' 
           THEN
             (
              SELECT  * 
               from Accounts
               where statusID=1
             )

        WHEN  AccountType='Mutual'
           THEN
             (
               SELECT *
               FROM Accounts
               WHERE AccountTypeID=2
             )

   END as Status, *
FROM Accounts

Showing Error:

more than one row returned by a subquery used as an expression

Upvotes: 2

Views: 2365

Answers (2)

Amit
Amit

Reputation: 142

You should try this:- 

SELECT *
FROM Accounts
WHERE CASE  WHEN (AccountStatus='Open' ) THEN statusID=1 
            WHEN (AccountType='Mutual')THEN AccountTypeID=2 
      END;

Upvotes: 1

Dr. X
Dr. X

Reputation: 2920

I think your problem is not a case query, you want to get the table row given as below. If you really want to use case when type query please define the tables in detail.

SELECT *, 'Open' AS Status from Accounts where statusID = 1
UNION ALL
SELECT *, 'Mutual' AS Status FROM Accounts WHERE AccountTypeID = 2

Upvotes: 1

Related Questions