CODEWITHSUNDEEP
phppostgresqlsubquery
noob2
noob2

Reputation: 21

subquery must return only one column

This query runs successfully in pgAdmin, but when I transfer it in zend I get:
ERROR: subquery must return only one column...

Can someone distinguish the problem?

  SELECT "trail_history"."new_audit_trail".*,
         (SELECT "admin".list_category_values_new."values"
            FROM "admin".list_category_values_new
           WHERE CAST(seq  AS character varying) = "trail_history"."new_audit_trail"."current_value"
             AND "name" = "trail_history"."new_audit_trail"."name") as "values"
    FROM "trail_history"."new_audit_trail" 
   WHERE (capno LIKE '12101062411001%') 
     AND (recon = '0') 
ORDER BY "date_happened" DESC

Upvotes: 2

Views: 5351

Answers (1)

Joel Brown
Joel Brown

Reputation: 14388

Your sub-select SELECT "admin".list_category_values_new."values"... has nothing that prevents it from returning multiple rows. You need to use TOP 1 or MAX or something to ensure that only one record comes out of the sub-select.

You can correlate the sub-query so that each record in your main select gets a different single value, but if you're going to use a sub-select it can only return one row per row of output in your main select.

Upvotes: 2

Related Questions