[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
[sqr-users] RE: Passing Variables to Inline Views
This can work if you put the input statement in a loop, have the users input the
values one by one, and concatenate them. Here is an example I have used:
move '''' to $regions ! set to starting quote
while 1 = 1
input $region_in maxlen=10 'Enter Territory Code or leave blank to
exit'
uppercase $region_in
if rtrim($region_in, ' ') = '' ! user has left blank to exit
loop; append final quote and exit loop
concat '''' with $regions
break
end-if
if $regions <> '''' ! append end quote/comma/begin quote if
there is already a value in $regions
concat ''',''' with $regions
end-if
concat $region_in with $regions
end-while
.................
let $select_regions = '('||$regions||')'
.................
BEGIN-SELECT
.................
where c.region_cd in [$select_regions]
END-SELECT
HTH,
Denise White
Sr. Software Engineer
Vicor
--__--__--
Message: 16
From: "Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>
To: "'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
Subject: RE: [sqr-users] Passing Variables to Inline Views
Date: Wed, 8 Jan 2003 17:23:11 -0800
Reply-To: sqr-users@sqrug.org
So the users enter the values of $ga_numbers? That seems fraught with
peril. You have to be sure the users type the list with a left parenthesis,
one single quote, a value, one single quote, a comma, repeat as necessary,
... and a right parenthesis. (You only need two single quotes within the
program itself for the SQR compiler to differentiate between the end of a
string and a quote within a string.)
For debugging, you might output the value of $ga_numbers. For ongoing use,
I would write code to validate that string character by character, as hard
as that would be. There are a lot of ways it could be wrong.
The where clause won't work without the [] brackets around $ga_numbers. You
could write "and ale.ga_nbr in ($ga_number1, $ga_number2, ...)" if you knew
how many numbers there would be. Then the users should enter each
$ga_number WITHOUT the parenthesis, quotes, and commas. But an ordinary
variable can't contain a list of literal values. The SQR interface to the
SQL interpreter won't expect to find multiple values and punctuation in an
ordinary variable. It needs you to use a dynamic variable.
-----Original Message-----
From: CARFREH1@Nationwide.com [mailto:CARFREH1@Nationwide.com]
Sent: Wednesday, January 08, 2003 11:26 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Passing Variables to Inline Views
Not a problem. Here you go. Thank you!!!
input $co_abbrev 'Input company abbrev'
input $eff_dt 'Input effective date, YYYYMMDD'
input $ga_numbers 'Input GA Nums'
input $feed_path 'Input report path'
|---------+-------------------------------------->
| | "Alexander, Steve" |
| | <Steve.Alexander@ci.sj.|
| | ca.us> |
| | |
| | Sent by: |
| | sqr-users-admin@sqrug.o|
| | rg |
| | |
| | |
| | |
| | 01/08/03 11:31 AM |
| | Please respond to |
| | sqr-users |
| | |
|---------+-------------------------------------->
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
|
|
| T
|
| To: "'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
|
| cc:
|
|
|
| bcc:
|
| Subject: RE: [sqr-users]
Passing Variables to Inline Views
|
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
Please show us the code in which you set the value of $ga_numbers.
-----Original Message-----
From: CARFREH1@Nationwide.com [mailto:CARFREH1@Nationwide.com]
Sent: Wednesday, January 08, 2003 7:51 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Passing Variables to Inline Views
Still not working...I will give you exactly what I am running and maybe
that will clarify (Please note that the variable $ga_numbers is being
passed in at execution ('aaa','bbb','ccc') ). If I put [ ] around the
variable $ga_numbers the SQR fails with an ORA-00921. It appears that SQL
cannot see the variable due to the open bracket. Thanks everyone!!!
SQL: select v.policy_number, v.product_code, pf.fund_code, f.fund_abbrev,
pf.fund_unit_balance, pf.fund_company, pf.fund_type, pf.fund_version,
v.annuitant_phone_number from expl_dly_funds f,
expl_dly_policy_funds
pf, (select p.policy_number, p.product_code, p.annuitant_phone_number
from expl_dly_policies p, agents_linked_expld ale where
p.policy_status_code between '1' and '5' and ale.ga_nbr in
And here is the block:
begin-select
v.policy_number &policy_number
v.product_code &product_code
pf.fund_code &fund_code
f.fund_abbrev &fund_abbrev
pf.fund_unit_balance &fund_units
pf.fund_company &fund_company
pf.fund_type &fund_type
pf.fund_version &fund_version
v.annuitant_phone_number &annuitant_phone_number
from expl_dly_funds f,
expl_dly_policy_funds pf,
(select p.policy_number, p.product_code, p.annuitant_phone_number
from expl_dly_policies p,
agents_linked_expld ale
where p.policy_status_code between '1' and '5'
and ale.ga_nbr in $ga_numbers
and p.writing_agent_number = ale.wa_nbr
union
select p2.policy_number, p2.product_code,
p2.annuitant_phone_number
from expl_dly_policies p2,
agents_linked_expld ale2,
df_legend_ann_phone_nums dl
where p2.policy_status_code between '1' and '5'
and p2.writing_agent_number = ale2.wa_nbr
and ale2.ga_nbr in ('ddd','eee')
and p2.annuitant_phone_number = dl.annuitant_phone_number) v
where pf.fund_company = f.fund_company
and pf.fund_code||'' = f.fund_code
and pf.fund_balance > 0
and v.policy_number = pf.policy_number
order by v.policy_number, pf.fund_code
end-select
James Womeldorf
<jwomeldo@fastenal.com> T
To:
"'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
Sent by: cc:
sqr-users-admin@sqrug.o
rg bcc:
Subject:
RE: [sqr-users] Passing Variables
to Inline Views
01/07/03 05:35 PM
Please respond to
sqr-users
I believe 'Dynamic SQL' is the answer here. I have not tested this, but I
think this will work.
The show statement should look like this:
$var1 = 'aaa','bbb'
let $Q = chr(39)
let $var1 = $Q || 'aaa' || $Q || ',' $Q || 'bbb' || $Q
show '$var1 = ' $var1
begin-select
.
.
.
from table_a a,
table_b b,
(select col1, col2, col3 col4
from table_c, table_d
where col5 in ([$var1])) c
-----Original Message-----
From: CARFREH1@nationwide.com [mailto:CARFREH1@nationwide.com]
Sent: Tuesday, January 07, 2003 4:11 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Passing Variables to Inline Views
Here is a problem I am running into right now:
begin-select
.
.
.
from table_a a,
table_b b,
(select col1, col2, col3 col4
from table_c, table_d
where col5 in $var1) c
where
.
.
.
This will not pass the var1 values ('aaa','bbb','ccc'...) into the inline
view properly as no data is returned. however if a single value is passed:
where col5 = $var1
It works fine.
I have tried messing with single quotes, [ and ] and [$where] clauses and
none have worked. Could really use some help at this point.
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users