Hello Subathra,
I had this CSV structure in the input as below, let's imagine this is the same as you get from database.
Source is not the same, but structure is, so I would like to discuss this example.
N;10012;20031118;292.67;4
N;10013;20031119;3627.46;3
N;10014;20031120;11899.77;8
N;10015;20031121;292.67;4
S;4324;10012;101;23;19.98;2;39.96
S;4325;10012;102;24;21.89;4;87.56
S;4326;10012;103;46;115.23;1;115.23
S;4327;10012;104;311;8.32;6;49.92
S;4328;10013;105;54;12.43;2;24.86
S;4329;10013;107;435;543.4;6;3260.4
S;4330;10013;108;4526;342.2;1;342.2
S;4331;10014;109;53;6.32;4;25.28
S;4332;10014;110;3452;12.23;4;48.92
S;4333;10014;111;54;12.43;23;285.89
S;4334;10014;112;2;7.11;6;42.66
S;4335;10014;113;76;34.43;42;1446.06
S;4336;10014;114;4443;110.3;64;7059.2
S;4337;10014;115;654;4.43;432;1913.76
S;4338;10014;116;6544;98;11;1078
S;4339;10015;117;23;19.98;2;39.96
S;4340;10015;118;24;21.89;4;87.56
S;4341;10015;119;46;115.23;1;115.23
S;4342;10015;120;311;8.32;6;49.92
I created TEMP structure in the input side of the map that will be Header One - Details, Header Two - Details, ... rather then All Headers - All Details.
Map screenshot is attached.
You cannot have TEMP structure in your map, but that might be done by extended rule in the Post Session, that can see both sides of the map and that will be executed after direct links. You have to have at least one direct link per group/record to get mapping, that will be dummy link to create any output, but Post Session will override it with correct tructure.
Extended rule used in the INPUT side in my map is below, but you will have to modify for your map:
integer header_count;
integer details_count;
integer temp_index_details;
integer header_i;
integer details_i;
header_count = count($header[*]);
details_count = count($details[*]);
header_i = 1;
details_i = 1;
temp_index_details = 1;
while ( header_count >= header_i ) do
begin
$TEMP_GROUP[header_i].#TEMP_invoice_ID_header = $header[header_i].#invoice_ID_header;
$TEMP_GROUP[header_i].#TEMP_date_header = $header[header_i].#date_header;
$TEMP_GROUP[header_i].#TEMP_count_details_header = $header[header_i].#count_details_header;
$TEMP_GROUP[header_i].#TEMP_total_amount_header = $header[header_i].#total_amount_header;
while ( details_count >= details_i ) do
begin
if ( $details[details_i].#invoice_ID_detail = $header[header_i].#invoice_ID_header ) then
begin
$TEMP_details[header_i][temp_index_details].#TEMP_detail = $details[details_i].#detail;
$TEMP_details[header_i][temp_index_details].#TEMP_invoice_ID_detail = $details[details_i].#invoice_ID_detail;
$TEMP_details[header_i][temp_index_details].#TEMP_ID_detail = $details[details_i].#ID_detail;
$TEMP_details[header_i][temp_index_details].#TEMP_unit_ID_detail = $details[details_i].#unit_ID_detail;
$TEMP_details[header_i][temp_index_details].#TEMP_unit_price_detail = $details[details_i].#unit_price_detail;
$TEMP_details[header_i][temp_index_details].#TEMP_qty_detail = $details[details_i].#qty_detail;
$TEMP_details[header_i][temp_index_details].#TEMP_total_price_detail = $details[details_i].#total_price_detail;
details_i = details_i + 1;
temp_index_details = temp_index_details + 1;
end
else
begin
details_i = details_i + 1;
end
end
header_i = header_i + 1;
details_i = 1;
temp_index_details = 1;
end
That is just an idea and similar example I have.
Kind regards,
Mirjana
------------------------------
Mirjana Vojvodic
------------------------------
Original Message:
Sent: Fri June 28, 2024 07:54 AM
From: Subathra Bairavan
Subject: SQL Outbound Translation map
Hi Mirjana,
I am extracting the data from SQL(Input side) and writing them to the CSV file (Output side)
Thanks,
Subathra Bairavan
------------------------------
Subathra Bairavan
Original Message:
Sent: Wed June 26, 2024 01:59 AM
From: Mirjana Vojvodic
Subject: SQL Outbound Translation map
Hi Subathra,
Are you selecting data from database (SQL in the input side of the map) or you are inserting into database (using SQL in the output side of the map)?
Kind regards,
Mirjana
------------------------------
Mirjana Vojvodic
Original Message:
Sent: Mon June 10, 2024 10:14 AM
From: Subathra Bairavan
Subject: SQL Outbound Translation map
Hi Fabrizio,
Yes I tried Group to on the SQL statement still its printing my header first and then the line details.I have raised the question to IBM support folks and they suggested me to use Key field concept.I haven't tried yet.I will let you know how it goes..
------------------------------
Subathra Bairavan
Original Message:
Sent: Tue June 04, 2024 04:22 AM
From: Fabrizio Allegrini
Subject: SQL Outbound Translation map
Sorry for my stupid answer, but have you tried to order the sql query to obtain that output?
------------------------------
Fabrizio Allegrini
Original Message:
Sent: Thu May 30, 2024 02:14 PM
From: Subathra Bairavan
Subject: SQL Outbound Translation map
Hi,
We are trying to Integrate Sterling Integrator with Net Suite database using ODBC Connection.The ODBC setup is done and Pool Connection is also done successfully.I have created the Outbound map.. We have two queries used one is at the header level and another one is at the line level.
--HeadeQuery
--HeaderRec(to pull the values)
--field
--Group(100 times)
--GroupQuery
--GroupRec
--field
When I try to run the map all the lines are printed but not grouped by.When i try to manipulate and play its bringing my application down..Is there a way to handle it..
Output I am getting
------------------------------
A|1|
B|1|
B|2|
A|2|
All I need is the below expected output
A|1|
B|1|
A|2|
B|2|
How this can be achieved.Any help is appreciated..
Thanks,
Subathra Bairavan
------------------------------
Subathra Bairavan
------------------------------