[sqr-users] INPUT Parms

["Hunsaker, Michael S" <mhunsake@indiana.edu>]

Hello -

I am trying to use an INPUT statement to grab one or more choices from
the user.  For example, my input statement:

Input $campus 'Enter Campus Code: ' type=char

I am asking the user to enter the campus or campuses to view data for.
The main problem I am running into is building the dynamic where clause.
Since the user input will look something like the below line, I cannot
build the data into a where clause without the single quotes.

BL, IN, SB

Is there an easy way to pad each input with single quotes?  I would like
the response to look like this in order to build the dynamic where
clause.

'BL','IN','SB'

Since I am new to SQR, I am probably overseeing a particular function
that would help me out.  Normally there are "explode/implode" functions
to be used in cases like these. 

Thanks.

Mike


-----Original Message-----
From: Steve Cavill [mailto:steve.cavill@infoclarity.com.au] 
Sent: Friday, October 17, 2003 10:52 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Dynamic Substitution Vars

Matt,
How about setting up the command line  so when the job runs it simply
reads
the parameters off the command line in pairs:
SQRW.EXE <connect> <flags> parm1name=parm1value parm2name=parm2value....
etc
use ONE input command in a loop with the batch-mode option to stop
reading
when you run out of parms.
then use an evaluate to determine which parms have been supplied:

e.g.

SQRW.EXE <connect> <flags> begin-date='01-jan-03' code='blah blah' ....
etc
...
 while (1)
  input $parm status=#stat batch-mode
  if #stat=3
   break
  else
   do get_parm ($parm)
  end-if
 end-while

begin-procedure get_parm($a)
 unstring $a by '=' into $parm $value
 !either print the parms now, or save in an array for printing later
evaluate $parm
 when 'begin-date'
  let $begin-date=$value
 when 'code'
  let $code=$value
 when....etc
end-evaluate

If you don't like the evaluate as hard to maintain you could also load
the
parms into a table in pairs.  the table would have three (or more)
columns
Report_code, input_parm_name, input_value

then your program can do a select against the table to get all the
required
parms when required:
e.g.
begin-select
begin-date
from table
where   input_parm_name = 'begin-date' and Report_code='xxx'
end-select

The advantage of saving the parms in a table is you can keep the parms
for
as long as you like if you use one table and make the Report_code
unique.
So you can retrieve the parms at any time for history or problem
diagnosis.

You can either load the parms into the table within the SQR program or
have
a separate scheduler that loads the parms then calls the SQR.  This is
how
the batch scheduler works in many applications, including but not
limited to
Peoplesoft.

Cheers, Steve.

-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Ray Ontko
Sent: Saturday, 18 October 2003 8:32 AM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Dynamic Substitution Vars


Matt,

I don't know if this will work for you, but in situations where
I've needed dynamic SQR code, I have writen an SQR program that
generates the SQR program (using WRITE or PRINT).  You can even
use the generator program to launch the generated program using
CALL SYSTEM.

Ray

On Fri, Oct 17, 2003 at 04:26:20PM -0400, Matt Rogish wrote:
> All,
>
> I sent a message earlier about dynamic 'input' variables and got some
> interesting suggestions on and off list, none of which worked out
quite as
> I had hoped.
>
> To re-cap:
> We have lots of programs which have this basic structure:
> set up variables
> ask for input
> run some SQL
> show report
> show summary
>
> In order to ease maintenance what I'd like to do is standardize the
"set
up
> variables", "ask for input", and "show summary" pieces using some sort
of
> dynamic SQR.
>
> In the summary section we list some report variables (counters and
things)
> and we like to list the input parameters because there's nothing like
> getting a complex multi-section report and trying to guess what input
> parameters the particular report was run with (because the user
forgets,
or
> you look at the report a month later, or they were in Brio Portal but
have
> since been changed, etc.).
>
> The way we have it now is something like this:
> begin-procedure get-input
>   Input $code 'Enter some code'
>   Input $begin_date 'Enter report begin date' type=date
>   ..
> end-procedure
>
> begin-procedure show-input-summary
>   print 'Input Parameters:' (+1) bold
>
>   print 'Some Code: ' (+2)
>   print $code
>
>   print 'Begin Date: ' (+2)
>   print $begin_date
>
>   etc.
> end-procedure
>
> Oftentimes there are many parameters so it becomes cumbersome to list
long
> blocks of Input and print statements.  It also makes maintenance a
> nightmare because you don't know which programs require what input and
if
> you want to change/add one you have to change it several places.
>
> So, the goal is to minimize code changes when the input parameter list
> changes -- either by adding, changing, or removing input
> parameters.  Preferably, there would be one spot in the program where
I
> change a parameter list and the get-input and show-input-summary
procedures
> do not have to change!  It goes without saying that there might have
to be
> other changes to handle the addition or removal of a parameter, but
that
> can come later. :)
>
> Some ideas I've considered are:
> Idea One -- Build an array which stores the input parameter name
> ("some_code") and input query string ("Enter some code") then loop
through
> each one and build an 'Input' statement from this.
> Problem:
> I don't know how to execute dynamic SQR code, namely something like:
> let $stmt = 'Input $' || $input_var_name || ' ''' || $input_query ||
''''
> [$stmt]
> This gives an error:
> (SQR 4008) Unknown function or variable in expression: [$stmt]
>
> Idea Two -- Create substitution variables, loop through each of them,
and
> use them in Input statements.
> Problem:
> I can't dynamically *name* a substitution variable:
> Let #num = 1
> #DEFINE variable_number_#1
> Show {variable_number_1}
> This gives an error:
> (SQR 4707) No value found for substitution variable:
{variable_number_1}
>
> Any help/ideas?
>
>
> Thanks,
>
> --
> Matt Rogish - rogishmn@muohio.edu
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users