CODEWITHSUNDEEP
postgresqlpostgresql-8.4
mulrus
mulrus

Reputation: 85

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

I got very good help for my question PostgreSQL: return message after count = 0

This query works fine:

SELECT  CASE WHEN COUNT(*)  = 0 THEN 'NO RESULT'
            ELSE CAST(COUNT(*) as TEXT)
        END as myfield from mytable

But today I need if some rows are in table, I need to show select result.

I tried:

SELECT  CASE WHEN COUNT(*)  = 0 THEN 'NO RESULT'
             ELSE (select name from mytable)
        END as myfield from mytable

And I got error:

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

I found that IN function but don't have any idea to improve it.

Upvotes: 2

Views: 1476

Answers (1)

Arion
Arion

Reputation: 31239

I think you can do something like this:

select name from mytable
UNION ALL
SELECT 'NO RESULT'
WHERE NOT EXISTS(SELECT NULL FROM mytable)

Upvotes: 2

Related Questions