Combining records
-
Our company downloads AS400 files to SQL tables using SQL DTS. We have an acknowledgement file that uses order_number, order_type, and line_number as the unique key. Each record also has a reference field.
I need help in using DTS, a stored_procedure, or an active script to concatenate the reference field for uninque keys.
So if three records have the same order_number, order_type, and line_number , I need to create one record and concantenate each reference field.
record1 Order_number- 123, order_type -SO, line_number-1, ref.- abc
record2 Order_number- 123, order_type -SO, line_number-1, ref. - 456
record3 Order_number- 123, order_type -SO, line_number-1, ref.- ups
The output result should be
new record- order_number- 123, order_type-SO, line_number-1, ref.-abc 456 ups.
-
Well, I don't see a way to do this in one easy step. So, I would load the AS400 data into a staging table.
From the staging table, you may need to then populate another table that crosstabs the data from the original staging table. Then, you can query from that to populate the final destination table.
Hope this helps.
-
This might do it for you:
1. Create a master list of order-number-type-lines:
CREATE TABLE orderLines (
order_number INT
order_type VARCHAR(10)
line_number INT
all_refs VARCHAR(255) )
INSERT INTO orderLines
SELECT DISTINCT order_number, order_type, line_number, ""
FROM acknowledgements
2. Append all the references:
UPDATE orderLines
SET all_refs=LTRIM(ord.all_refs + " ") + ack.ref
FROM orderLines AS ord
JOIN (SELECT DISTINCT order_number, order_type, line_number, ref
FROM acknowledgements) AS ack
ON ord.order_number = ack.order_number
AND ord.order_type = ack.order_type
AND ord.line_number = ack.line_number
Regards,Bob Monahon
-
Thanks for the suggestion, but the results were not as expected. The output result was one record for each unique order_number, line_number, and order_type, but reinfo fields are not concat.
For example if there were 10 records for with unique order_number, line_number, and order_type, but different refinfo fields only one refinfo field is shown on the record.
-
This may help. It is not glamorous but it should do the trick.
declare @OrderNumber int, @PrevOrderNumber int
declare @OrderType Varchar(10),@PrevOrderType Varchar(10)
declare @LineNumber int,@PrevLineNumber int
declare @refs varchar(10)
declare @NewRefs varchar(100)
declare curOrderLines cursor for
select order_number,order_type,line_number, refs from OrderLines
order by order_number,order_type,line_number
Open curOrderLines
fetch curOrderLines into @OrderNumber, @OrderType, @LineNumber, @refs
select @PrevOrderNumber = @OrderNumber, @PrevOrderType = @OrderType, @PrevLineNumber = @LineNumber
select @NewRefs = ''
WHILE @@fetch_status = 0
BEGIN
IF @OrderNumber = @PrevOrderNumber and @OrderType = @PrevOrderType and @LineNumber = @PrevLineNumber
BEGIN
select @NewRefs = @NewRefs + @refs
END
ELSE
BEGIN
insert into OrderLinesNew values(@PrevOrderNumber, @PrevOrderType, @PrevLineNumber,@NewRefs)
select @PrevOrderNumber = @OrderNumber, @PrevOrderType = @OrderType, @PrevLineNumber = @LineNumber
select @NewRefs = @refs
END
fetch curOrderLines into @OrderNumber, @OrderType, @LineNumber, @refs
END
insert into OrderLinesNew values(@PrevOrderNumber, @PrevOrderType, @PrevLineNumber,@NewRefs)
CLOSE curOrderLines
deallocate curOrderLines
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply