Solved

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

Posted on 2009-04-11
2
1,804 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:techques
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 50 total points
ID: 24122731
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24122810
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
SQl Agent job fails--SSIS package looses password 6 53
MS SQL page split per second is high 19 103
Query 14 57
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question