Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS
From: tshad on 27 Jun 2010 17:15
The error I was getting was:

Illegal object name A

Actually this was part of a CTE and maybe that is the problem. I just
looked at it and it is more like:

WITH Person1
AS
(
SELECT ...
}
,
Person2
AS
(
SELECT ...
)
SELECT
sFirstname,
sLastName,
nProductID,
VisitDate,
dVisitDate
FROM
(
SELECT
P.sFirstName,
P.sLastName,
PE.nProductID,
CONVERT(varchar,PE.dVisitDate,101),
PE.dVisitDate
From Person P
Join PersonEvent PE on P.PersonID = PE.PersonID
WHERE ...
) AS A
WHERE dStart = (SELECT MAX(dStart) FROM A WHERE A.sFirstName = sFirstName
AND A.sLastName = sLastName AND A.nProductID = ProductID)

If I comment out the dStart=(SELECT MAX(dStart)... line, it works fine.

Thanks,

Tom

"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:i00db9$1kg$1(a)news.eternal-september.org...
> tshad wrote:
>> I could probably put this in a temporary table then do the select off
>> of that but I'd rather use the derived table.
>>
>> Not sure why I can't use the derived table as it is just a table???
>>
>
> And I'm not sure what's preventing you. Are you getting an error
> message? if so, what is it?
> --
> HTH,
> Bob Barrows
>
>


From: tshad on 28 Jun 2010 01:25

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DA1EDBC5FA72Yazorman(a)127.0.0.1...
> tshad (tfs(a)dslextreme.com) writes:
>> I have a result set that I need to filter down to the last date something
>> was done.
>>
>> The problem is that I can't seem to use the derived table in the From
>> clause of my Subquery. The Query looks something like:
>>
>> SELECT
>> sFirstname,
>> sLastName,
>> nProductID,
>> VisitDate,
>> dVisitDate
>> FROM
>> (
>> SELECT
>> P.sFirstName,
>> P.sLastName,
>> PE.nProductID,
>> CONVERT(varchar,PE.dVisitDate,101),
>> PE.dVisitDate
>> From Person P
>> Join PersonEvent PE on P.PersonID = PE.PersonID
>> WHERE ...
>> ) AS A
>> WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
>> and A.sLastName = sLastName and A.nProductID = ProductID)
>
> You can use a CTE instead (if you are on SQL 2005 that is).
>
> WITH A AS (
> SELECT P.sFirstName,
> P.sLastName,
> PE.nProductID,
> CONVERT(varchar,PE.dVisitDate,101),
> PE.dVisitDate
> From Person P
> Join PersonEvent PE on P.PersonID = PE.PersonID
> WHERE ...
> )
> SELECT sFirstname,
> sLastName,
> nProductID,
> VisitDate,
> dVisitDate
> FROM A
> WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName =
> sFirstName
> and A.sLastName = sLastName and A.nProductID = ProductID)
>
> But this is not a fantastic solution; despite the syntax the query in
> the CTE is likely to be executed twice.
>

Why would it be executed twice???

> A better solution is to add this to the CTE:
>
> rowno = row_number() OVER(PARTITION BY P.sFirstName, P.sLastName,
> PE.nPRoductID
> ORDER BY dStart DESC)
>
> In the WHERE clause rather than the subquery, you have:
>
> WHERE rowno = 1
>

Would that give me the 1st record for each person and product?

Thanks,

Tom


From: Erland Sommarskog on 28 Jun 2010 02:27
tshad (tfs(a)dslextreme.com) writes:
>> But this is not a fantastic solution; despite the syntax the query in
>> the CTE is likely to be executed twice.
>>
>
> Why would it be executed twice???

Because the CTE definition into the query at parse time, and the
optimizer is usually not able to see that the same expression appears
in two places. Could be changed in a future version of SQL Server, but
currently it's not that way.

>> A better solution is to add this to the CTE:
>>
>> rowno = row_number() OVER(PARTITION BY P.sFirstName, P.sLastName,
>> PE.nPRoductID
>> ORDER BY dStart DESC)
>>
>> In the WHERE clause rather than the subquery, you have:
>>
>> WHERE rowno = 1
>>
>
> Would that give me the 1st record for each person and product?

That's the idea, but rather then asking - test it!


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: tshad on 28 Jun 2010 10:53

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DA556191AE19Yazorman(a)127.0.0.1...
> tshad (tfs(a)dslextreme.com) writes:
>>> But this is not a fantastic solution; despite the syntax the query in
>>> the CTE is likely to be executed twice.
>>>
>>
>> Why would it be executed twice???
>
> Because the CTE definition into the query at parse time, and the
> optimizer is usually not able to see that the same expression appears
> in two places. Could be changed in a future version of SQL Server, but
> currently it's not that way.
>
Maybe I am missing something here, but which expression appears twice in
this query?

WITH A AS (
SELECT P.sFirstName,
P.sLastName,
PE.nProductID,
CONVERT(varchar,PE.dVisitDate,101),
PE.dVisitDate
From Person P
Join PersonEvent PE on P.PersonID = PE.PersonID
WHERE ...
)
SELECT sFirstname,
sLastName,
nProductID,
VisitDate,
dVisitDate
FROM A
WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
and A.sLastName = sLastName and A.nProductID = ProductID)

Thanks,

Tom

>>> A better solution is to add this to the CTE:
>>>
>>> rowno = row_number() OVER(PARTITION BY P.sFirstName, P.sLastName,
>>> PE.nPRoductID
>>> ORDER BY dStart DESC)
>>>
>>> In the WHERE clause rather than the subquery, you have:
>>>
>>> WHERE rowno = 1
>>>
>>
>> Would that give me the 1st record for each person and product?
>
> That's the idea, but rather then asking - test it!
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: tshad on 28 Jun 2010 10:56

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DA556191AE19Yazorman(a)127.0.0.1...
> tshad (tfs(a)dslextreme.com) writes:
>>> But this is not a fantastic solution; despite the syntax the query in
>>> the CTE is likely to be executed twice.
>>>
>>
>> Why would it be executed twice???
>
> Because the CTE definition into the query at parse time, and the
> optimizer is usually not able to see that the same expression appears
> in two places. Could be changed in a future version of SQL Server, but
> currently it's not that way.
>
>>> A better solution is to add this to the CTE:
>>>
>>> rowno = row_number() OVER(PARTITION BY P.sFirstName, P.sLastName,
>>> PE.nPRoductID
>>> ORDER BY dStart DESC)
>>>
>>> In the WHERE clause rather than the subquery, you have:
>>>
>>> WHERE rowno = 1
>>>
>>
>> Would that give me the 1st record for each person and product?
>
> That's the idea, but rather then asking - test it!
>

I will.

Just don't have access to the database until later today :)

Thanks,

Tom

>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS