February 17, 2016 at 9:18 am
Hi
I am having trouble with a where clause on a sub query (or left join eventually)
This works...
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
AND [LotJob] = '0000337495'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
this doesn't...
select *
from
(
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
--and [LotJob] = '0000337495'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
) a
where [LotNumber] = '000337495'
is there something i'm missing?
Thanks
February 17, 2016 at 9:30 am
Those two queries are not logically equivalent.
The first restricts the rows involved to rows with a TrnType of 'S' and a LotJob of '0000337495', and then performs the aggregation (which incidentally means MAX(LotJob) will always be '0000337495').
The second restricts rows involved to rows with a TrnType of 'S', performs the aggregations, and only then checks for aggregated rows with a MAX(LotJob) of '0000337495'. It may very well be that no combination of [StockCode], [SalesOrder], [SalesOrderLine] has an unrestricted MAX(LotJob) of '0000337495', which would lead to the results you're seeing.
Since they do very different things, the important questions are 1) what is it that you need the query to do, and 2) assuming the answer to question 1 is what the first query does (since you say it works), then what is the goal of rewriting it in the alternate form?
Cheers!
February 17, 2016 at 9:31 am
Yes.
The first query only considers rows where [LotJob] = '0000337495'.
The second first gets the max LotJob and then that max value must be = '0000337495' for the row to be selected. No such groups exist, the max value is always something else.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 17, 2016 at 9:33 am
The max lotjob for the groups of [StockCode], [SalesOrder], [SalesOrderLine] might not be the same that the one in your filter.
In the first query, you're returning all the groups that contain that certain LotJob, and it will always be the MAX, because it's the only one. In the second query, you're filtering after you group.
The second query is equivalent to this:
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
HAVING MAX([LotJob]) = '0000337495';
February 17, 2016 at 10:39 am
Thanks for the replies..
perhaps i should have given my final expectations...
i have 2 tables which i'm trying to join. I know the lot exists under the conditions in the subquery but i get null values in the results
select t1.[StockCode], t1.[SalesOrder], t1.[SalesOrderLine], t1.[LotNumber], t1.[Customer], t2.[ShipDate]
from table t1
left join
(
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
) t2
on t1.LotNumber = t2.LotNumber
where LotNumber = '0000337495'
February 17, 2016 at 11:55 am
spin (2/17/2016)
Thanks for the replies..perhaps i should have given my final expectations...
i have 2 tables which i'm trying to join. I know the lot exists under the conditions in the subquery but i get null values in the results
select t1.[StockCode], t1.[SalesOrder], t1.[SalesOrderLine], t1.[LotNumber], t1.[Customer], t2.[ShipDate]from table t1
left join
(
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
) t2
on t1.LotNumber = t2.LotNumber
where LotNumber = '0000337495'
If the error message SQL Server throws is about LotNumber being ambiguous, that's because SQL Server wants to know if you want it to use the LotNumber columns from t1 or from t2 in the WHERE clause.
If that is not your problem, then please post a script that we can use to reproduce the issue: CREATE TABLE statements for tables involved, INSERT statements with a handful rows of sample data, the results you want to get from that data (with an explanation), and the resullts or error messages you now get.
February 17, 2016 at 4:18 pm
Since you're not returning anything from table t2 there is no point in using a JOIN.
Check for existence would do just fine:
select t1.[StockCode], t1.[SalesOrder], t1.[SalesOrderLine], t1.[LotNumber], t1.[Customer], t2.[ShipDate],
from table t1
WHERE EXISTS (
SELECT *
FROM [EncoreCompanyA].[dbo].[LotTransactions] LT
WHERE TrnType = 'S'
AND LT.[LotJob] = t1.LotNumber
)
AND LotNumber = '0000337495'
_____________
Code for TallyGenerator
February 17, 2016 at 4:22 pm
Sergiy (2/17/2016)
Since you're not returning anything from table t2 there is no point in using a JOIN.Check for existence would do just fine:
select t1.[StockCode],t1.[SalesOrder],t1.[SalesOrderLine],t1.[LotNumber],t1.[Customer],t2.[ShipDate][/i],
from table t1
WHERE EXISTS (
SELECT *
FROM [EncoreCompanyA].[dbo].[LotTransactions] LT
WHERE TrnType = 'S'
AND LT.[LotJob] = t1.LotNumber
)
AND LotNumber = '0000337495'
It was hiding 🙂
Cheers!
February 17, 2016 at 4:27 pm
Jacob Wilkins (2/17/2016)
Sergiy (2/17/2016)
Since you're not returning anything from table t2 there is no point in using a JOIN.Check for existence would do just fine:
select t1.[StockCode],t1.[SalesOrder],t1.[SalesOrderLine],t1.[LotNumber],t1.[Customer],t2.[ShipDate][/i],
from table t1
WHERE EXISTS (
SELECT *
FROM [EncoreCompanyA].[dbo].[LotTransactions] LT
WHERE TrnType = 'S'
AND LT.[LotJob] = t1.LotNumber
)
AND LotNumber = '0000337495'
It was hiding 🙂
Cheers!
Sorry, did not scroll far enough to the right. 🙂
Then the question remains: what's the purpose of the query?
Why do you need to return in the same line:
t1.[LotNumber] and MAX([LotJob]),
t2.[ShipDate] and max([TrnDate]) ShipDate
?
_____________
Code for TallyGenerator
February 18, 2016 at 2:57 am
Sergiy (2/17/2016)
Jacob Wilkins (2/17/2016)
Sergiy (2/17/2016)
Since you're not returning anything from table t2 there is no point in using a JOIN.Check for existence would do just fine:
select t1.[StockCode],t1.[SalesOrder],t1.[SalesOrderLine],t1.[LotNumber],t1.[Customer],t2.[ShipDate][/i],
from table t1
WHERE EXISTS (
SELECT *
FROM [EncoreCompanyA].[dbo].[LotTransactions] LT
WHERE TrnType = 'S'
AND LT.[LotJob] = t1.LotNumber
)
AND LotNumber = '0000337495'
It was hiding 🙂
Cheers!
Sorry, did not scroll far enough to the right. 🙂
Then the question remains: what's the purpose of the query?
Why do you need to return in the same line:
t1.[LotNumber] and MAX([LotJob]),
t2.[ShipDate] and max([TrnDate]) ShipDate
?
You overlooked the absence of commas in the original query. LotNumber and ShipDate are aliases for the two MAX(..) expressions.
February 18, 2016 at 2:39 pm
spin (2/17/2016)
Thanks for the replies..perhaps i should have given my final expectations...
i have 2 tables which i'm trying to join. I know the lot exists under the conditions in the subquery but i get null values in the results
select t1.[StockCode], t1.[SalesOrder], t1.[SalesOrderLine], t1.[LotNumber], t1.[Customer], t2.[ShipDate]from table t1
left join
(
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
) t2
on t1.LotNumber = t2.LotNumber
where LotNumber = '0000337495'
Would you be able to supply some test data and table create statements? What columns are you getting nulls for?
----------------------------------------------------
February 18, 2016 at 2:45 pm
Does this return anything that looks like what you may want ?
select t1.[StockCode], t1.[SalesOrder], t1.[SalesOrderLine], t1.[LotNumber], t1.[Customer],
t2.[ShipDate]
--from table t1
from (select [StockCode], [SalesOrder], [SalesOrderLine], [LotNumber], [Customer]
from
where LotNumber = '0000337495'
) as t1
left join
(
SELECT [StockCode], [SalesOrder], [SalesOrderLine], max([LotJob]) [LotNumber], max([TrnDate]) ShipDate
FROM [EncoreCompanyA].[dbo].[LotTransactions]
WHERE TrnType = 'S'
GROUP BY [StockCode], [SalesOrder], [SalesOrderLine]
) as t2
ON t1.LotNumber = t2.LotNumber
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply