Re: Dynamic "where" clause in query in Sql*forms 3.0

p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:

>Daryl Erwin (derwin_at_mach2.wlu.ca) wrote:
>> In article <1993Jun7.163709.15957_at_stortek.com> v045100_at_otis1.UUCP (Jennifer Farnham) writes:
>> >
>> >
>> >I want to do this:
>> >
>> > select field1, field2, field3
>> > into :display1, :display2, :display3
>> > from real_table
>> > where :huge_field_on_screen;
 [...]
>> I believe you can prepend your query with '#' as the first character. THis
>> will result in your where clause becoming the default where clause for
>> the query.
 

>Basically right, but I *think* what happens is that your where clause
>becomes:
> where huge_field_on_screen <whatever follows the # symbol>
 

>> If you dont want the user to see this you can of course, copy it to anther
>> field. The field MUST be base table field as far as I know.
>Yes, it must be a base table field.

Depending on the platform. On the my PC it doesn't show but the same form on a Mac shows the altered value. (I alter it in a Pre-query trigger)

>> This is from "advanced" sqlforms book and I have tried to apply its use to
>> your situation.
 It's also used by CASE tools to implement foreign keys, with such code as
> :global.dummy := '#IN (SELECT PER_ID FROM PEOPLE WHERE SURNAME LIKE '''||
> :ITEM.DSP_SURNAME || '%'')'
> copy(global.dummy, 'ITEM.PER_ID');
 

>Assuming that you have a block called item (to record data on items!), and
>this has a foreign key (PER_ID) to the PEOPLE TABLE. The block also
>contains a NON-database field (DSP_SURNAME) to indicate the surname of the
>related person.
> The where clause finally executed (if you put eg. SM in the DSP_SURNAME)
>field is:
> where PER_ID IN (SELECT PER_ID FROM PEOPLE WHERE SURNAME LIKE 'SM%')
 

>Note the use of global.dummy (which is a string variable), and COPY to
>get around the type checking which will prevent you putting a string
>value into a numeric field.

>Since the field must be a base table field, what we do if we need to add a
>very strange clause is
> some_base_table_field := '#some_base_table_field and <more where clause>'
>so we end up with
> where some_base_table_field=some_base_table_field and <more where clause>
> ^^^^^^^^always true, of course^^^^^^^^^^^^^

WARNING! This is not always true! If some_base_table_field can be null this is not true.
I normally do something like (assuming a scenario like the one 10 lines above with surname and per_id):

if :item.dsp_surname is null then
  copy(null, 'item.per_id');
else
  copy('# is not null) and ( <more where clause>', 'item.per_id'); end if;

Yes - these ^ and ^ parenthesis are for real. The query forms build and send to the RDBMS put a pair of parenthesis around the generated part-statement. Now you can put 'or's in the <more where clause> without presedence misinterpretation (sp?).

Remember the previously discussed use of 'exists' instead of 'in': If the query is likely to return many rows from item or the subquery is likely to return many (unnessercary) per_id from people or another part of the query effectivly reduces the number of rows returned then <more where clause> should (maybe) look like this:  

 exists (select 'x' from people where per_id=item.perid and surname like ...)

                                             ^^^^ this refers to the table not

Hope this helps!

>> --
>> Daryl Erwin Net Address: derwin_at_mach2.WLU.CA
>> Information Systems Surface Mail: P4-1, Main Campus
>> Wilfrid Laurier University, Waterloo, Ontario
>> Bus:(519) 884-1970 x2910 !edisni deppart m'I pleH
 

>--
> _________________________ __________________________________________
> / Tommy Wareing \ / In the beginning, there was The Bomb \
>| p0070621_at_uk.ac.brookes X And The Bomb said "Let there be Light!" |
> \ 0865-483389 / \ - The Bomb, Dark Star /
> ~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Received on Fri Jun 11 1993 - 15:20:13 CEST