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

# 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
2 Solutions

Commented:
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

Author 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
WHERE Z.EmpCode = '0060'
AND Z.SalaryMonth = '201208'
AND Z.FixedSalaryCode = 'StdAllowanceAmount'

How to solve it ?

Thank you.
0

Commented:
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
WHERE Z.EmpCode = '0060'
AND Z.SalaryMonth = '201208'
AND Z.FixedSalaryCode = 'StdAllowanceAmount'
0

Commented:
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
WHERE Z.EmpCode = '0060'
AND Z.SalaryMonth = '201208'
AND Z.FixedSalaryCode = 'StdAllowanceAmount'

Order by salarymonth desc
0

Commented:
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
WHERE Z.EmpCode = '0060'
AND Z.SalaryMonth = '201208'
AND Z.FixedSalaryCode = 'StdAllowanceAmount') A

ORDER BY z.SalaryMonth DESC
``````
0

Commented:
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
WHERE Z.EmpCode = '0060'
AND Z.SalaryMonth = '201208'
AND Z.FixedSalaryCode = 'StdAllowanceAmount'
0

Commented:
(SELECT * FROM TMEMPLOYEE
WHERE EmpCode =  '0266')
UNION ALL
(SELECT * FROM TMEMPLOYEE
WHERE EmpCode =  '0266')
ORDER BY EMPCODE,FULLNAME
0

Author 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.