Hi,
I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlited 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