subquery must return only one column

Question

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

Rob Bednark · Accepted Answer · 2020-07-16 06:10:46Z

2

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.

edited Jul 16, 2020 at 6:10

Rob Bednark

28.8k28 gold badges90 silver badges131 bronze badges

answered Jul 13, 2011 at 3:51

Joel Brown

14.5k4 gold badges57 silver badges68 bronze badges

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

3 Comments

Explosion Pills Over a year ago

Confusion of terms here. The error is that the subquery should return only one column, but it selecting more than one. However, you are correct .. it can only return one row too with the way it is used here. If the column error is corrected, the row error would be next.

Joel Brown Over a year ago

I assumed @OP's question contained a minor error in terminology. Clearly the indicated subquery is only returning one column. The possibility of multiple rows is obviously going to be an issue, however.

Vladimir Protasov Over a year ago

@joel-brown Looks like PostgreSQL got the minor error in terminology: it's standart error message.

Collectives™ on Stack Overflow

subquery must return only one column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related