Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UNION Query help Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 2:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 594, Visits: 1,097
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



Post #1505112
Posted Wednesday, October 16, 2013 2:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,966, Visits: 10,741
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505115
Posted Wednesday, October 16, 2013 3:10 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
Change
ORDER BY CboValuesTranslations.cboValueTranslationName Asc
with
ORDER BY Name Asc
Post #1505116
Posted Wednesday, October 16, 2013 3:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:03 AM
Points: 1,062, Visits: 708
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...
Post #1505117
Posted Wednesday, October 16, 2013 3:35 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
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'."


Post #1505121
Posted Thursday, October 17, 2013 5:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 667, Visits: 6,708
It looks like you have 5 columns in 1 query and 4 in the other.
Post #1505628
Posted Thursday, October 17, 2013 7:00 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
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.
Post #1505684
Posted Thursday, October 17, 2013 7:07 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 667, Visits: 6,708
I see I missed (...(...))
Post #1505687
Posted Thursday, October 17, 2013 7:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 986, Visits: 1,327
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
Post #1505714
Posted Thursday, October 17, 2013 1:33 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 667, Visits: 6,708
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.
Post #1505887
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse