[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
The way I do it is to concatinate the quotes seperately. If you collect
the input values BL, IN and SB from input variables $LOC1, $LOC2 and
$LOC3 and want to build the following where clause:
where $SCHOOL in ('BL', 'IN','SB')
First build the part in parenthesise with code like this:
LET $CLAUSE =
'('
|| '''' || $LOC1 || '''' || ','
|| '''' || $LOC2 || '''' || ',' ! repeat as many times as needed
for all locations
|| '''' || $LOC3 ||
|| ')'
Then add the where clause like this:
select
SCHOOL
from (table name)
where SCHOOL in [$CLAUSE]
Knapp, Richard wrote:
>I'm on version 4 of SQR and have always built up quoted and comma separated
>strings by concatenating the elements. I use chr() to create the repeated
>elements like ', and ).
>
>Richard Knapp
>Database Programmer/Analyst
>Institutional Research and Planning
>University of Missouri System
>573-882-8856
>knappr@umsystem.edu
>
>
>-----Original Message-----
>From: Hunsaker, Michael S [mailto:mhunsake@indiana.edu]
>Sent: Monday, October 20, 2003 8: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
>
>
--
Gar Longworth
IS Specialist, Application Development
Information & Media Technologies
University of Wisconsin - Milwaukee
P. O. Box 413
Milwaukee, WI 53201
gpl@uwm.edu
414-229-6183
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users