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
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_ListingProperti es] 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.
> JOIN [dbo].[RWX_ListingProperti
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.[DecimalNativeValu e] 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_ListingProperti es] 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
SELECT
li.[Description],
Cast(lp.[DecimalNativeValu
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
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_ListingProperti
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-
Hope it helps!
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]
--
Hope it helps!
ASKER
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]
...
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]
...
ASKER
Thanks