a substitute for UNION?

I'd like to change query:
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, COn 19 Feb, 20:46, bbl...@.op.pl wrote:
> I'd like to change query:
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
> to one SELECT like this
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
> or (say DM has columns A, B, C):
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C
Use a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Use a CASE expression. To adapt your second example:
> SELECT col1, col2, col3,
> CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
> CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
> WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?

> Always avoid using SELECT *.
Why?|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @.pp INT
SET @.pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
)
GROUP BY [ID]|||You are missing naming the derived table. Note the AS tbl at the end of the
inner query:
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bbla32@.op.pl> wrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegroups.com..
.
> I'll show you my query which doesn't want to compile for some reason
> (Incorrect syntax near the keyword GROUP):
> DECLARE @.pp INT
> SET @.pp = 7
>
> SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
> FROM (
> SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.SenderID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverPersonID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
> GROUP BY [ID]
>|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You are missing naming the derived table. Note the AS tbl at the end of th
e inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?|||bbla32@.op.pl wrote:
> On Feb 19, 10:31 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Thanks!
> Can I write a faster query?
A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"
- Make sure you have proper indexes in place. For example on
DocumentMessages(SenderID,ObsoleteDate) and on
DocumentMessages(ReceiverPersonID,Obsole
teDate)
- Remove unnecessary IN clauses
Gert-Jan|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.
SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]
I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.
Roy Harvey
Beacon Falls, CT
On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>DECLARE @.pp INT
>SET @.pp = 7
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>FROM (
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
>GROUP BY [ID]|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Assuming I didn't make a mistake - the code is untested of course -
> the following might perform slightly better. Or not, a lot depends on
> the indexing and actual data.
Great example!

> as an EXISTS test. In any case the use of subqueries in the CASE
> should limit the number of times the subqueries are executed, which is
> where any performance gain will come from.
Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>Well, wouldn't it execute slower since the subquery is within CASE?
No reason why that should be an issue.
The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.
Roy Harvey
Beacon Falls, CT