Solved

# UNION PROBLEM

Posted on 2012-08-31
469 Views
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
Question by:emi_sastra

LVL 25

Expert Comment

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

-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

LVL 25

Accepted Solution

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

LVL 13

Assisted Solution

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

LVL 5

Expert Comment

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

LVL 8

Expert Comment

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

LVL 9

Expert Comment

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

LVL 1

Author Closing Comment

Hi All,

Thank you very much for your help.
0

## Featured Post

### Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…