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

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;
> >
> >
> >in sql*forms3.0.
> >
> >Can this be done? huge_field_on_screen will vary of course as to what
> > the criteria wil be within this field....
 

> Since I couldn't mail you, I will risk embarassment :-)
Since I read this, I'll risk embarassment by posting a followup :-)

> 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.

> 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 || '%'')'

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^^^^^^^^^^^^^


> -- 

> 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                /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~