May 8, 2024 at 3:32 pm
Hi,
I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlighted in yellow.
I need to combine both the headers and lines using a UNION operation to display the result below.
From the line table, I need to add 3 empty fields to match the number of fields in the header table for the union operation.
The results currently show three commas in the output text file1, but I need the result without these extra commas as seen in results file2.
How can I achieve this?
select HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount
from header
union all
select LineLEvel,InvNumber,TAmount,Code,InvoiceDate,PNumber,'','',''
from lines
File1
HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount
LineLevel,InvNumber,TAmount,Code,InvoiceDate,PNumber,,,
H,1,5,8901230,19192,10000008927,102-4490-4330-18,163.00,200.00,
L,817053,163.0000,804,2024-03-14,n/a,,,
H,2,5,8901230,19192,10000008927,102-4527-4408-1,1393.95,5438,
L,6054660,4.7000,804,2024-03-05,n/a,,,
L,6054750,14.2500,804,2024-03-05,n/a,,,
File2
HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount
LineLevel,InvNumber,TAmount,Code,InvoiceDate,PNumber
H,1,5,8901230,19192,10000008927,102-4490-4330-18,163.00,200.00,
L,817053,163.0000,804,2024-03-14,n/a
H,2,5,8901230,19192,10000008927,102-4527-4408-1,1393.95,5438,
L,6054660,4.7000,804,2024-03-05,n/a
L,6054750,14.2500,804,2024-03-05,n/a
May 8, 2024 at 4:24 pm
Well you should be able to just do two data flows, first one selects from header and creates the files the other one selects from line and does an append.
May 8, 2024 at 4:35 pm
Thanks for your response.
The first data flow creates the header, which will only display all the header records. The second one creates the lines, showing all the line records. How can you append between two files and ensure the lines are under the correct header?
May 8, 2024 at 5:34 pm
What do you have that identifies which lines go with which headers?
May 10, 2024 at 9:48 am
I can't quite work out what's going on here.
It sounds like all of your data is coming from text files and SQL Server is not involved at all. Is that correct?
Also, if a text file contains multiple rows of headers, that implies that you want to create multiple combined text files as the output (one for each header row), is that correct?
If so, the question already asked above needs to be answered: what is the link between a particular header row and its associated data rows?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply