Subquery returned more than 1 value
-
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT IH.InvoiceDate AS HistoryDate, 'Part of Invoice' AS HistoryType, SI.SiteName AS HistoryBranch, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference, ( SELECT TIP.InvoicePhoneIMEI FROM tblInvoicePhones TIP INNER JOIN tblInvoiceItems TII ON TIP.InvoiceItemID = TII.InvoiceItemID WHERE TII.InvoiceID = IH.InvoiceID ) As HistoryIMEIs, (ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser FROM tblInvoiceHeader IH INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID LEFT JOIN tblCustomers C ON IH.CustomerID = C.CustomerID WHERE II.StockID = '1750' AND IH.InvoiceDate >= 'January 1, 2007' AND IH.InvoiceDate <= 'July 2, 2007' AND IH.SiteID = '10' AND IH.InvoiceProForma = 0 -
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT IH.InvoiceDate AS HistoryDate, 'Part of Invoice' AS HistoryType, SI.SiteName AS HistoryBranch, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference, ( SELECT TIP.InvoicePhoneIMEI FROM tblInvoicePhones TIP INNER JOIN tblInvoiceItems TII ON TIP.InvoiceItemID = TII.InvoiceItemID WHERE TII.InvoiceID = IH.InvoiceID ) As HistoryIMEIs, (ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser FROM tblInvoiceHeader IH INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID LEFT JOIN tblCustomers C ON IH.CustomerID = C.CustomerID WHERE II.StockID = '1750' AND IH.InvoiceDate >= 'January 1, 2007' AND IH.InvoiceDate <= 'July 2, 2007' AND IH.SiteID = '10' AND IH.InvoiceProForma = 0The message relates to the subquery. In this example the subquery can only return one row. I would run the subquery only and see how many rows it is returning. If it returns more than one then you have a problem Jon
-
The message relates to the subquery. In this example the subquery can only return one row. I would run the subquery only and see how many rows it is returning. If it returns more than one then you have a problem Jon
As i mentioned in my original post the subquery should and does return more than one rows. I want for example the following to be returned. 12/10/2007 Sale 967676689768879, 23987298739872, 39278387298372, 3927328739872973 24/12/2007 Sale 8768676876876876, 87687678876876786, 9876987865765678 Where the large numbers are collected from the subquery. Is this not possible? How else can I go about getting information in this way?
-
As i mentioned in my original post the subquery should and does return more than one rows. I want for example the following to be returned. 12/10/2007 Sale 967676689768879, 23987298739872, 39278387298372, 3927328739872973 24/12/2007 Sale 8768676876876876, 87687678876876786, 9876987865765678 Where the large numbers are collected from the subquery. Is this not possible? How else can I go about getting information in this way?
No, You cant do it that way. The way your SQL is structured the subquery is in effect a single field. you cant just put several values into one field. I see what you are trying to do but i dont think there is a way to do it with a single SQL statement (although i may be wrong about that).
-
No, You cant do it that way. The way your SQL is structured the subquery is in effect a single field. you cant just put several values into one field. I see what you are trying to do but i dont think there is a way to do it with a single SQL statement (although i may be wrong about that).
Ok so you can't merge the fields in anyway. Does anyone else know?
-
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT IH.InvoiceDate AS HistoryDate, 'Part of Invoice' AS HistoryType, SI.SiteName AS HistoryBranch, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference, ( SELECT TIP.InvoicePhoneIMEI FROM tblInvoicePhones TIP INNER JOIN tblInvoiceItems TII ON TIP.InvoiceItemID = TII.InvoiceItemID WHERE TII.InvoiceID = IH.InvoiceID ) As HistoryIMEIs, (ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser FROM tblInvoiceHeader IH INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID LEFT JOIN tblCustomers C ON IH.CustomerID = C.CustomerID WHERE II.StockID = '1750' AND IH.InvoiceDate >= 'January 1, 2007' AND IH.InvoiceDate <= 'July 2, 2007' AND IH.SiteID = '10' AND IH.InvoiceProForma = 0Hi You can write your sub-query in a separate function. (table-valued function). Using this function, you can join with the main query as given below. Select Col1, Col2, dbo.Fn_SubQuery(param1,param2) from Table1 Hope this helps. See the example below
use northwind go create function dbo.Fn_OrdersProductID(@OrderID int) returns nvarchar(4000) as begin DECLARE @ProductID nvarchar(4000) set @ProductID = null SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar) FROM [Order Details] WHERE OrderID = @OrderID RETURN @ProductID end -- run the foll. stmt after executing the above function -- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from OrdersApply the same for your query -- modified at 7:08 Tuesday 3rd July, 2007 -- modified at 7:11 Tuesday 3rd July, 2007Harini
-
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT IH.InvoiceDate AS HistoryDate, 'Part of Invoice' AS HistoryType, SI.SiteName AS HistoryBranch, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference, ( SELECT TIP.InvoicePhoneIMEI FROM tblInvoicePhones TIP INNER JOIN tblInvoiceItems TII ON TIP.InvoiceItemID = TII.InvoiceItemID WHERE TII.InvoiceID = IH.InvoiceID ) As HistoryIMEIs, (ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser FROM tblInvoiceHeader IH INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID LEFT JOIN tblCustomers C ON IH.CustomerID = C.CustomerID WHERE II.StockID = '1750' AND IH.InvoiceDate >= 'January 1, 2007' AND IH.InvoiceDate <= 'July 2, 2007' AND IH.SiteID = '10' AND IH.InvoiceProForma = 0Harini is right I believe. Something like:
SELECT DISTINCT IH.InvoiceDate AS HistoryDate , 'Part of Invoice' AS HistoryType , SI.SiteName AS HistoryBranch , ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference , getInvoicePhoneIMEIs(IH.InvoiceID) AS HistoryIMEIs FROM tblInvoiceHeader IH INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID LEFT JOIN tblCustomers C ON IH.CustomerID= C.CustomerID WHERE II.StockID = '1750' AND IH.InvoiceDate >= 'January 1, 2007' AND IH.InvoiceDate <= 'July 2, 2007' AND IH.SiteID = '10' AND IH.InvoiceProForma = 0where getInvoicePhoneIMEIs(IH.InvoiceID) uses a cursor based on your subselect to build a string. http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string[^] Be sure to include the DISTINCT keyword or you'll get a result row for every InvoicePhoneIMEI value. David
-
Hi You can write your sub-query in a separate function. (table-valued function). Using this function, you can join with the main query as given below. Select Col1, Col2, dbo.Fn_SubQuery(param1,param2) from Table1 Hope this helps. See the example below
use northwind go create function dbo.Fn_OrdersProductID(@OrderID int) returns nvarchar(4000) as begin DECLARE @ProductID nvarchar(4000) set @ProductID = null SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar) FROM [Order Details] WHERE OrderID = @OrderID RETURN @ProductID end -- run the foll. stmt after executing the above function -- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from OrdersApply the same for your query -- modified at 7:08 Tuesday 3rd July, 2007 -- modified at 7:11 Tuesday 3rd July, 2007Harini
what is the need of COALESCE