troubleshooting Question

SQL view problem

Avatar of itinfo7
itinfo7 asked on
Microsoft SQL ServerMySQL ServerMicrosoft SQL Server 2008
2 Comments1 Solution263 ViewsLast Modified:
I have view as below
SELECT     O.OrderID, O.CreatedOn,
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'LastName')) AS 'LastName',
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'FirstName')) AS 'FirstName', SUBSTRING
                          ((SELECT     (', ' + CR.Name)
                              FROM         dbo.Nop_Customer_CustomerRole_Mapping CCRM INNER JOIN
                                                    dbo.Nop_CustomerRole CR ON CR.CustomerRoleID = CCRM.CustomerRoleID
                              WHERE     CCRM.CustomerID = C.CustomerID AND CCRM.CustomerRoleID <> 0 FOR XML PATH('')), 2, 999999) AS 'UserGroup', 
                      O.PurchaseOrderNumber AS 'BillingCode', PV.Name, COALESCE (CAST(OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / CAST(OPV.Quantity AS decimal(10, 2))) 
                      + (NULLIF (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0)) AS decimal(10, 3)), 0) 
                      AS 'All-In Item Cost', OPV.Quantity AS 'Number Purchased', COALESCE (CAST((OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)) 
                      + NULLIF ((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)), 0)) * OPV.Quantity AS decimal(10, 3)), 0) 
                      AS 'Extended All-In Cost', OPV.[UnitPriceInclTax] AS 'Base Item Cost', COALESCE (CAST(CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0) 
                      AS decimal(10, 3)), 0) AS 'Freight Item Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) 
                      * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)) / CAST(OPV.Quantity AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Handling Item Cost', 
                      OPV.[UnitPriceInclTax] * OPV.Quantity AS 'Extended Base Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Freight', 
                      COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Handling',
                          (((SELECT     COUNT(*)
                               FROM         dbo.Nop_OrderProductVariant OPV2
                               WHERE     OPV2.OrderID = O.OrderID) * 1) + 5) AS 'TotalHandling', O.[OrderShippingInclTax] AS 'TotalFreight', O.StoreID
FROM         dbo.Nop_Order AS O INNER JOIN
                      dbo.Nop_OrderProductVariant AS OPV ON OPV.OrderID = O.OrderID INNER JOIN
                      dbo.Nop_Customer AS C ON C.CustomerID = O.CustomerID INNER JOIN
                      dbo.Nop_ProductVariant AS PV ON PV.ProductVariantId = OPV.ProductVariantID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40


I am modifiying the view and adding this piece of code at the end of my view.

inner join  Nop_ShippingSpecialityServiceInfo sp
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40

so final view will be like this
SELECT O.OrderID, O.CreatedOn, o.ShippingRateComputationMethodID,
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'LastName')) AS 'LastName',
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'FirstName')) AS 'FirstName', SUBSTRING
                          ((SELECT     (', ' + CR.Name)
                              FROM         dbo.Nop_Customer_CustomerRole_Mapping CCRM INNER JOIN
                                                    dbo.Nop_CustomerRole CR ON CR.CustomerRoleID = CCRM.CustomerRoleID

                              WHERE     CCRM.CustomerID = C.CustomerID AND CCRM.CustomerRoleID <> 0 FOR XML PATH('')), 2, 999999) AS 'UserGroup', 
                      O.PurchaseOrderNumber AS 'BillingCode', PV.Name, COALESCE (CAST(OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / CAST(OPV.Quantity AS decimal(10, 2))) 
                      + (NULLIF (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0)) AS decimal(10, 3)), 0) 
                      AS 'All-In Item Cost', OPV.Quantity AS 'Number Purchased', COALESCE (CAST((OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)) 
                      + NULLIF ((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)), 0)) * OPV.Quantity AS decimal(10, 3)), 0) 
                      AS 'Extended All-In Cost', OPV.[UnitPriceInclTax] AS 'Base Item Cost', COALESCE (CAST(CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0) 
                      AS decimal(10, 3)), 0) AS 'Freight Item Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) 
                      * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)) / CAST(OPV.Quantity AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Handling Item Cost', 
                      OPV.[UnitPriceInclTax] * OPV.Quantity AS 'Extended Base Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Freight', 
                      COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Handling',
                          (((SELECT     COUNT(*)
                               FROM         dbo.Nop_OrderProductVariant OPV2
                               WHERE     OPV2.OrderID = O.OrderID) * 1) + 5 ) AS 'TotalHandling', (O.[OrderShippingInclTax] ) AS 'TotalFreight', 
                      O.StoreID
                      , sp.AdditionalPrice
FROM         dbo.Nop_Order AS O INNER JOIN
                                           dbo.Nop_OrderProductVariant AS OPV ON OPV.OrderID = O.OrderID INNER JOIN
                      dbo.Nop_Customer AS C ON C.CustomerID = O.CustomerID INNER JOIN
                      dbo.Nop_ProductVariant AS PV ON PV.ProductVariantId = OPV.ProductVariantID 
                      
                      
inner join  Nop_ShippingSpecialityServiceInfo sp 
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40 

addition piece of code:---> I am joining tbale called  Nop_ShippingSpecialityServiceInfo(attached image) to the current view as you can see in modified view(attached image) what I am trying to build column with orderID 3886 is reaping 72 times.because I am comparing  sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID. and ShippingRateComputationMethodID. is 13 is repeating 18 times for each order.I don't want to be repeate that many times I want to be as it is like in first image orderinfo.png
3886 is repeating 4 times. and I also want to add my piece of code

inner join  Nop_ShippingSpecialityServiceInfo sp
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40 .

Please guide me in this.
orderInfo.PNG
modiiedorder.PNG
shippingspecialityservicetable.PNG
ASKER CERTIFIED SOLUTION
jogos

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros