All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Why the following sql has the error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


select a.id, shortcode, c.currency, a.type from account a inner join currency c on a.currencyid = c.id WHERE a.id > -1 union select -2 as id, '-- Please Select --' as shortcode from account a order by a.id

Open in new window

techquesAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Try this query out:

Removed ORDER BY clause because UNION doesn't allows it.

SELECT a.id,shortcode,c.currency,a.type
FROM   account a
INNER JOIN currency c
  ON   a.currencyid = c.id
WHERE  a.id > -1 
UNION 
SELECT -2 AS id,'-- Please Select --' AS shortcode, NULL, null
FROM   account a

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can use order by (no points here) like this:

SELECT a.id,shortcode,c.currency,a.type
FROM   account a
INNER JOIN currency c
  ON   a.currencyid = c.id
WHERE  a.id > -1 
UNION 
SELECT -2 AS id,'-- Please Select --' AS shortcode, NULL, null
FROM   account a
ORDER BY 1

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.