September 1, 2014 at 9:31 am
Hi all , following is my SP , when I just comment the where clause i.e.
o.StatusID in (Select osl.val from dbo.SplitStringToIntegerArray(@OrderStatusList, ',') osl )
my sp gives me the result set but when I uncomment it I am not getting any value..
below is my function split and SP can any one help me to identify the problem??
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION
[dbo].[SplitStringToIntegerArray](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
/****** Object: StoredProcedure [dbo].[LoadSalesOrder] Script Date: 09/01/2014 16:24:18 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/****** Object: StoredProcedure [dbo].[LoadSalesOrder] Script Date: 08/29/2014 19:36:31 ******/
Alter PROCEDURE LoadSalesOrder
@AllowedSalespersons varchar,
@RecordsWithNoSalesperson int,
@p_SalesOrderOpen int,
@p_SalesOrderClosed int,
@p_OrderTypeIDInvoice int,
@p_InvoiceTypeIDReceivable int,
@OrderStatuses varchar
AS
BEGIN
DECLARE @AllowedSalespersonsList nvarchar(MAX) = ',' + @AllowedSalespersons + ','
DECLARE @OrderStatusList nvarchar(MAX) = ',' + @OrderStatuses + ','
;WITH
SalespersonFilter
AS
(
select o1.ID
from Orders o1 with (nolock)
left join OrderCommission oc with (nolock) on o1.ID = oc.OrderID
where oc.SalespersonID in (Select s.val from dbo.SplitStringToIntegerArray(@AllowedSalespersonsList, ',') s) and
(o1.ordertypeid != 3 OR o1.ordertypeid is null)
union
select o2.ID
from Orders o2 with (nolock)
left join OrderCommission oc2 with (nolock) on o2.ID = oc2.OrderID
where oc2.ID is NULL and (o2.ordertypeid != 3 OR o2.ordertypeid is null) and 1 = @RecordsWithNoSalesperson
)
,
cteNonMatrix ( InvoicedFromID, odrId, lineItemId, priceSum, isMatrix, BaseSubTotalMerchandisePrice, BaseFreightPrice, BaseDiscountPrice, BaseTaxAmount )
AS ( SELECT o16.InvoicedFromID InvoicedFromID, o16.ID odrId , li7.id lineitemid ,
( CASE WHEN li7.PerPrice > 0 THEN ( ( li7.Quantity * li7.Price ) / li7.PerPrice )
ELSE ( li7.quantity * li7.Price )
END ) priceSum ,
CASE WHEN EXISTS ( SELECT id FROM dbo.LineItemAttributes lia with (nolock) WHERE lia.LineItemID = li7.ID )
THEN 1 ELSE 0 END isMatrix,
o16.BaseSubTotalMerchandisePrice,
o16.BaseFreightPrice,
o16.BaseDiscountPrice,
o16.BaseTaxAmount
FROM dbo.Orders o16
INNER JOIN SalespersonFilter s with (nolock) on s.ID = o16.ID
INNER JOIN dbo.LineItems li7 with (nolock) ON o16.id = li7.OrderID AND li7.AdditionalChargeParentID IS NULL
AND li7.IsFreight <> 1 ),
cteMatrix ( lineItemId, priceSum ) AS ( SELECT li7.id lineitemid ,
CASE WHEN li7.perprice > 0 THEN SUM(lvs.price * lvs.Quantity) / li7.PerPrice ELSE SUM(lvs.price * lvs.Quantity)
END priceSum FROM dbo.Orders o16 with (nolock) INNER JOIN dbo.LineItems li7 with (nolock) ON o16.id = li7.OrderID
AND li7.AdditionalChargeParentID IS NULL AND li7.IsFreight <> 1
AND EXISTS ( SELECT id FROM dbo.LineItemAttributes lia WHERE lia.LineItemID = li7.ID )
INNER JOIN dbo.LineItemValues lvs with (nolock) ON li7.ID = lvs.LineItemID GROUP BY li7.id , li7.PerPrice ),
cteAdditionalNm ( odrAddId, lineItemId, acId, priceSum, isMatrix )
AS ( SELECT odr16.ID odrAddId ,
li9.Id lineItemId ,
liAdd.id acId ,
( CASE WHEN liAdd.PerPrice > 0
THEN ( ( liAdd.Quantity * liAdd.Price )
/ liAdd.PerPrice )
ELSE ( liAdd.quantity * liAdd.Price )
END ) priceSum ,
CASE WHEN EXISTS ( SELECT id
FROM dbo.LineItemAttributes lia with (nolock)
WHERE lia.LineItemID = liAdd.ID )
THEN 1
ELSE 0
END isMatrix
FROM orders odr16 with (nolock)
INNER JOIN SalespersonFilter s with (nolock) on s.ID = odr16.ID
INNER JOIN lineitems li9 with (nolock) ON odr16.ID = li9.OrderID
AND li9.AdditionalChargeParentID IS NULL
AND li9.IsFreight <> 1
INNER JOIN lineitems liAdd with (nolock) ON li9.ID = liAdd.AdditionalChargeParentID
AND liAdd.IsFreight <> 1
),
cteAdditionalM ( acId, priceSum )
AS ( SELECT liAdd.ID acId ,
CASE WHEN liadd.perprice > 0
THEN SUM(lvs.price * lvs.Quantity)
/ liAdd.PerPrice
ELSE SUM(lvs.price * lvs.Quantity)
END priceSum
FROM orders odr16 with (nolock)
INNER JOIN SalespersonFilter s with (nolock) on s.ID = odr16.ID
INNER JOIN lineitems li9 with (nolock) ON odr16.ID = li9.OrderID
AND li9.AdditionalChargeParentID IS NULL
AND li9.IsFreight <> 1
INNER JOIN lineitems liAdd with (nolock) ON li9.ID = liAdd.AdditionalChargeParentID
AND liAdd.IsFreight <> 1
AND EXISTS ( SELECT
id
FROM
dbo.LineItemAttributes lia with (nolock)
WHERE
lia.LineItemID = liAdd.ID )
INNER JOIN dbo.LineItemValues lvs with (nolock) ON liAdd.ID = lvs.LineItemID
GROUP BY liAdd.id ,
liadd.PerPrice
)
SELECT
o.BaseBilledCost,
(select count(*) from OrderContactLink with (nolock) where orderid=o.id) ContactCount ,
o.BaseBilledCostLessFreight,
o.BaseBilledFreightCost ,
o.BaseCommissionAmount ,
o.BaseDepositAmount ,
o.BaseDiscountPrice ,
o.BaseFreightCost ,
o.BaseFreightPrice ,
o.BaseInvoiceAdjustmentAmount ,
o.BaseSubTotalMerchandiseCost ,
o.BaseSubTotalMerchandisePrice ,
o.BaseTaxAmount ,
o.CustomerPO CustomerPo,
c.CustomerNumber CustomerNumber ,
CASE WHEN o.StatusID = @p_SalesOrderOpen THEN 'Open' WHEN o.StatusID = @p_SalesOrderClosed THEN 'Closed' END AS OrderStatus,
o.ID OrderID,
CAST(CONVERT(VARCHAR(10),o.OrderDate,101) as DateTime) as OrderDate ,
o.OrderNumber ,
CAST(CONVERT(VARCHAR(10),o.ReorderDate,101) as DateTime) as ReorderDate ,
CAST(CONVERT(VARCHAR(10),o.ShipDate,101) as DateTime) as ShipDate ,
CAST(CONVERT(VARCHAR(10),o.InHandDate,101) as DateTime) as InhandDate,
c.CustomerName + ' - ' + c.CustomerNumber CustomerUniqueName,
c.ID CustomerID, c.CustomerName,
org.Description OrderSource,
rg.Description OrderRegion,
ind.Description OrderIndustry,
tr.Description OrderTerms,
orig.Description OrderOrigin,
(
select count(*) from (
SELECT Distinct si.TrackingNumber, max(si.shipdate) as sd
FROM dbo.ShippingInfoImports si with (nolock)
WHERE si.ShippingKey LIKE CONVERT(VARCHAR(40), o.OrderNumber) + '%'
group by si.TrackingNumber
)
AS sii
) PackageCount,
(SELECT TOP(1) oc.CompanyName FROM dbo.OrderContactLink ocl with (nolock) INNER JOIN dbo.OrderContacts oc with (nolock) ON ocl.OrderContactID = oc.ID
AND ocl.DefaultContact = 1 AND ocl.ContactTypeID = 11 AND ocl.OrderID = o.id ) OrderBillToName,
(SELECT TOP(1) oc.ContactName FROM dbo.OrderContactLink ocl with (nolock) INNER JOIN dbo.OrderContacts oc with (nolock) ON ocl.OrderContactID = oc.ID
AND ocl.DefaultContact = 1 AND ocl.ContactTypeID = 11 AND ocl.OrderID = o.id ) OrderBilltoContact,
(SELECT TOP(1) oc.ContactName FROM dbo.OrderContactLink ocl with (nolock) INNER JOIN dbo.OrderContacts oc with (nolock) ON ocl.OrderContactID = oc.ID
AND ocl.DefaultContact = 1 AND ocl.ContactTypeID = 10 AND ocl.OrderID = o.id ) AcknowledgeToContact,
(SELECT TOP(1) oc.ContactName FROM dbo.OrderContactLink ocl with (nolock) INNER JOIN dbo.OrderContacts oc with (nolock) ON ocl.OrderContactID = oc.ID
AND ocl.DefaultContact = 1 AND ocl.ContactTypeID = 12 AND ocl.OrderID = o.id ) ShipToContact,
ISNULL(lineItemSummary.LineItemsCount, 0) LineItemsCount,
lineItemSummary.BaseLineItemTotalCost ,
lineItemSummary.BaseLineItemTotalPrice ,
IsNull(InvoiceSummary.BaseTotalCostForAllInvoices, 0) BaseTotalCostForAllInvoices,
(SELECT TOP(1) v.VendorName FROM OrderCommission oc with (nolock)
Left JOIN dbo.Salespersons sp with (nolock) ON oc.SalespersonID = sp.ID
Left JOIN dbo.Vendors v with (nolock) ON sp.VendorID = v.id
WHERE oc.OrderID = o.id AND oc.PrimarySalesperson = 1) PrimarySalesPerson,
(SELECT TOP (1)oc2.SalespersonID from dbo.OrderCommission oc2 with (nolock) WHERE oc2.OrderID = o.ID AND oc2.PrimarySalesperson = 1) PrimarySalesPersonId,
CASE when ISNULL(inv.BaseSubTotalMerchandisePrice, 0) > 0 then
ISNULL(inv.BaseSubTotalMerchandisePrice, 0) - ISNULL(inv.BaseDiscountPrice, 0) + ISNULL(inv.BaseTaxAmount, 0) + ISNULL(inv.BaseFreightPrice, 0)
when inv.FreightPrice > 0 then
ISNULL(inv.BaseFreightPrice, 0)
else 0 end TotalPriceForAllInvoices,
o.ExternalOrderNumber
FROM orders o with (nolock)
INNER JOIN SalespersonFilter s with (nolock) on s.ID = o.ID
Left outer join
(SELECT i.id id,
Sum(i2a.BaseSubTotalMerchandisePrice) BaseSubTotalMerchandisePrice,
sum(i2a.BaseDiscountPrice) BaseDiscountPrice,
sum(i2a.BaseTaxAmount) BaseTaxAmount,
sum(i2a.BaseFreightPrice) BaseFreightPrice,
Count(l1.orderid) freightprice
FROM orders i with (nolock)
INNER JOIN SalespersonFilter s with (nolock) on s.ID = i.ID
left join orders i2a with (nolock)
INNER JOIN SalespersonFilter s2 with (nolock) on s2.ID = i2a.ID
on i.id = i2a.invoicedfromid and i2a.ordertypeid = 2 and ISNULL(i2a.id, 0) > 0
AND EXISTS (SELECT * FROM dbo.Receivables ro2 with (nolock) WHERE ro2.OrderID = i2a.id AND ro2.InvoiceTypeID = 1)
Left outer Join lineitems l1 with (nolock)
ON l1.OrderID = i.ID and l1.IsFreight = 1 and l1.Discountable = 0
group by i.id
) inv
ON o.id = inv.id
LEFT OUTER JOIN dbo.Customers c with (nolock)
ON o.CustomerID = c.ID
LEFT OUTER JOIN dbo.Origins org with (nolock)
ON c.OriginID = org.ID
LEFT OUTER JOIN dbo.Regions rg with (nolock)
ON c.RegionID = rg.ID
LEFT OUTER JOIN dbo.Industries ind with (nolock)
ON c.IndustryID = ind.ID
LEFT OUTER JOIN dbo.Terms tr with (nolock)
ON o.TermsID = tr.ID
LEFT OUTER JOIN dbo.Origins orig with (nolock)
ON o.OriginID = orig.ID
LEFT OUTER JOIN
(SELECT o2.ID, SUM(o2a.BaseSubTotalMerchandiseCost + o2a.BaseFreightCost) BaseTotalCostForAllInvoices
FROM Orders o2 with (nolock)
Inner JOIN SalespersonFilter s with (nolock) on s.ID = o2.ID
left JOIN Orders o2a with (nolock)
INNER JOIN SalespersonFilter s2 with (nolock) on s2.ID = o2a.ID
ON o2.id = o2a.InvoicedFromID AND o2a.OrderTypeID = @p_OrderTypeIDInvoice
AND EXISTS (SELECT * FROM dbo.Receivables ro2 with (nolock) WHERE ro2.OrderID = o2a.id AND ro2.InvoiceTypeID = @p_InvoiceTypeIDReceivable)
GROUP BY o2.ID) InvoiceSummary
ON o.ID = InvoiceSummary.ID
LEFT OUTER JOIN
(
SELECT o1.id OrderId, SUM(l1.cost *l1.Quantity) BaseLineItemTotalCost, COUNT(l1.id) LineItemsCount,
SUM (l1.Price * l1.Quantity) BaseLineItemTotalPrice
FROM lineitems l1 with (nolock)
INNER JOIN orders o1 with (nolock)
ON l1.OrderID = o1.ID
INNER JOIN SalespersonFilter s on s.ID = o1.ID
GROUP BY o1.id
) lineItemSummary
ON o.ID = lineItemSummary.OrderId
where
o.StatusID in (Select osl.val from dbo.SplitStringToIntegerArray(@OrderStatusList, ',') osl ) and
o.OrderTypeID = 1
END
GO
September 2, 2014 at 12:21 pm
First I would see if the query returns anything when you replace
o.StatusID in (Select osl.val from dbo.SplitStringToIntegerArray(@OrderStatusList, ',') osl ) and
o.OrderTypeID = 1
with
where o.OrderTypeID = 1
If you you get rows then you know the problem is with your dbo.SplitStringToIntegerArray result set; StatusID does not exist in (Select osl.val from dbo.SplitStringToIntegerArray(@OrderStatusList, ',') osl ).
Build the @OrderStatusList variable and run your splitString query against that variable only. Make sure that something it is returning something. Then run your stored proc and compare the StatusID values returned against the results of the splitstring query. That's what I would do.
BTW, if you are looking for a splitter, use the one by Jeff Moden mentioned in my signature line. You will find that it makes short work of the XML splitter you are using. You also may want to consider losing those nolock table hints if your data absolutely has to be accurate.
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply