February 25, 2011 at 8:41 am
Hi there
I have a sales line table which contains lines with remarks.
On the other hand I already have a report with per delivery note one line as result. I have to join this one line with the results of all the remarks linked to this delivery note (linked throught order nr).
So my stored procedure for the delivery notes is :
set @sql='
SELECT distinct TOP (100) PERCENT PWSH.[Responsibility Center], PWSL.[Destination No_], STA.Name, STA.Address, STA.[Address 2], STA.City, STA.[Country Code], STA.[Post Code], PWSL.[Qty_ Colli], PWSL.[Colli No_], PWSL.[Delivery Note No_], PWSH.[Shipping Agent Code], PWSH.[Shipment Date]
FROM dbo.[OPS Sepia$Posted Whse_ Shipment Line] AS PWSL INNER JOIN
dbo.[OPS Sepia$Posted Whse_ Shipment Header] AS PWSH ON PWSL.No_ = PWSH.No_ INNER JOIN
dbo.[OPS Sepia$Ship-to Address] AS STA ON PWSL.[Ship-to Code] = STA.Code AND PWSL.[Destination No_] = STA.[Customer No_] INNER JOIN
dbo.[OPS Sepia$Sales Line] AS SL ON PWSL.[Source No_] = SL.[Document No_] AND PWSL.[Source Line No_] = SL.[Line No_]
WHERE (PWSH.[Shipping Agent Code] = ''' + @ShippingAgentCode + ''') and PWSH.[Shipment Date] = convert(datetime,''' + convert(varchar(10),@date,103)+ ''',103)'
exec (@sql)
The query for the remarks is for exemple :
select SL.[Document No_], SL.Description FROM dbo.[OPS Sepia$Sales Line]AS SL
where SL.No_ like 'COM%' and SL.Description is not null
but this one often delivers more than one line per order and there can be more orders linked to one delivery note.
How to resolve ?
JV
February 25, 2011 at 11:13 am
What exactly do you consider being a problem that needs to get resolved?
Based on your requirement
I have to join this one line with the results of all the remarks linked to this delivery note
you're dealing with a 1:n relationship that implies returning more than one row per delivery note.
You need to define your requirement. Either you apply an aggregation (MIN(),MAX(),LEN()<x) or you could concatenate all remarks. Or you could use the result as it is. It depends.
February 28, 2011 at 8:03 am
Hi
I should have all remarks.
How to build up ?
Kind regards
JV
February 28, 2011 at 10:46 am
jvElecenter (2/28/2011)
HiI should have all remarks.
How to build up ?
Kind regards
Each of the options I posted in my previous post will meet that requirement.
Maybe you should show us some sample data and your expected result based on those sample.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply