Page 1 of 1

Create dynamic query using Rexx

PostPosted: Fri Oct 12, 2012 4:14 pm
by jegan_selvan
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

Re: Create dynamic query using Rexx

PostPosted: Fri Oct 12, 2012 4:33 pm
by BillyBoyo
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".

Re: Create dynamic query using Rexx

PostPosted: Fri Oct 12, 2012 4:50 pm
by enrico-sorichetti
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

Re: Create dynamic query using Rexx

PostPosted: Wed Oct 17, 2012 5:48 pm
by jegan_selvan
Hi All,

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

parse1:
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

Re: Create dynamic query using Rexx

PostPosted: Wed Oct 17, 2012 6:00 pm
by enrico-sorichetti
what happened when You tested it?

the coding does not match the initial description !

Re: Create dynamic query using Rexx

PostPosted: Wed Oct 17, 2012 6:46 pm
by NicC
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,