IBM Mainframe Forum

by **jegan_selvan** » Fri Oct 12, 2012 4:14 pm

Hi Team,

Good Morning.
I have a requirement to create a dynamic query in the flat file.

Example : One input file will be an CSV file with comma delimited file having the Field name with Record length 80 and FB.
Field-1 ,Field-2,Field-3 ,Field-4

The other input file will be an CSV file with Comma delimited data corresponding to input File 1. This is also FB and lets have the record length as 500.
data1-1,data1-2,data1-3,data1-4
data2-1,data2-2,data2-3,data2-4
data3-1,data3-2,data3-3,data3-4
data4-1,data4-2,data4-3,data4-4

We need to generate the output file like mentioned below.

Insert into
XXX.ABC
(Field-1 ,Field-2,Field-3 ,Field-4)
values
(data1-1,data1-2,data1-3,data1-4) ;
commit;

Insert into
XXX.ABC
(Field-1 ,Field-2,Field-3 ,Field-4)
values
(data2-1,data2-2,data2-3,data2-4) ;
commit;

Insert into
XXX.ABC
(Field-1 ,Field-2,Field-3 ,Field-4)
values
(data3-1,data3-2,data3-3,data3-4) ;
commit;

Here Table name will be given as a constant one to one variable - TName = XXX.ABC

Thaks for your help in advance

Regards,
Jegan S

by **BillyBoyo** » Fri Oct 12, 2012 4:33 pm

Unless you are considering paying, no-one here is going to write this for you. You have to do it yourself. If you get stuck, someone will be here with advice. But not having a single line of code does not count as "stuck".

by **enrico-sorichetti** » Fri Oct 12, 2012 4:50 pm

it is not that complicated ...
the only point to be analyzed carefully is the <data> part

500 / 4 means that each data field is longer than the 80 bytes of a card sysin

read the documentation of <whatever> you want to use to insert to see the requirements/limitations of the input file

and how to manage filed data longer than 80 bytes or the input lrecl

by **jegan_selvan** » Wed Oct 17, 2012 5:48 pm

Hi All,

This is my early coding now. Please correct me if anything i am wrong. I have used just three fields.

parse1:

Select all

IF POS('ROUTINE <-- ',data2) > 0 then
do
do
CALL WRITE 'INSERT INTO MF_TABLE ('
CALL WRITE "DATA1.0 || )"
call write " values('"||NAME||"','"||STATUS||"'",'"||AU_STAT||"'");
CALL write "COMMIT") ;"
call init
End
end
return
/* ------------------ end of parse1 -------------- */
init:
NAME='';STATUS='';AU_STAT='';s_init=""
return
/* ------------------ end of init -------------- */

Code'd

by **enrico-sorichetti** » Wed Oct 17, 2012 6:00 pm

what happened when You tested it?

the coding does not match the initial description !

by **NicC** » Wed Oct 17, 2012 6:46 pm

Why 'then do do' and 'end end'?
Why no code tags to present your code nicely? If you do not know about code tags search the forum, use the POSTREPLY button instead of the 'Quick Reply' button,

IBM Mainframe Forum

Create dynamic query using Rexx

Create dynamic query using Rexx

Re: Create dynamic query using Rexx

Re: Create dynamic query using Rexx

Re: Create dynamic query using Rexx

Re: Create dynamic query using Rexx

Re: Create dynamic query using Rexx