OK, so, as long as the result of whichever of the 50 odd WHEN cases that is
selected at runtime is the string g_select01 containing the selected SELECT
statement, and that statement returns a single numeric value (either a
single row or multiple rows, but only the single value per row (or at least
the same number and type of values), then you can place the PREPARE,
DECLARE, and OPEN statements just once after the END CASE statement and not
have to increase the amount of code by having a separate PREPARE st_clopmt
FROM g_select01 and DECLARE cu_clopmt FOR st_clopmt for each version of the
statement. So, just put whichever SELECT is finally built by the CASE
statement into a single character string variable name (not a different
string for each version of the SELECT) and you will be golden.
Is that the question?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Jun 17, 2013 at 1:39 PM, Randall Young (ADE) <
***@arkansas.gov> wrote:
> I am sorry. I am not making myself clear. Within the CASE statement I
> will have about 50 WHENs based on different p_pageline. The queries in
> each WHEN will be similar. I was trying to avoid adding a lot of extra
> lines to what is already bloated code. I was hoping that I could PREPARE
> statements and/or CREATE CURSORs at runtime when I knew what the value of
> p_pageline was and thus reduce the number of cursor names.
>
> Otherwise, it seems that I have to specifically set up each individual
> PREPARE and/or CREATE CURSOR in order for the compiler to successfully
> compile the code. Am I creating another problem by trying to reduce the
> number of cursors and the number of lines of code? Thanks for your kind
> help.
>
> --
> Randall Young
> ***@arkansas.gov
>
> "Blessed are we who can laugh at our surroundings for we shall never be
> considered completely sane."
>
>
> -----Original Message-----
> From: informix-list-***@iiug.org [mailto:
> informix-list-***@iiug.org] On Behalf Of Marco Greco
> Sent: Monday, June 17, 2013 11:21 AM
> To: informix-***@iiug.org
> Subject: Re: declaring cursors at run time
>
>
>
> On 17/06/13 15:40, Randall Young (ADE) wrote:
> > Is there a way to declare cursors at run time? I am trying something
> > like the following.
> >
> > CASE p_pageline
> >
> > WHEN 3619
> >
> > LET g_select01 =
> >
> > 'SELECT SUM(tot_exp)
> > FROM (',
> >
> > 'SELECT ',
> >
> >
> > '(exp1+exp2+exp3+exp4+exp5+exp6+exp7+exp8+exp9+exp10+',
> >
> >
> > 'exp11+exp12+exp13) tot_exp ',
> >
> > 'FROM expledgr ',
> >
> > 'WHERE yr = ',
> > gr_sisyear.cur_year[3,4],
> >
> > ' AND ( ',
> >
> >
> > 'key_orgn[1,4] = "2000" OR ',
> >
> >
> > 'key_orgn[1,4] between "2100" AND "2199" ',
> >
> > ') AND ',
> >
> >
> > 'key_orgn[5,8] BETWEEN "3100" AND "3119" ',
> >
> > 'AND ( ',
> >
> >
> > 'account BETWEEN "61000" AND "61199" OR ',
> >
> >
> > 'account BETWEEN "61300" AND "61699" OR ',
> >
> >
> > 'account BETWEEN "61800" AND "61999" ',
> >
> > ') ',
> >
> > ')'
> >
> > there would be other WHENs here
> >
> > END CASE
> >
> > PREPARE st_clopmf FROM g_select01
> >
> > When I try to EXECUTE st_clopmt, the compiler gives me the following.
> >
> > | The cursor 'st_clopmf' has not yet been declared in this program
> >
> > | See error number -4372.
> >
> > I am using IBM INFORMIX-4GL Version 7.50.FC5 and DB-Access Version
> 11.50.FC5.
> >
> > Thanks.
> >
> > Randall Young
> >
> > ***@arkansas.gov <mailto:***@arkansas.gov>
> >
> > "Blessed are we who can laugh at our surroundings for we shall never
> > be considered completely sane."
>
> Yes, you can do that kind of stuff, but it being a select (not INTO TEMP),
> you have to DECLARE a cursor for the statement after you PREPARE it, and
> either use a FOREACH, or an OPEN / FETCH / CLOSE sequence.
>
> --
> Ciao,
> Marco
>
> ______________________________________________________________________________
> Marco Greco /UK /IBM Standard disclaimers
> apply!
>
> Structured Query Scripting Language
> http://www.4glworks.com/sqsl.htm
> 4glworks
> http://www.4glworks.com
> Informix on Linux
> http://www.4glworks.com/ifmxlinux.htm
> _______________________________________________
> Informix-list mailing list
> Informix-***@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> _______________________________________________
> Informix-list mailing list
> Informix-***@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>