Passing a variable number of search codes as parameters in ACE Report SQL in Unix
Short question:- I know I can hard code and where statement in clause eg and patmoprf.mocode in ("123456.00",234567.00", etc) but I want to make this generic from user entry string via a shell script. Is this possible. If so how ===================================================== More Eplanation below I am using informix version 4 on a unix box Comand to run is sacego -s -d live modmed49 $parameter1 $parameter2 $parameter3 $parameter4 Parameter 3 contains a variable number of search codes built up from a C program eg "123456.44", "678790.00", etc all values separated by commas ================================================= In my report I have the following SQL select mis.daadmno, mis.daurno, mis.adocta, mis.adate, patmoprf.mocode from mis, patmoprf where mis.daadmno = patmoprf.dmoadmn and patmoprf.mocode in ($param3) into temp reqmis ; I know I can hard code and patmoprf.mocode in ("123456.00",234567.00", etc) but I want to make this generic from user entry Passing the p[arameter 3 string obtained from the shell script in the report uses the string as one entry rather than individual entries separated by commas. Can anyone please help me in explaining if I can pass in a string contaning a variable number of search codes to be used in the where clause in statement above. All codes are separated by commas. Thanks Danny Di Biase
Danny Di Biase wrote: > Short question:- > > I know I can hard code and where statement in clause eg > and patmoprf.mocode in ("123456.00",234567.00", etc) > but I want to make this generic from user entry string via a shell script. > > Is this possible. If so how? [...expanded version of question snipped...] Because of the way embedded SQL works, and the way ACE works, there are not many options. One option is to repeat whatever parameters are specified on the command line a number of times (repeats in the IN list don't do any damage). Howver, that pre-supposes that you shell script knows how many items are in the IN list, and you have to get the number right. and you impose an upper bound on the number of arguments permitted. The other alternative is to compile the ACE report on the fly -- have the shell script generate the IN clause and then compile the report and run it. I've used the second technique; I've not tried the first but it should work. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Thank you to all thos that have helped. I will try the solution -- ----------------------------------------------------- Click here for Free Video!! http://www.gohip.com/free_video/ Jonathan Leffler <jleffler@earthlink.net> wrote in message news:3917B4DF.CE82CF85@earthlink.net... > Danny Di Biase wrote: > > > Short question:- > > > > I know I can hard code and where statement in clause eg > > and patmoprf.mocode in ("123456.00",234567.00", etc) > > but I want to make this generic from user entry string via a shell script. > > > > Is this possible. If so how? [...expanded version of question snipped...] > > Because of the way embedded SQL works, and the way ACE works, there > are not many options. One option is to repeat whatever parameters > are specified on the command line a number of times (repeats in the > IN list don't do any damage). Howver, that pre-supposes that you > shell script knows how many items are in the IN list, and you have > to get the number right. and you impose an upper bound on the number > of arguments permitted. The other alternative is to compile the ACE > report on the fly -- have the shell script generate the IN clause > and then compile the report and run it. > > I've used the second technique; I've not tried the first but it > should work. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > >