Link to home
Start Free TrialLog in
Avatar of CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx

asked on

SQL - SP needs a little help

This SP gets all the information I need but it creates 3 rows for every record.  The [CustomFieldId] is the problem but I don't know how to fix it.



SELECT    
	   li.[Description], 	   
	   Cast(lp.[DecimalNativeValue] As INT) As 'LotSize', 
	   lp.[EnumNativeValue] AS 'Source',
	   lp.[DateTimeNativeValue] AS 'SDate',	   
           li.[Quantity],
           li.[PerUnitAmount],
	   li.[TotalAmount],  
            i.[Comments],
	    i.[ShippingFirstName],      
            i.[ShippingLastName],       
            i.[ShippingStreet1],
            i.[ShippingStreet2],
            i.[ShippingCity],
            i.[ShippingStateRegion],
            i.[ShippingZipPostal],      
	   so.[FirstItemAmount],      
           so.[AdditionalItemAmount]

  FROM [dbo].[RWX_LineItems] li
    JOIN [dbo].[RWX_Invoices] i ON i.[Id] = li.[InvoiceId] 
    JOIN [dbo].[RWX_ShippingOptions] so ON i.[ShippingOptionId] = so.[Id]
    JOIN [dbo].[RWX_ListingProperties] lp ON li.[ListingId] = lp.[ListingId]

  WHERE i.[Status] = 'Paid' AND i.[Shipped] = 0 
    AND (lp.[CustomFieldId] = 160806 OR lp.[CustomFieldId] = 160792 OR lp.[CustomFieldId] = 160801) 
    AND i.[OwnerId] = @OwnerId

Open in new window

Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

The general idea would be to use a SELECT DISTINCT, but that will only work if all column values match between the rows. However, if the values differ between rows, then you'll need to decide how to determine which is the correct row to display/use.
I have a dollar that says if you look at the JOIN..ON criteria with alll the JOINs that you'll find that there are multiple child rows for a given parent row, either because that's the case in your data or  the ON columns do not uniquely identify a 1:1 relationship between your data.

>    JOIN [dbo].[RWX_ListingProperties] lp ON li.[ListingId] = lp.[ListingId]
Since Listing Properties uses the foreign key ListingID instead of a primary key id per the rest of the naming convention, I'd start here.
You can put the values for the different CustomFieldIds into separate columns, like below.  Naturally you can give more meaningful names to the code lookup values while I couldn't, since I didn't know the equivalent business value each id value represented.


  SELECT    
               li.[Description],         
               Cast(lp.[DecimalNativeValue] As INT) As 'LotSize',
               lp_ca.*,
                 li.[Quantity],
                 li.[PerUnitAmount],
               li.[TotalAmount],  
                 i.[Comments],
               i.[ShippingFirstName],      
                 i.[ShippingLastName],      
                 i.[ShippingStreet1],
                 i.[ShippingStreet2],
                 i.[ShippingCity],
                 i.[ShippingStateRegion],
                 i.[ShippingZipPostal],      
                 so.[FirstItemAmount],      
                 so.[AdditionalItemAmount]

  FROM [dbo].[RWX_LineItems] li
  JOIN [dbo].[RWX_Invoices] i ON i.[Id] = li.[InvoiceId]
  JOIN [dbo].[RWX_ShippingOptions] so ON i.[ShippingOptionId] = so.[Id]
  CROSS APPLY (
            SELECT
                          MAX(CASE WHEN lp.[CustomFieldId] = 160792 THEN lp.[EnumNativeValue] END) AS Score_160792,
                          MAX(CASE WHEN lp.[CustomFieldId] = 160792 THEN lp.[DateTimeNativeValue] END) AS SDate_160792,
                          MAX(CASE WHEN lp.[CustomFieldId] = 160801 THEN lp.[EnumNativeValue] END) AS Score_160801,
                          MAX(CASE WHEN lp.[CustomFieldId] = 160801 THEN lp.[DateTimeNativeValue] END) AS SDate_160801,
                          MAX(CASE WHEN lp.[CustomFieldId] = 160806 THEN lp.[EnumNativeValue] END) AS Score_160806,
                          MAX(CASE WHEN lp.[CustomFieldId] = 160806 THEN lp.[DateTimeNativeValue] END) AS SDate_160806
            FROM [dbo].[RWX_ListingProperties] lp
            WHERE li.[ListingId] = lp.[ListingId]
                AND (lp.[CustomFieldId] = 160792 OR lp.[CustomFieldId] = 160801 OR lp.[CustomFieldId] = 160806)
      ) AS lp_ca
  WHERE i.[Status] = 'Paid'
       AND i.[Shipped] = 0
       AND i.[OwnerId] = @OwnerId
Hi,
Can you please provide these 3 rows and what is expected out of it.?  Meanwhile please try below-

--

;WITH CTE AS
(
	SELECT    
		   li.[Description], 	   
		   Cast(lp.[DecimalNativeValue] As INT) As 'LotSize', 
		   lp.[EnumNativeValue] AS 'Source',
		   lp.[DateTimeNativeValue] AS 'SDate',	   
			   li.[Quantity],
			   li.[PerUnitAmount],
		   li.[TotalAmount],  
				i.[Comments],
			i.[ShippingFirstName],      
				i.[ShippingLastName],       
				i.[ShippingStreet1],
				i.[ShippingStreet2],
				i.[ShippingCity],
				i.[ShippingStateRegion],
				i.[ShippingZipPostal],      
		   so.[FirstItemAmount],      
			   so.[AdditionalItemAmount]
	FROM [dbo].[RWX_LineItems] li
		JOIN [dbo].[RWX_Invoices] i ON i.[Id] = li.[InvoiceId] 
		JOIN [dbo].[RWX_ShippingOptions] so ON i.[ShippingOptionId] = so.[Id]
		JOIN [dbo].[RWX_ListingProperties] lp ON li.[ListingId] = lp.[ListingId]
	WHERE i.[Status] = 'Paid' AND i.[Shipped] = 0 
	AND (lp.[CustomFieldId] = 160806 OR lp.[CustomFieldId] = 160792 OR lp.[CustomFieldId] = 160801) 
	AND i.[OwnerId] = @OwnerId
)
SELECT 
[Description],LotSize,[Source],SDate,[Quantity],
[PerUnitAmount],
[TotalAmount],  
[Comments],
[ShippingFirstName],      
[ShippingLastName],       
[ShippingStreet1],
[ShippingStreet2],
[ShippingCity],
[ShippingStateRegion],
[ShippingZipPostal],      
[FirstItemAmount],      
[AdditionalItemAmount]
FROM CTE
[Description],LotSize,[Source],SDate,[Quantity],
[PerUnitAmount],
[TotalAmount],  
[Comments],
[ShippingFirstName],      
[ShippingLastName],       
[ShippingStreet1],
[ShippingStreet2],
[ShippingCity],
[ShippingStateRegion],
[ShippingZipPostal],      
[FirstItemAmount],      
[AdditionalItemAmount]

--

Open in new window


Hope it helps!
Avatar of CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx

ASKER

This shows the 3 rows that should group together.  Scott, I used your SP above to get the results as shown.  It's the same thing my SP gets.

User generated image
Scott, I take it back...  When I use your SP I get an error "The multi-part identifier "lp.DecimalNativeValue" could not be bound."
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, sorry, I changed the alias name in the outer query from "lp" to "lp_ca" (lp cross apply).  I missed changing the alias on the "DecimalNativeValue" column in the outer SELECT:

SELECT    
               li.[Description],          
               Cast(lp_ca.[DecimalNativeValue] As INT) As 'LotSize',
               lp_ca.*, /*you may want to list individual columns here*/
                 li.[Quantity],
                 li.[PerUnitAmount],
               li.[TotalAmount],  
                 i.[Comments],
               i.[ShippingFirstName],      
                 i.[ShippingLastName],      
                 i.[ShippingStreet1],
                 i.[ShippingStreet2],
                 i.[ShippingCity],
                 i.[ShippingStateRegion],
                 i.[ShippingZipPostal],      
                 so.[FirstItemAmount],      
                 so.[AdditionalItemAmount]
...
Thanks