SQL view problem

itinfo7
itinfo7 used Ask the Experts™
on
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

Open in new window



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 

Open in new window


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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You want a join and you get one. But if 13 records meet the join-condition then you get 13 results for each original result.

You must look in to that 13 records and say which record you want to be returned and then see how you can change your filter to only get that 1 result.

There are 2 columns I think that can reduce the results but I cannot tell if that will always return a unique record in your db.

where sp.IsActive = 1 and sp.AdditionalPrice > 0

Author

Commented:
Original order 3886 is only one. and I am comparing shipping speciality service is 18 so orginal order should repeat 18 times right that's what I need to do.right now it is going longer till 72.Can I add some subquery for orginal order and make  that It repeat 18 times.

somehting like this.

elect  o.orderID ,sp.AdditionalPrice,o.ShippingRateComputationMethodID,sp.ShippingSpecialityServiceInfoID 
from [Nop_Order] o
inner join  Nop_ShippingSpecialityServiceInfo sp 
on sp.ShippingRateComputationMethodID = o.ShippingRateComputationMethodID and sp.StoreID = o.StoreID
where  sp.AdditionalPrice<>0 and sp.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2'

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial