Re: Problem with Dynamic Where Clause

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

When my team was thinking about dynamic select statement, I started off
working on the where clause by creating a table of all the constraint
element (customer name, id etc...) and my program will build the where
clause itself.  Our programs will have a very basic condition (in other
words, all our programs have the reporting time period keyed in as the basic
where-clause), so my program will build the additional part of the
where-clause, if needed, based on the constraint table.  So my program basic
select statement looked like:
***********
   begin-select
     :
     :
   from tableA
   where reporting_period >= $gStartDate and reporting_period <= $gEndDate
[$whereClause]
*************
$gStartDate and $gEndDate are dates passed in as parameters, and the
$whereClause = additional conditions.  SQR seems to like this way.  So, I am
currently attempting to store the whole where clause in the database so that
my code would look like:

***************
    begin-select
     :
     :
    from tableA
    [$whereClause]
    end-select
****************

Ideally, I want my program to look like:
************************
   begin-program
     do getDetails
   end-program

  begin-procedure getDetails
      do getSelectStatement($selectStatement)

 [$selectStatement]
  end-procedure

 begin-procedure getSelectStatement (:$selectStatement)
   :
   :
   :
   LET $selectStatement = .......

end-procedure


*********************
Less code, less headache....

I'll try to implement your suggestions below ... I would be more than
interested to read any tips on how to implement dynamic select statement
that you were talking about in your email cause that's ultimately where I
want to be able to implement

 Thanks a bunch Tony


> -----Original Message-----
> From: Tony DeLia [SMTP:tdelia@EROLS.COM]
> Sent: Tuesday, July 20, 1999 1:26 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Problem with Dynamic Where Clause
>
> Shinta,
>    You're stored Where clause table is an interesting concept...
> PeopleSoft COBOL uses a similar method extensively (but stores the
> entire SQL statement)... here's a couple suggestions you may consider to
> implement this...
>
> A)
> Remove the 'WHERE' from the statement text... this is redundant and
> prevents you from appending the statement to existing where clause
> criteria... you can specify the operator in your program
> (WHERE,AND,OR...)...
>
> B)
> Create a bind variable designator in your where clause... such as...
> (example)...
> 'product_id = :C and product_price >= :N'
>
> Use a code-sequence that suits doesn't conflict with your database...
>
> C)
> Create a simple SQR routine to resolve the bind variables with passed
> parameters...
>
> do Bind-SQL($where_clause, $gProductID, $gMinPrice, etc....)
>
> Your routine can then substitute $gProductID for :C and $gMinPrice for
> :N. I used :C to designate a character bind variable so quotes will be
> created... The :N is numeric and will insert only the value (no
> quotes)... perform datatype conversion as fit...
>
> NOTE: Bind variables must be resolved in your dynamic SQL... you cannot
> pass the variable name (i.e. $gProductID) within the SQL text...
>
> D)
> You can then use the statement as follows...
>
> begin-select
> ...
>  from some_table
> where some_status = 'A'
>   AND [$where_clause]          <=== not restricted by WHERE keyword
>
> end-select
>
> E)
> In the example above if $where_clause is null set it to '1 = 1' so there
> will be no compilation errors...
>
> F)
> For extra credit add a table alias assignment character in your
> string...
>
> '#.product_id = :C and #.product_price >= :N'
>
>
> Have your Bind-SQL routine translate the table alias as well...
>
> do Bind-SQL($where_clause, '#.', 'A.', $gProductID, $gMinPrice, etc...)
>
> The routine can substitute 'A.' for '#.'
> If you don't want an alias pass a null value to replace the '#.' and it
> will be removed...
>
> Result...
>
> A.product_id = 'XYZ' and A.product_price = 100
>
> Result in SQL...
>
> begin-select
> ...
>  from some_table      A,
>       other_table     O
> where A.key         = O.key
>   AND A.some_status = 'A'
>   AND [$where_clause]
>
> end-select
>
>
>   AND A.product_id = 'XYZ' and A.product_price = 100   <===
> [$where_clause]
>
> By adding alias substitution you've removed ambiguity in your
> statements... they're now much more flexible...
>
> These are just a few suggestions... there's alot of slick ways to
> incorporate dynamic SQL into your programs...
>
>                     Tony DeLia
>
> PS - I wrote a simple routine to replace aliases in a dynamic string...
> I'll post this when I get a chance (on the road at the moment)... I
> specifically created this so I could re-use the [$SlctCalendar] string
> in PeopleSoft Payroll... i.e. changing original alias (A.) to another
> alias... this allows me to use the same criteria in multiple locations
> of the program...
>
> Thanks to everyone who offered positive feedback and suggestions for my
> site... (www.sqrtools.com)... this is a great network...
>
>
> Manning, Shinta 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.
>
> --
> Tony DeLia
> AnswerThink Consulting Group
> PeopleSoft Solutions Practice - Delphi Partners
> tdelia@erols.com
> http://www.sqrtools.com