Linking from Quotes to Sales Orders?

[Former Member]

Hi Everyone,

I have been tasked with creating a report which allows for the searching of Quotes. One of the reports output requirements is that I show any Sales Orders (where they exist) which relate to a given Quote number.

Here is the code that I have written so far -

DECLARE @custCode nvarchar(10), @itemFrom nvarchar(30), @itemTo nvarchar(30), @dateFrom DATE, @dateTo DATE

SET @custCode = ''
    IF @custCode = ''
        SET @custCode = '%'

SET @itemFrom = ''
    IF @itemFrom = ''
        SET @itemFrom = '00%'

SET @itemTo = '' + 'Z'
    IF @itemTo = 'Z'
        SET @itemTo = 'ZZZZZ%'

SET @dateFrom = ''
    IF @dateFrom = ''
        SET @dateFrom = '1999-01-01'

SET @dateTo = '2015-01-27'
    IF @dateTo = '1753-01-01'
        SET @dateTo = GETDATE()

SELECT
T0.DocNum AS 'Quote #'

,(SELECT Ta.DocNum FROM ORDR Ta INNER JOIN RDR1 Tb on Tb.DocEntry = Ta.DocEntry AND Tb.LineNum = T1.BaseLine WHERE Tb.BaseType = '23' AND Tb.BaseEntry = T1.DocEntry) AS 'Sales Order #'

, CASE WHEN T0.DocStatus = 'C' THEN 'Closed' ELSE 'Open' END AS Status
, T0.DocDate AS 'Doc Date'
, T0.CardCode AS 'Cust Code'
, T0.CardName AS 'Cust Name'
, T1.ItemCode AS 'Item Code'
, T1.Dscription AS 'Item Description'
, T1.Quantity AS 'Quantity'
, T1.Price
, T1.LineTotal
, T2.SlpName AS 'Sales Person'

FROM OQUT T0
LEFT JOIN QUT1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OSLP T2 ON T2.SlpCode = T0.SlpCode

WHERE T0.CardCode LIKE (@custCode)
    AND (T1.ItemCode >= @itemFrom AND T1.ItemCode <= @itemTo)
    AND (T0.DocDate >= @dateFrom AND T0.DocDate <= @dateTo)
    AND T0.CANCELED = 'N'

ORDER BY [Quote #]

My challenge specifically relates to the INNER SELECT, as it is causing the following error in SSMS.

Msg 512, Level 16, State 1, Line 36

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

Any help with resolving this error will be greatly appreciated.

Kind Regards,

David

[kothandaraman_nagarajan · Accepted solution]

Hi,

This sub query returns more than one value. Run this query alone and check how many document in query result.

(SELECT Ta.DocNum FROM ORDR Ta INNER JOIN RDR1 Tb on Tb.DocEntry = Ta.DocEntry AND Tb.LineNum = T1.BaseLine WHERE Tb.BaseType = '23' AND Tb.BaseEntry = T1.DocEntry) AS 'Sales Order #'

To avoid this error, directly link SQ and SO without subquery.

Thanks & Regards,

Nagarajan

[Former Member]

Hi David,

Try:

(SELECT Top 1 Ta.DocNum FROM ORDR Ta INNER JOIN RDR1 Tb on Tb.DocEntry = Ta.DocEntry AND Tb.LineNum = T1.BaseLine WHERE Tb.BaseType = '23' AND Tb.BaseEntry = T1.DocEntry) AS 'Sales Order #'

Thanks,

Gordon

[Former Member]

Hi Gordon,

I tried your suggestion both inside an INNER SELECT, and as part of a main query (as suggested by Nagarajan), however in both instances by adding the extra clause 'Tb.LineNum = T1.BaseLine' I receive only NULLs in the 'Sales Order#' column.

To add to the confusion by not having a reference to the LineNum / BaseLine as you suggest I see a quadrupling in my results set, with Items being listed four times when they should be listed once, eight times when they should be listed twice, and so on and so forth.

My current challenge is limiting my result set to the correct number of rows (as eluded to earlier everything is repeated four times) which also retaining my 'Sales Order#' column.

Do you have any further suggestions?

Kind Regards,

David

[Former Member]

You may add Distinct after Select and check the result.

[Former Member]

Hi Gordon,

I think that I misread your original reply; ultimately I have been able to return the desired data using a join very similar to the one that you suggested.

LEFT JOIN RDR1 T3 ON T3.BaseEntry = T1.DocEntry AND T3.BaseLine = T1.LineNum

My complete query is shown below, and it varies from my INNER SELECT because instead of joining from the ORDR (Header) to the RDR1 (Row) table, I am now joining from the RDR1 (Row) to the ORDR (Header).

/*

Name:            Quotations Search 
Author:         David Dartnell
Created:        27/01/2015
Last Updated:    27/01/2015
Purpose: 
Usage: 
Support Issue ID/Software Fix: N/A
Supported Versions: SQL 2008R2

*/

DECLARE @custCode nvarchar(10), @itemFrom nvarchar(30), @itemTo nvarchar(30), @dateFrom DATE, @dateTo DATE

SET @custCode = ''
    IF @custCode = ''
        SET @custCode = '%'

SET @itemFrom = ''
    IF @itemFrom = ''
        SET @itemFrom = '00%'

SET @itemTo = '' + 'Z'
    IF @itemTo = 'Z'
        SET @itemTo = 'ZZZZZ%'

SET @dateFrom = ''
    IF @dateFrom = ''
        SET @dateFrom = '1999-01-01'

SET @dateTo = '2015-01-27'
    IF @dateTo = '1753-01-01'
        SET @dateTo = GETDATE()

SELECT
T0.DocNum AS 'Quote #'
, ISNULL(CAST(T4.DocNum AS nvarchar), '') AS 'Sales Order #'
, CASE WHEN T0.DocStatus = 'C' THEN 'Closed' ELSE 'Open' END AS Status
, T0.DocDate AS 'Doc Date'
, T0.CardCode AS 'Cust Code'
, T0.CardName AS 'Cust Name'
, T1.ItemCode AS 'Item Code'
, T1.Dscription AS 'Item Description'
, T1.Quantity AS 'Quantity'
, T1.Price
, T1.LineTotal
, T2.SlpName AS 'Sales Person'

FROM OQUT T0
LEFT JOIN QUT1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OSLP T2 ON T2.SlpCode = T0.SlpCode
LEFT JOIN RDR1 T3 ON T3.BaseEntry = T1.DocEntry AND T3.BaseLine = T1.LineNum
LEFT JOIN ORDR T4 ON T4.DocEntry = T3.DocEntry

WHERE T0.CardCode LIKE (@custCode)
    AND (T1.ItemCode >= @itemFrom AND T1.ItemCode <= @itemTo)
    AND (T0.DocDate >= @dateFrom AND T0.DocDate <= @dateTo)
    AND T0.CANCELED = 'N'

ORDER BY [Quote #], [Item Code]

Thanks you Gordon & Nagarajan for your generous contributions.

Kind Regards,

David