mad mad - 3 months ago 12
SQL Question

Subquery returned more than 1 value. when the subquery is used as an expression.in SQL Server

Can anyone tell me how to solve this problem?

Here's my code

Subquery :

SELECT
A.StoreNo, C.[date], A.ProductBarCode, A.ProductQty
INTO
##inv1
FROM
#calender C
OUTER APPLY
(SELECT TOP 100 PERCENT *
FROM ##Temp I
WHERE I.Date < C.DATE
AND StoreNo IN (SELECT StoreNo FROM ##StoreList)
ORDER BY I.Date) A
OPTION (maxrecursion 0)


I used top 100 percent because I have more than 1000 productBarcode for each storeNo, if I choose top 1 its showing only one productBarcode duplicate value for next day.

Declare
@pheader nvarchar(Max), @sql_pivot nvarchar(max)

Begin
SELECT
@pheader = STUFF((SELECT distinct ',' + QUOTENAME([StoreNo])
FROM ##StoreList
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
--SET @pheader = LEFT(@pheader, LEN(@pheader) - 1)
SET @sql_pivot = 'SELECT *
FROM
(SELECT
S.[StoreNo], p.Date as Date,
p.ProductBarCode as ProductBarCode,
ISNULL(i.productqty - (SELECT ProductQty as runningsum
FROM #Stock st
WHERE st.date <= i.date
AND st.storeno = i.storeno
AND st.ProductBarCode = i.ProductBarCode), i.productqty) as ProductQty

FROM #inventory i
LEFT JOIN ##StoreList S ON S.StoreNo = i.StoreNo
LEFT JOIN #Stock st ON st.StoreNo = i.StoreNo AND st.ProductBarCode = i.ProductBarCode AND st.Date = i.Date
RIGHT JOIN ##product p ON i.Date = P.Date and p.ProductBarCode = i.ProductBarCode
-- where p.ProductBarCode IN(2300007115072,2300012213046,2300012712075)
GROUP BY S.storeNo, i.StoreNo, i.ProductBarCode, i.Date, p.ProductBarCode, p.Date, i.ProductQty) p

PIVOT (SUM(ProductQty)
FOR [StoreNo] IN ('+ @pheader+') )Pvt'

EXECUTE sp_executesql @sql_pivot


I cant get three to four storeNo at a time if i select more then 10 its showing this error


Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.


I'm using SQL Server .

Answer

Add top 1 to your subquery. try like this,

DECLARE @pheader NVARCHAR(Max)
    ,@sql_pivot NVARCHAR(max)

BEGIN
    SELECT @pheader = STUFF((
                SELECT DISTINCT ',' + QUOTENAME([StoreNo])
                FROM ##StoreList
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

    SET @pheader = LEFT(@pheader, LEN(@pheader) - 1)
    SET @sql_pivot = 
        'SELECT *   
                          FROM 
                              (SELECT 
                                   S.[StoreNo], p.Date as Date, 
                                   p.ProductBarCode as ProductBarCode,
                                   ISNULL(i.productqty - (SELECT top 1 ProductQty as runningsum 
                                                          FROM #Stock st 
                                                          WHERE st.date <= i.date 
                                                            AND st.storeno = i.storeno 
                                                            AND st.ProductBarCode = i.ProductBarCode), i.productqty) as ProductQty

                                FROM #inventory i
                                LEFT JOIN ##StoreList S ON S.StoreNo = i.StoreNo
                                LEFT JOIN #Stock st ON st.StoreNo = i.StoreNo AND st.ProductBarCode = i.ProductBarCode AND st.Date = i.Date
                                RIGHT JOIN ##product p ON i.Date = P.Date and p.ProductBarCode = i.ProductBarCode
                                -- where p.ProductBarCode IN(2300007115072,2300012213046,2300012712075)
                                GROUP BY S.storeNo, i.StoreNo, i.ProductBarCode, i.Date, p.ProductBarCode, p.Date, i.ProductQty) p

                          PIVOT (SUM(ProductQty) 
                                 FOR [StoreNo] IN (' 
        + @pheader + ') )Pvt'

    EXECUTE sp_executesql @sql_pivot
END