?
Solved

UNION PROBLEM

Posted on 2012-08-31
8
Medium Priority
?
482 Views
Last Modified: 2012-09-04
Hi All,

I have query below :

SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266    '

ORDER BY EMPCODE

UNION ALL
 
SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266    '

ORDER BY FULLNAME

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'UNION'.

What's wrong ?

Thank you.
0
Comment
Question by:emi_sastra
8 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38356597
You cannot have two ORDER BY .. the order by after the first SELECT is the problem, having one at the bottom sorts the entire output.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38356605
-You cannot have two ORDER BY .. the order by after the first SELECT is the problem
Ok.

-having one at the bottom sorts the entire output.
I still need to do so.

Please see the more real code below.

SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201207 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC
 UNION ALL
SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201200 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC
 UNION ALL
SELECT
 EmpCode
, SalaryMonth
, NewSalary AS StdAllowanceAmount
 FROM THMSALARYADJ AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth = '201208'
 AND Z.FixedSalaryCode = 'StdAllowanceAmount'

How to solve it ?

Thank you.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38356634
try (untested):

SELECT * FROM (SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201207 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC )v1
 UNION ALL
SELECT * FROM (SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201200 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC)v2
 UNION ALL
SELECT  
 EmpCode
, SalaryMonth
, NewSalary AS StdAllowanceAmount
 FROM THMSALARYADJ AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth = '201208'
 AND Z.FixedSalaryCode = 'StdAllowanceAmount'
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 13

Assisted Solution

by:p_nuts
p_nuts earned 1000 total points
ID: 38356638
SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201207 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 
 UNION ALL
SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201200 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 
 UNION ALL
SELECT
 EmpCode
, SalaryMonth
, NewSalary AS StdAllowanceAmount
 FROM THMSALARYADJ AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth = '201208'
 AND Z.FixedSalaryCode = 'StdAllowanceAmount'

Order by salarymonth desc
0
 
LVL 5

Expert Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 38356754
Try below:

 SELECT z.EmpCode, z.SalaryMonth, z.StdAllowanceAmount FROM (SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201207 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'

 UNION ALL 

SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201200 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 
 UNION ALL 


SELECT
 EmpCode
, SalaryMonth
, NewSalary AS StdAllowanceAmount
 FROM THMSALARYADJ AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth = '201208'
 AND Z.FixedSalaryCode = 'StdAllowanceAmount') A

ORDER BY z.SalaryMonth DESC 

Open in new window

0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 38358069
SELECT * FROM
(SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201207 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC
) t1
 UNION ALL
SELECT * FROM (
SELECT
 TOP 1
 EmpCode, SalaryMonth, StdAllowanceAmount
 FROM THMSALARY201200 AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth <= '201208'
 ORDER BY SalaryMonth DESC) t2
 UNION ALL
SELECT
 EmpCode
, SalaryMonth
, NewSalary AS StdAllowanceAmount
 FROM THMSALARYADJ AS Z
 WHERE Z.EmpCode = '0060'
 AND Z.SalaryMonth = '201208'
 AND Z.FixedSalaryCode = 'StdAllowanceAmount'
0
 
LVL 9

Expert Comment

by:keyu
ID: 38363018
(SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266')
UNION ALL
 (SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266')
ORDER BY EMPCODE,FULLNAME
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 38366308
Hi All,

Thank you very much for your help.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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