Create dynamic query using Rexx



IBM's Command List programming language & Restructured Extended Executor

Create dynamic query using Rexx

Postby 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
jegan_selvan
 
Posts: 2
Joined: Fri Oct 12, 2012 3:32 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Create dynamic query using Rexx

 

Re: Create dynamic query using Rexx

Postby 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".
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Create dynamic query using Rexx

Postby 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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: Create dynamic query using Rexx

Postby 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:
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
jegan_selvan
 
Posts: 2
Joined: Fri Oct 12, 2012 3:32 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Create dynamic query using Rexx

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

what happened when You tested it?

the coding does not match the initial description !
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: Create dynamic query using Rexx

Postby 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,
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times


Return to CLIST & REXX

 


  • Related topics
    Replies
    Views
    Last post