[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
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
_______________________________________________
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