UNION Query help

Question

UNION Query help

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
October 16, 2013 at 2:47 am

#299197

I am getting a error message while executing the query below:
Msg 104, Level 16, State 1, Line 18
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
SELECT ISNULL(CboValuesTranslations.cboValueTranslationName,ISNULL(CboValues.cboValueName,'')) as Name,
cboValues.cboValueSqlId as SqlId, CboValues.cboValueIncId as IncId,
LTRIM(STR(cboValues.cboValueSqlId))+'-'+LTRIM(STR(CboValues.cboValueIncId)) AS KeyId
FROM ListsOfCboValues INNER JOIN CboValues ON ListsOfCboValues.listOfCboValueSqlId = CboValues.listOfCboValueSqlId AND ListsOfCboValues.listOfCboValueIncId = CboValues.listOfCboValueIncId AND CboValues.isDeleted=0x0 LEFT OUTER JOIN CboValuesTranslations ON cboValuesTranslations.cboValueSqlId = cboValues.cboValueSqlId AND cboValuesTranslations.cboValueIncId = cboValues.cboValueIncId AND cboValuesTranslations.isDeleted=0x0 AND cboValuesTranslations.LanguageSqlId=2 AND cboValuesTranslations.LanguageIncId=2
WHERE ListsOfCboValues.isDeleted = 0x0 AND (ListsOfCboValues.listOfCboValueCode = 'EAS-00008')
UNION
SELECT 'NULL' as Name,
null as SqlId, null as IncId,
'NULL' AS KeyId
ORDER BY CboValuesTranslations.cboValueTranslationName Asc

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I guess CboValuesTranslations.cboValueTranslationName is not used in certain rows as it is inside an ISNULL function. If CboValuesTranslations.cboValueTranslationName is NULL, another column is used.

I would simply select CboValuesTranslations.cboValueTranslationName and CboValues.cboValueName as two seperate columns and use an outer query to apply the ISNULL logic.

ps: ever heard of aliases? 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 2

Change

ORDER BY CboValuesTranslations.cboValueTranslationName Asc

with

ORDER BY Name Asc

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

use ORDER BY CboValuesTranslations.cboValueTranslationName Asc

for 1st Select statement and then use union with 2nd select statement.

or

Write a Select on top of Statements and then sort...

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 4

Pulivarthi Sasidhar (10/16/2013)
use ORDER BY CboValuesTranslations.cboValueTranslationName Asc
for 1st Select statement and then use union with 2nd select statement.

You will get error "Incorrect syntax near the keyword 'union'."

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 5

It looks like you have 5 columns in 1 query and 4 in the other.

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 6

Greg Edwards-268690 (10/17/2013)
It looks like you have 5 columns in 1 query and 4 in the other.

No, Both has 4 columns.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 7

Greg Edwards-268690

SSC-Insane

Points: 20680

October 17, 2013 at 7:07 am

#1659302

I see I missed (...(...))

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 8

T.Ashish (10/16/2013)
Change
ORDER BY CboValuesTranslations.cboValueTranslationName Asc
with
ORDER BY Name Asc

+1 on this. CboValuesTranslations.cboValueTranslationName is not in your second SELECT....

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 9

If you use aliases, it becomes a bit clearer where another issue might lie.

It looks like you are mixing joins with select criteria.

AND CboValues.isDeleted=0x0 is the first one.

Maybe you copied and pasted and things got mixed up.

I would have this, and some similar, below the WHERE clause.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 10

Or just:

ORDER BY 1

I often avoid using the column names at all in UNIONed queries, as the names somehow seem to

change a lot 🙂 :

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 11

Parentheses

(SELECT ISNULL(CboValuesTranslations.cboValueTranslationName,ISNULL(CboValues.cboValueName,'')) as Name,

cboValues.cboValueSqlId as SqlId, CboValues.cboValueIncId as IncId,

LTRIM(STR(cboValues.cboValueSqlId))+'-'+LTRIM(STR(CboValues.cboValueIncId)) AS KeyId

FROM ListsOfCboValues INNER JOIN CboValues ON ListsOfCboValues.listOfCboValueSqlId = CboValues.listOfCboValueSqlId AND ListsOfCboValues.listOfCboValueIncId = CboValues.listOfCboValueIncId AND CboValues.isDeleted=0x0 LEFT OUTER JOIN CboValuesTranslations ON cboValuesTranslations.cboValueSqlId = cboValues.cboValueSqlId AND cboValuesTranslations.cboValueIncId = cboValues.cboValueIncId AND cboValuesTranslations.isDeleted=0x0 AND cboValuesTranslations.LanguageSqlId=2 AND cboValuesTranslations.LanguageIncId=2

WHERE ListsOfCboValues.isDeleted = 0x0 AND (ListsOfCboValues.listOfCboValueCode = 'EAS-00008')

UNION

SELECT 'NULL' as Name,

null as SqlId, null as IncId,

'NULL' AS KeyId)

ORDER BY 1 Asc

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 12

T.Ashish

SSCarpal Tunnel

Points: 4248

October 17, 2013 at 10:50 pm

#1659552

Good. Now OP has many ways to order his UNION query.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

ScottPletcher (10/17/2013)
Or just:
ORDER BY 1
I often avoid using the column names at all in UNIONed queries, as the names somehow seem to
change a lot 🙂 :

Thank goodness that method has finally been deprecated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 14

Jeff Moden (10/17/2013)
ScottPletcher (10/17/2013)
Or just:
ORDER BY 1
I often avoid using the column names at all in UNIONed queries, as the names somehow seem to
change a lot 🙂 :
Thank goodness that method has finally been deprecated.

+1 for Jeff Moden