A WHERE in a Union query

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable

Does anyone know how to write this correctly?Not tested it, but something like this:

select YearList from
(select distinct Cas YearList
from dbo.ViewPressReleases
where YearList like myvariable) SomeNameYouLike
union
select datepart(yyyy, getdate())
order by YourOrderField|||ok thanks,

so now i have

select YearList from (select distinct DatePart(yyyy,[PressreleaseDate]) as YearList
from dbo.ViewPressReleases where Yearlist like stgetstyear) mixedyearlist
union select datepart(yyyy, getdate()) order by Yearlist

but i get an error that Yearlist in there WHERE statment is invalid.

any ideas?|||you can't use your alias in the where clause, so you have to reuse the DATEPART function again. Also, your DATEPART returns an integer value. If you want to perform a like with a wildcard character (%) on it, you can use the DATENAME function which returns a string.|||i actually want the integer. This was my origianal probelm; that you cant use an alias in a where clause, but i cant simply use the "datepart..." as its part of a union query and i need to filter the resulting list of the union query, if i've explained myself clearly.|||no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find|||Originally posted by r937
no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find

Ok...

i have

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])

i want to then filter the resulting list of years by myvariable

so i need to add

WHERE ??? LIKE myvariable

if this was not a union query i would write something like

WHERE DatePart(yyy,[PressreleaseDate]) LIKE my variable

but as it is a union query i asume i have to write somethin like

WHERE (SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])) LIKE my viable

(which doesnt work) and i know i cant simply use the alias like:

WHERE Yearlist Like myvaiable

so i need to know how to phrase the SQL corrctly in order to filter the list of years.|||no, don't explain in terms of sql, you already tried that ;)

you cannot use LIKE on integers

try explaining it in english

"i want to select only years which ... ?"|||I Think i have it.

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases WHERE DatePart(yyyy,[PressreleaseDate]) LIKE stgetstyear UNION SELECT datepart(yyyy, getdate()) WHERE datepart(yyyy, getdate()) LIKE stgetstyear ORDER BY DatePart(yyyy,[PressreleaseDate])

Thanks.sql

A UNION made in heaven, or hades?

Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.

However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.

I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:

"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"

Perhaps I am getting too fancy, but thought I could do it in a single select.

here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9

INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9

select sym, rank
from ( select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym

DROP TABLE tMyPicks
DROP TABLE tUrPicks

This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.

IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.

Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.

I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(I'd suggest:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
ORDER BY 1
-PatP|||How about a slightly different tack...

select you.sym, you.rank , me.sym, me.rank
from tMyPicks me full outer join tUrPicks you on me.sym = you.sym
where (you.sym is null or me.sym is null)
or you.rank <> me.rank
order by you.sym

Sniped for using longer table synonyms ;-)|||Sniped for using longer table synonyms ;-)It's a rough neighborhood, what can I say?

-PatP|||YESSSSS!!!

You guys RULE!!! I KNEW I was making it too hard...and there you are, Pat...allowing me an opportunity to use my favorite function, COALESCE!!! *LOL*

Thanks much guys...I sincerely (as always) appreciate your time!!!|||Dang...so close...Actually, I left out an important aspect of my problem...one that throws a monkey wrench into the whole deal.

Alas, There can also be situations (typically at the lower end of the ranked list) in which the symbol is NOT in BOTH of the two tables. That's where my UNION was helpful...

That complicates the join, I know...if anyone has a quick adjustment, that would be much appreciated, otherwise I'll keep playing around with things. I adjusted the original post to show the data as it could be out there...

I did notice that my test data in my code above did not address this situation either...thank goodness for the ability to test against the live data ;)|||symbol not in both tables is covered by FULL OUTER JOIN

MCrowley's WHERE clause handles it well|||Then I'd use what is basically MCrowley's solution, something like:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1-PatP|||Wow, and I still get to use COALESCE *LOL*

Seriously, thanks again for your help. It seems so plain and obvious once someone else writes it down (oh, and that "figures it out" part too. ;) )

Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know.|||"Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know."

Be aware that such a situation would result in your immediate and permanent banishment from the forum. :)|||It's a rough neighborhood, what can I say?

And I would not have it any other way.

The thought occurs to me, though, that the data should probably all be in a single table of picks. Then it would be easier to get a third, fourth, or fifth set of picks in the database. The query would change a little bit to something like:

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM (select * from picks where picker = 'me') AS m
FULL OUTER JOIN (select * from picks where picker = 'you') AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1|||Well, I would also take that tack if'n it was up to me, and it fit with the plan ;) However, what y'all did not know (because, as you know, knowledge is power, and I only let on what I dared to let on...) is that I am just writing a stored proc that I can use to verify daily processing results in two different, but identical databases on different servers.

We have a production system that uses the Poor Man's Redundancy scheme...that is, two servers that, under the best of situations, contain databases that are *coff, coff* MIRRORS of each other, but are completely independent. Both start off with the same data in "identical" databases, and theoretically, after importing the "same" data from the "same" source each day, process independently (using the "same" stored procedures) and should, theoretically, arrive at the end of the processing day with exactly the same data in the respective databases.

That said, all the quotes should tell ya that it doesn't always happen that way. Sometimes an FTP or fails, and imported data isn't "identically" imported to each half of the mirrored system. What I am doing is trying to write a quick and dirty proc that checks one part of the end-result on each server, then compares the stock rankings to make sure both systems arrived at the same results at the end of the day.

If nothing else, this allows me to jump on the problem BEFORE the data gets out to the end users, and resolve any system/data issues that cause a disagreement between the two servers.

*phew* That said, I also neglected to show in my original code posting that there is also a DATE aspect of the select, so I had to play around with the code you kind gentlement provided yesterday in order to take that into account.

Here, for the sake of posterity (or is that posteriority?), is what I ended up with:CREATE TABLE tMyPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

CREATE TABLE tUrPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

INSERT INTO tMyPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101), 'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'HERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XHERE', 9

INSERT INTO tUrPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101),'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'NHERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XNHERE', 9

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON ((u.myDate = m.myDate) AND (u.sym = m.sym))
WHERE (COALESCE(U.mydate, M.myDate) = '2005-01-24') AND
((m.rank <> u.rank) OR (m.sym IS NULL) OR (u.sym IS NULL))
ORDER BY 1

DROP TABLE tMyPicks
DROP TABLE tUrPicks

this results in output that I need, which is:

XBFD 4 5
XHERE 9 NULL
XIMA 5 7
XNHERE NULL 9
XSOB 7 8
XSORU 8 4

As always, thanks for your help!

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, C
On 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]
|||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 the inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?
|||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
|||> 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?
|||> 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.

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

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:

Quote:

Originally Posted by

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/...US,SQL.90).aspx
--|||Use a CASE expression. To adapt your second example:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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.plwrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

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.comwr ote:

Quote:

Originally Posted by

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?|||bbla32@.op.pl wrote:

Quote:

Originally Posted by

>
On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.comwr ote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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!

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

>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|||The best way to make the subqueries faster is to run them less. If

Quote:

Originally Posted by

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?|||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.|||On 19 Feb 2007 15:53:06 -0800, bbla32@.op.pl wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>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

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 the 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:
> > 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|||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:
>> 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|||> 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?|||> 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.|||On 19 Feb 2007 15:53:06 -0800, bbla32@.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