May 30, 2013 at 5:05 pm
Hello,
I am trying to change the below query to add two new columns. Territory and Customer Sold To.
I have tried several different ways of pulling the information and get nothing but errors.
The information that I need is contained in the tblarInvoiceline table.
Any help would greatly appreciated.
Thanks,
Daveg
DECLARE @SPC varchar(6)
SET @SPC = '{[Report]!B7}'
Select
sp.Item, sp.itemdescription,
sp.SellingPrice,
sp.EffDate,
vp.PurchasePrice,
sp.pricedesc,
(select max(ar.InvoiceDate)
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item in (ari.Item)) as 'Last Date Sold',
(select distinct(il.VendorPurchaseFrom)
from tblimItemLoc il inner join tblapVendorPurchaseFrom apf on il.VendorPurchaseFrom = apf.VendorPurchaseFrom
Where sp.Item = il.Item and vp.Vendor = apf.Vendor) as 'Vendor Purchase From'
From vwimCurrentItemPriceAOP2 sp left join vwimCurrentItemVendorPrice vp on sp.Item = vp.Item
Where sp.SPC = @SPC
May 30, 2013 at 5:31 pm
I'm sorry but I forgot my mindreader cap.
Could you post the errors you're getting and the DDL involved? Some sample data and expected results will help us to see what you see.
Read the article linked in my signature to get the best help possible.
May 30, 2013 at 10:48 pm
Wow, I am so sorry! I thought I had posted in the Newbie section were not following whatever rules you think are important might be over looked.
I believe I made it fairly clear that I needed help with the syntax. So what the errors are don't really matter. and the results I expected are pretty clear also.
Possibly you should have put your thinking cap on instead of your mind reading cap before being rude.
Never mind responding I will post in a forum that is more concerned with helping than giving someone a bad time.
May 31, 2013 at 3:31 am
I don't think there is any issue with the syntax. The error is probably due to something else.
DECLARE @SPC varchar(6)
SET @SPC = '{[Report]!B7}'
SELECTsp.Item, sp.itemdescription, sp.SellingPrice, sp.EffDate, vp.PurchasePrice, sp.pricedesc,
(
SELECTMAX(ar.InvoiceDate)
FROMtblarInvoice ar
INNER JOIN tblarInvoiceLine ari ON ar.InvoiceNumber = ari.InvoiceNumber
--WHEREsp.Item in (ari.Item) /* This is the only change I will suggest as it seems strange*/
WHEREsp.Item = ari.Item
) as 'Last Date Sold',
(
SELECTDISTINCT (il.VendorPurchaseFrom)
FROMtblimItemLoc il
INNER JOIN tblapVendorPurchaseFrom apf ON il.VendorPurchaseFrom = apf.VendorPurchaseFrom
WHEREsp.Item = il.Item AND vp.Vendor = apf.Vendor
) as 'Vendor Purchase From'
FROMvwimCurrentItemPriceAOP2 sp
LEFT JOIN vwimCurrentItemVendorPrice vp ON sp.Item = vp.Item
WHEREsp.SPC = @SPC
dwg23 (5/30/2013)
So what the errors are don't really matter.
I spent more than 10 minutes with your query and couldn't find any syntax error
So, I think they do matter in such cases
Even if they didn't matter, wouldn't it be nice to help the people helping you with the error message?
As no error message has been provided, all I can do is make a guess
Is your error message the one below?
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2013 at 8:12 am
dwg23 (5/30/2013)
Wow, I am so sorry! I thought I had posted in the Newbie section were not following whatever rules you think are important might be over looked.I believe I made it fairly clear that I needed help with the syntax. So what the errors are don't really matter. and the results I expected are pretty clear also.
Possibly you should have put your thinking cap on instead of your mind reading cap before being rude.
Never mind responding I will post in a forum that is more concerned with helping than giving someone a bad time.
I would have to agree that his response was rude. Especially to somebody's first post. I can assure you that most people around here (including Luis) are generally very helpful and friendly. Don't let this drive you away. SSC is a goldmine of knowledge.
I agree with Kingston that the error messages really do matter. It helps us understand what the problem is you are experiencing. It is difficult if not impossible to help diagnose an issue without some knowledge of the system and what is happening.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2013 at 8:29 am
I'm sorry, my intention wasn't to be rude, I've had a tough week at work (some might have an idea why is that). I'm really interested in helping you, otherwise I wouldn't have responded to your post.
However, we need some help to help you and I wanted to guide you to get better help.
What I showed you is not a rule to get help but it's simply a guide to get it faster and better. 😉
You mention you need to add Territory and Customer Sold To to your query, but I couldn't find them in your code.
Remember that we can't see what you see and we have no idea of what your database environment looks like, so we need help to help you.
May 31, 2013 at 9:32 am
First off I do apologize for the way that I responded.
And I need to be more clear in what I am asking for.
The query as posted works. ( I am not sure why the "in" is used either. I did not write the query originally.)
I did not post the errors as what I really need help with is adding the Territory and Customer Sold To.
They are not in the code because at this point I have no idea what to write.
My first stab at it was.
(select ari.territory
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item in (ari.Item) as 'Territory',
This gave me the error that Kingston guessed at.
Other tries gave me other errors. so again I am at a total loss as to where to even start with this.
Thanks,
dwg23
May 31, 2013 at 9:40 am
dwg23 (5/31/2013)
First off I do apologize for the way that I responded.And I need to be more clear in what I am asking for.
The query as posted works. ( I am not sure why the "in" is used either. I did not write the query originally.)
I did not post the errors as what I really need help with is adding the Territory and Customer Sold To.
They are not in the code because at this point I have no idea what to write.
My first stab at it was.
(select ari.territory
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item in (ari.Item) as 'Territory',
This gave me the error that Kingston guessed at.
Other tries gave me other errors. so again I am at a total loss as to where to even start with this.
Thanks,
dwg23
I am willing and able to help but without some details I can't even take a shot at helping. Without the table structures and some sample data to work with it is pretty tough to help much.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2013 at 10:44 am
i would like to thank everyone for being willing to help and will be back in the future if I get stuck. I have figured out what I needed to do. It is probably very sloppy code but it gives the information I needed.
Here is the code I ended up with and if anyone can explain why adding 'max' to the sub query's made them work I would love to be educated as I need to be able to do more of these in the future.
Thanks,
dwg23
DECLARE @SPC varchar(6)
SET @SPC = '{[Report]!B7}'
Select
sp.Item, sp.itemdescription,
sp.SellingPrice,
sp.EffDate,
vp.PurchasePrice,
sp.pricedesc,
(select max(ari.territory)
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item = ari.Item) as 'Territory',
(select max(ar.InvoiceToName)
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item = ari.Item) as 'Sold To',
(select max(ar.InvoiceDate)
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item = ari.Item) as 'Last Date Sold',
(select distinct(il.VendorPurchaseFrom)
from tblimItemLoc il inner join tblapVendorPurchaseFrom apf on il.VendorPurchaseFrom = apf.VendorPurchaseFrom
Where sp.Item = il.Item and vp.Vendor = apf.Vendor) as 'Vendor Purchase From'
From vwimCurrentItemPriceAOP2 sp left join vwimCurrentItemVendorPrice vp on sp.Item = vp.Item
Where sp.SPC = @SPC
May 31, 2013 at 10:55 am
dwg23 (5/31/2013)
i would like to thank everyone for being willing to help and will be back in the future if I get stuck. I have figured out what I needed to do. It is probably very sloppy code but it gives the information I needed.Here is the code I ended up with and if anyone can explain why adding 'max' to the sub query's made them work I would love to be educated as I need to be able to do more of these in the future.
Thanks,
dwg23
Because once you add MAX you have now aggregated the data. That means you can't get multiple rows in the subquery.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2013 at 11:00 am
dwg23 (5/31/2013)
Here is the code I ended up with and if anyone can explain why adding 'max' to the sub query's made them work I would love to be educated as I need to be able to do more of these in the future.
When you use a subquery that way to return a value for a column, it should only return one row. If multiple rows are returned SQL Server won't know which one to use and therefore it shows the error.
Adding an aggregate function like MAX or MIN will ensure to have a single row.
I'm including a query that might help you to avoid multiple subqueries, but you should test it before using it.
DECLARE @SPC varchar(6)
SET @SPC = '{[Report]!B7}'
Select
sp.Item, sp.itemdescription,
sp.SellingPrice,
sp.EffDate,
vp.PurchasePrice,
sp.pricedesc,
InvLin.Territory as 'Territory',
InvLin.SoldTo as 'Sold To',
InvLin.LastDateSold as 'Last Date Sold',
(select distinct(il.VendorPurchaseFrom)
from tblimItemLoc il inner join tblapVendorPurchaseFrom apf on il.VendorPurchaseFrom = apf.VendorPurchaseFrom
Where sp.Item = il.Item and vp.Vendor = apf.Vendor) as 'Vendor Purchase From'
From vwimCurrentItemPriceAOP2 sp
left join vwimCurrentItemVendorPrice vp on sp.Item = vp.Item
join (select ari.Item, max(ari.territory) Territory, max(ar.InvoiceToName) SoldTo, max(ar.InvoiceDate) LastDateSold
from tblarInvoice ar
inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
GROUP BY ari.Item) InvLin ON sp.Item = InvLin.Item
Where sp.SPC = @SPC
May 31, 2013 at 11:07 am
Luis,
Thank you very much for the query and that is exactly the type of example I was looking for.
I also assumed that "MAX" was only for numeric data and the data I am pulling is alpha.
Is there some recommended reading on how to construct a query like you sent?
Thanks again,
Dwg23
May 31, 2013 at 11:25 am
I will give it a shot.
Thanks again.
dwg23
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply