On Mon, 29 Sep 2003 13:49:32 -0400 "Rozengurtel, Daniel"
<[EMAIL PROTECTED]> wrote:
> I have a quick question:
> Can I use a 'prepare' statement to execute later a "CREATE OR REPLACE
> VIEW..." with passing to it a specific param? I.e. something like
> that:
I'm pretty sure you can't use bind variables in a CREATE VIEW
statement. You can confirm that by creating a bind variable 'p1' in
SQL*Plus and trying to execute the statement there.
> my $sth_CreateEquityIDsView = $dbh->prepare(q{
> CREATE VIEW IDS_COUNT_VIEW AS (
> SELECT ISID.ID_CTXT_TYP, ISS_ID
> FROM FT_T_ISID ISID, FT_T_ISSU ISSU
> WHERE ISSU.ISS_TYP=:p1
> AND ISSU.INSTR_ID=ISID.INSTR_ID
> AND ISID.END_TMS IS NULL
> AND ISSU.END_TMS IS NULL
> union
> SELECT FRIP.PREF_ID_CTXT_TYP, ISID_ISS_ID
> FROM FT_T_FRIP FRIP, FT_T_ISSU ISSU
> WHERE ISSU.ISS_TYP=:p1
> AND ISSU.INSTR_ID=FRIP.INSTR_ID
> AND FRIP.PRT_PURP_TYP<>'PRICES'
> AND FRIP.END_TMS IS NULL
> AND ISSU.END_TMS IS NULL
> )}) || die $dbh->errstr;
It's easier to make sense of the SQL if you start the FROM clause on a
new line.
> $sth_CreateEquityIDsView->bind_param(1, $iss_typ, {TYPE=>SQL_CHAR});
> # placeholders are numbered from 1
Since you have explicitly named the bind variable, you could use 'p1'
instead of 1 in the bind_param() call.
> If it cannot be done this way, what do you think is the best approach
> to achive what I am trying to?
It depends on what you are trying to do. You could include
issu.iss_typ in the SELECT clauses of the UNION and include it in the
WHERE clause when you SELECT from the view.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
