[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] INPUT Parms
Here is a procedure that I wrote for the same purpose.
Just put this procedure in you SQR and after you get the input from user, in
the next statement call this procedure as:
Do AddDelimiters($campus)
This will reformat your $campus and you can put it into your where clause.
Begin-Procedure AddDelimiters(:$InputParam)
Let #delimiter_pos = 0
Let #Found = 0
Let $tmp_str1 = ''
Let $tmp_str2 = $InputParam
Let $tmp_str3 = ''
While 1
Find ',' IN $tmp_str2 #delimiter_pos #Found
If #Found <> -1
Let $tmp_str1 = SubStr($tmp_str2, 1, #Found ) || ''', '''
Let $tmp_str2 = SubStr($tmp_str2, #Found + 2, length($tmp_str2))
Let $tmp_str3 = $tmp_str3 || $tmp_str1
Else
Let $tmp_str3 = $tmp_str3 || $tmp_str2
Break
End-If
End-While
If SubStr($tmp_str3, 1, 1) != ''''
Let $tmp_str3 = '''' || $tmp_str3 || ''''
End-If
Let $InputParam = $tmp_str3
End-Procedure
Hope this helps,
Dèv Rishi Sahani
--------------------
FESCo DB Systems
Fidelity Investments
-----Original Message-----
From: Hunsaker, Michael S [mailto:mhunsake@indiana.edu]
Sent: Monday, October 20, 2003 9:51 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] INPUT Parms
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users