asked on 3/13/2009

need help with sql syntax

Hi, I'm doing the following call
SELECT cts,landread,devread,(SELECT DISTINCT (case when a.landread != 2 then 1 else 0 end) as returnvar2 FROM "DUB"."ctsnotes" as a WHERE cts = a.cts)as returnvar FROM "DUB"."ctsnotes" as b
This returns "ERROR: more than one row returned by a subquery used as an expression"

If I replace a.cts wtih a number it doesn't report an error.

I'm using Postgres for this call.

Any ideas guys ?

Databases PostgreSQL SQL

Last Comment

8080_Diver

3/13/2009

Pratima

3/13/2009

SELECT cts,landread,devread,
(SELECT DISTINCT Top 1 (case when a.landread != 2 then 1 else 0 end) as returnvar2
FROM "DUB"."ctsnotes" as a WHERE cts = a.cts)as returnvar
FROM "DUB"."ctsnotes" as b

Netlink2

3/13/2009

ASKER

Thanks pratima, I can see what you're getting at, but I get an error at "Top1":
ERROR: syntax error at or near "1"
LINE 2: (SELECT DISTINCT Top 1 (case when a.landread != 2 then 1 el...

ASKER CERTIFIED SOLUTION

Pratima

3/13/2009

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Pratima

3/13/2009

"Top1" must have space etween like this Top 1

Netlink2

3/13/2009

ASKER

Sorry, it was only my comment which had Top1 in it, the actual code was Top 1

Netlink2

3/13/2009

ASKER

OK I think I got it, Postgres doesn't use top
SELECT cts,landread,devread,
(SELECT (case when a.landread != 2 then 1 else 0 end) as returnvar2
FROM "DUB"."ctsnotes" as a WHERE cts = a.cts LIMIT 1)as returnvar
FROM "DUB"."ctsnotes" as b

Will get back to you soon

Pratima

3/13/2009

8080_Diver

3/13/2009

I am not entirely sure what you are trying to achieve with the inner select but I think it is equivalent to either SQL_A or SQL_B below:

--SQL_A
 
SELECT  cts,
        landread,
        devread,
        case when a.landread != 2 
             then 1  
             else 0 
        end as returnvar
FROM "DUB"."ctsnotes"  as b
 
 
--SQL_B
 
SELECT  DISTINCT
        cts,
        landread,
        devread,
        case when a.landread != 2 
             then 1  
             else 0 
        end as returnvar
FROM "DUB"."ctsnotes"  as b

Open in new window