Help while doing UNION... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help while doing UNION...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-07 : 07:57:13

Hello.

Mine Query A is returning me...

ID Amt
2106 625
2140 625
2123 625
2108 625

Mine Query B is returning me...

ID Amt
2103 625
2111 625
2123 625
2108 625

When i am doing A UNION B then it will returning me...

ID Amt
2103 625.00
2106 625.00
2108 625.00
2111 625.00
2123 625.00
2140 625.00

---------------------------------------------------------------------
I need output like this...

ID Amt
2106 625.00
2140 625.00
2123 625.00
2108 625.00
2103 625.00
2111 625.00

Can any one help me???

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-07 : 08:00:39

not sure i really understand your problem... is it just the order you have an issue with?

Em

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-07 : 08:02:01

Yes....

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-07 : 08:05:15

how about something like...

select ID,Amt
from
(
select ID,Amt,1 as tab_ord
from TableA
UNION
select ID,Amt,2
from TableB
) table_union
order by tab_ord

Em

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-07 : 08:14:39

Mine Query is ...

SELECT Row_Number() OVER (ORDER BY Deal, BrokerID) Line_No,
BrokerID, Brokerage
FROM
(
SELECT
Deal,
'BrokerID' = CASE WHEN y.ID = 1 THEN CP1B1
ELSE CASE WHEN y.ID = 2 THEN CP1B2
ELSE CASE WHEN y.ID = 3 THEN CP1B3
ELSE CP1B4
END
END
END,
'Brokerage' = CASE WHEN y.ID =1 THEN CP1A1
ELSE CASE WHEN y.ID = 2 THEN CP1A2
ELSE CASE WHEN y.ID = 3 THEN CP1A3
ELSE CP1A4
END
END
END
FROM DBO.TRADE X,
(
SELECT 1 ID UNION ALL
SELECT 2 ID UNION ALL
SELECT 3 ID UNION ALL
SELECT 4 ID
) Y
WHERE
Month(X.MODIFY_DATE)<= Month(@P_Start_Date) and
Month(X.MODIFY_DATE)>= Month(@P_END_Date) and
CONVERT(SMALLDATETIME, CONVERT(NVARCHAR, X.MODIFY_DATE,101)) between @P_Start_Date and @P_END_Date and
X.Version = ( SELECT MaX(Version) FROM DBO.Trade where Deal = X.Deal and
Month(MODIFY_DATE)<=Month(@P_Start_Date) and
Month(MODIFY_DATE)>=Month(@P_END_Date)
) AND
X.ACTION_TYPE != 'D' AND
Deal = @Deal

-- =================================================================================================================
UNION --ALL
-- =================================================================================================================

SELECT
Deal,
'BrokerID' = CASE WHEN y.ID = 1 THEN CP1B1
ELSE CASE WHEN y.ID = 2 THEN CP1B2
ELSE CASE WHEN y.ID = 3 THEN CP1B3
ELSE CP1B4
END
END
END,
'Brokerage' = CASE WHEN y.ID =1 THEN CP1A1
ELSE CASE WHEN y.ID = 2 THEN CP1A2
ELSE CASE WHEN y.ID = 3 THEN CP1A3
ELSE CP1A4
END
END
END
FROM DBO.TRADE X,
(
SELECT 1 ID UNION ALL
SELECT 2 ID UNION ALL
SELECT 3 ID UNION ALL
SELECT 4 ID
) Y
WHERE
Month(X.MODIFY_DATE)>=DatePart(Month,(@P_Start_Date))-1 and
Month(X.MODIFY_DATE)<=DatePart(Month,(@P_END_Date))-1 and
X.Version = ( SELECT MAX(Version) FROM DBO.Trade WHERE Deal= X.Deal and
Month(X.MODIFY_DATE)<= DatePart(Month, @P_Start_Date)-1 and
Month(X.MODIFY_DATE)>= DatePart(Month, @P_END_Date)-1
) AND
X.ACTION_TYPE != 'D' AND
Deal = @Deal
) X

--------------------------------------------------------------------

Now can you please help me???

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-07 : 08:22:50

I guess by using Union all output will be what you are expecting but you will get duplicate records which you have to eliminate.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-07 : 08:25:00

i am using UNION Only..

