• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

UNION PROBLEM

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
emi_sastra
Asked:
emi_sastra
2 Solutions
 
lwadwellCommented:
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
 
emi_sastraAuthor Commented:
-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
 
lwadwellCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
p_nutsCommented:
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
 
VIVEKANANDHAN_PERIASAMYCommented:
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
 
Andrei FomitchevCommented:
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
 
keyuCommented:
(SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266')
UNION ALL
 (SELECT * FROM TMEMPLOYEE
 WHERE EmpCode =  '0266')
ORDER BY EMPCODE,FULLNAME
0
 
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now