Postgres WHEN case with Select query

Question

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

Those two subqueries, because they are in the SELECT portion of your query, can only return a single value. I suspect you have more than one record in your Accounts table that has a StatusID=1 or StatusID=2. How would you suspect it would display MANY records in a single record of your result set? If you share some sample data and your desired results we can guide you to a proper way of getting what you want. — JNevill
– JNevill, Commented Aug 23, 2017 at 20:14

Amit · Accepted Answer · 2017-09-12 06:12:37Z

1

You should try this:-

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

answered Sep 12, 2017 at 6:12

Amit

14210 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dr. X · Accepted Answer · 2017-08-23 20:19:53Z

1

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

answered Aug 23, 2017 at 20:19

Dr. X

2,9402 gold badges18 silver badges37 bronze badges

Collectives™ on Stack Overflow

Postgres WHEN case with Select query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related