a substitute for UNION?

[bbla32]

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

[David Portas]

On 19 Feb, 20:46, [e-mail] 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/m...S,SQL.90).aspx
--

[bbla32]

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?

[bbla32]

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]

[Tibor Karaszi]

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
<bb****@op.plwrote in message news:11**********************@m58g2000cwm.googlegr oups.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]

[bbla32]

On Feb 19, 10:31 pm, "Tibor Karaszi"
<[e-mail] rote:

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

Thanks!

Can I write a faster query?

[Gert-Jan Strik]

bb****@op.pl wrote:

>
On Feb 19, 10:31 pm, "Tibor Karaszi"
<[e-mail] rote:

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

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,ObsoleteDate)

- Remove unnecessary IN clauses

Gert-Jan

[Roy Harvey]

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, bb****@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]

[bbla32]

On Feb 19, 11:33 pm, Roy Harvey <[e-mail]:

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.

[Roy Harvey]

On 19 Feb 2007 15:01:46 -0800, bb****@op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <[e-mail]:

>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?

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

[bbla32]

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.

That makes sense. I have a problem with your query though: in the
construct like below

SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END

when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?

[bbla32]

when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
I changed to

WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...

and

WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...

I suppose it should be ok now.

[Roy Harvey]

On 19 Feb 2007 15:53:06 -0800, bb****@op.pl wrote:

>when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?

I changed to

WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...

and

WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...

I suppose it should be ok now.

That is the problem with not haveing the real table definitions and
test data to work with. Your correction is fine.

Roy Harvey
Beacon Falls, CT