February 20, 2014 at 8:19 am
select MAX(a.InvoiceId),c.SoldTo,(select 'CustomerOrderNumber1' = CASE WHEN c.SoldTo = 'G09905' THEN 'UN013'
WHEN c.SoldTo = 'G09929' and id.CustomerOrderNumber != '' THEN 'AL037'
WHEN c.SoldTo = 'G09929' and id.CustomerOrderNumber = '' THEN 'AL039'
WHEN c.SoldTo = 'G09930' THEN 'DT001' END)As CustOrd
from dbo.InvoiceHeader a with (nolock)
join dbo.InvoiceAddress b with (nolock)
on a.InvoiceID=b.InvoiceID
join AddressRelationship c
ON a.Shipto = c.AccountNumber
JOIN InvoiceDetail id
ON id.InvoiceID = a.InvoiceID and id.DetailID =(select top 1 DetailID from InvoiceDetail id2 where id2.InvoiceID=a.InvoiceID)
where b.AddressType='SU' and a.Status = '1' and a.TradingPartnerID='G09905' and b.VATNumber='12345678'
GROUP BY c.Soldto,TradingPartnerID,CustOrd
This produces the error invalid column name CustOrd. Does GROUP BY statement not like alias's
Essentially, I would like to group by the results of the case. Now I can put the CASE statement in the group by which works fine but I was looking for something that looks a bit neater if possible
Any suggestions or observations? Why does the above not work?
February 20, 2014 at 8:34 am
mark.dungey 56406 (2/20/2014)
select MAX(a.InvoiceId),c.SoldTo,(select 'CustomerOrderNumber1' = CASE WHEN c.SoldTo = 'G09905' THEN 'UN013'WHEN c.SoldTo = 'G09929' and id.CustomerOrderNumber != '' THEN 'AL037'
WHEN c.SoldTo = 'G09929' and id.CustomerOrderNumber = '' THEN 'AL039'
WHEN c.SoldTo = 'G09930' THEN 'DT001' END)As CustOrd
from dbo.InvoiceHeader a with (nolock)
join dbo.InvoiceAddress b with (nolock)
on a.InvoiceID=b.InvoiceID
join AddressRelationship c
ON a.Shipto = c.AccountNumber
JOIN InvoiceDetail id
ON id.InvoiceID = a.InvoiceID and id.DetailID =(select top 1 DetailID from InvoiceDetail id2 where id2.InvoiceID=a.InvoiceID)
where b.AddressType='SU' and a.Status = '1' and a.TradingPartnerID='G09905' and b.VATNumber='12345678'
GROUP BY c.Soldto,TradingPartnerID,CustOrd
This produces the error invalid column name CustOrd. Does GROUP BY statement not like alias's
Essentially, I would like to group by the results of the case. Now I can put the CASE statement in the group by which works fine but I was looking for something that looks a bit neater if possible
Any suggestions or observations? Why does the above not work?
You can not use column alias's in your group by. If you want it to look neater you could encapsulate in an CTE and then you could reference by the column alias, but I wouldn't recommend doing it just because it is neater.
Also are you aware of what NOLOCK does? You understand that you could get duplicate rows or even missing rows along with dirty reads by using it? I would recommend that if this is a production query that you look into removing the NOLOCK hints. If you are using them because of concurrency issues I would recommend that you look into other solutions (Snapshot Isolation perhaps).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply