Re: Problem with Dynamic Where Clause

["Manning, Shinta" <shintamanning@NFISG.COM>]

At this point I think I'll just do this:
  SELECT ... FROM .... WHERE [$whereClause]

cause that seems to work just fine.  How many potential where clauses???? Oh
boy!  That's a scary thought actually.... And the thing is it's randomly
used.

> -----Original Message-----
> From: David Donnelly [SMTP:Dave@ISISBIO.COM]
> Sent: Tuesday, July 20, 1999 12:32 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Problem with Dynamic Where Clause
>
> Pankaj is correct; another way to put it is that you can't mix
> substitution variables and dynamic SQL.  You're trying to do this:
>
>  select ... from .... [$whereclause]
>
> but the $whereclause contains substitution variable.   The problem is that
> dynamic SQL is evaluated only once, at compile time, and then passed as a
> literal text to SQL.  With substitution variables, a different thing
> happens: SQL is given a pointer to a variable and told to check it each
> time it needs it.
>
> About the only way to do this is to construct the where clause each time
> it changes.  But perhaps there are better solutions.  If you give us more
> information, like how many potential where-clauses you have (is it only 3
> or really more like 300?), and their distribution (are they pretty much
> equal and random, or is one of them used 99% of the time?), you will
> probably get some clever ideas.
>
> Dave
>
> At 09:21 AM 7/20/1999 -0500, you wrote:
> >I am storing all potential where clauses in the database.  So, I have a
> >table, call it where_clause_table, and it has two columns: id,
> where_clause.
> >Sample entries would be:
> >
> >      1234, 'WHERE customer_account_number = $gCustomerID AND
> customer_name
> >= $gCustomerName'
> >      4567, 'WHERE product_id = $gProductID AND product_price >=
> $gMinPrice
> >AND product_price <= $gMaxPrice'
> >      4254, 'WHERE state_code = 'CO'
> >
> >If my program selects the last where clause, the program runs well. Came
> >back with all customers who lives in CO.  But if it selects any of the
> first
> >and the last, I get an error:
> >
> >                ORA-00911: invalid character
> >
> >And I think it doesn't like the '$' character. Is there an escape
> character
> >that I can use to tell SQR or ORACLE that I really want the '$' in my
> >string?  In C++, the escape character, I think, is '\'.
> >
> >Thank you in advance.
> >