And i am not getting output the way i want it...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 13:34:56

[code]SELECT Row_Number() OVER (ORDER BY Deal, BrokerID) Line_No,
BrokerID, Brokerage
FROM
(
SELECT
Deal,
'BrokerID' = CASE WHEN y.ID = 1 THEN CP1B1
ELSE CASE WHEN y.ID = 2 THEN CP1B2
ELSE CASE WHEN y.ID = 3 THEN CP1B3
ELSE CP1B4
END
END
END,
'Brokerage' = CASE WHEN y.ID =1 THEN CP1A1
ELSE CASE WHEN y.ID = 2 THEN CP1A2
ELSE CASE WHEN y.ID = 3 THEN CP1A3
ELSE CP1A4
END
END
END,
1 AS Ord
FROM DBO.TRADE X,
(
SELECT 1 ID UNION ALL
SELECT 2 ID UNION ALL
SELECT 3 ID UNION ALL
SELECT 4 ID
) Y
WHERE
Month(X.MODIFY_DATE)<= Month(@P_Start_Date) and
Month(X.MODIFY_DATE)>= Month(@P_END_Date) and
CONVERT(SMALLDATETIME, CONVERT(NVARCHAR, X.MODIFY_DATE,101)) between @P_Start_Date and @P_END_Date and
X.Version = ( SELECT MaX(Version) FROM DBO.Trade where Deal = X.Deal and
Month(MODIFY_DATE)<=Month(@P_Start_Date) and
Month(MODIFY_DATE)>=Month(@P_END_Date)
) AND
X.ACTION_TYPE != 'D' AND
Deal = @Deal

-- =================================================================================================================
UNION --ALL
-- =================================================================================================================

SELECT
Deal,
'BrokerID' = CASE WHEN y.ID = 1 THEN CP1B1
ELSE CASE WHEN y.ID = 2 THEN CP1B2
ELSE CASE WHEN y.ID = 3 THEN CP1B3
ELSE CP1B4
END
END
END,
'Brokerage' = CASE WHEN y.ID =1 THEN CP1A1
ELSE CASE WHEN y.ID = 2 THEN CP1A2
ELSE CASE WHEN y.ID = 3 THEN CP1A3
ELSE CP1A4
END
END
END,
2 AS Ord
FROM DBO.TRADE X,
(
SELECT 1 ID UNION ALL
SELECT 2 ID UNION ALL
SELECT 3 ID UNION ALL
SELECT 4 ID
) Y
WHERE
Month(X.MODIFY_DATE)>=DatePart(Month,(@P_Start_Date))-1 and
Month(X.MODIFY_DATE)<=DatePart(Month,(@P_END_Date))-1 and
X.Version = ( SELECT MAX(Version) FROM DBO.Trade WHERE Deal= X.Deal and
Month(X.MODIFY_DATE)<= DatePart(Month, @P_Start_Date)-1 and
Month(X.MODIFY_DATE)>= DatePart(Month, @P_END_Date)-1
) AND
X.ACTION_TYPE != 'D' AND
Deal = @Deal
) X
ORDER BY X.Ord[/code]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-08 : 01:55:27

Thanks visakh16.

Still i am facing same problem. I think by just doing ordering, it will not solve the problem.

I want to maintain order while doing (A) U (B)

Ex..
A = {1,2,5}
B = {2,4,6}

then I need Answer (A) U (B) = {1,2,5,4,6}

-- Regards
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 05:45:14

quote:
Originally posted by hirani_prashant

Thanks visakh16.

Still i am facing same problem. I think by just doing ordering, it will not solve the problem.

I want to maintain order while doing (A) U (B)

Ex..
A = {1,2,5}
B = {2,4,6}

then I need Answer (A) U (B) = {1,2,5,4,6}

-- Regards
Prashant Hirani

Can you try dumping result onto a temporary table with an id column and then retrieving from it order by id and ord?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-08 : 23:35:11

If I understand your problem correctly, I think you can do this by also outer joining your second table to your first and only showing those that have have not already occurred in the first.

select * from
(
select 1 as ord, * from A
union all
select 2 as ord, * from B left outer join A on (...)
where A.PK is null
) X
order by ord,...

You might find CTEs useful here as you need to use A twice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources