Re: Changing the WHERE/ORDER BY clause in SQL*FORMS

From: Rick Wright <wrighri_at_jacobs.CS.ORST.EDU>
Date: 17 Apr 92 03:56:51 GMT
Message-ID: <1992Apr17.035651.11891_at_CS.ORST.EDU>

In article <fragante.703209729_at_chilko.ucs.ubc.ca> fragante_at_unixg.ubc.ca (Gv Fragante) writes:

>My question now is, has anyone used the pound (#) operator to dynamically
>change the default WHERE/ORDER BY clause of FORMS ? There are examples given
>in the documentation "Advanced SQL*FORMS technique", but I cannot understand
>how it works.

When SQL*FORMS executes a query it normally builds a sql query based on fields that have data entered into them. If it finds a field that begins with a pound sign (#) it will take everything that follows the pound sign as a literal string that is placed into the sql query. Since the "order by" clause always follows the "where" clause any text that will modify the order by should be placed in the last database field in the block. That text will be appended to the end of any other text that is generated by FORMS. You can try this out in an executing form by simply typing it into the field before pressing [EXECUTE QUERY]. After you try that out you can modify the PRE-QUERY trigger on the block you want to order. The last field in the block should have a query length of 240 to insure that you will have plenty of room to insert your order by clause. Don't forget to consider the case where the operator enters query criteria into the last field. Your will need to format your text in the PRE-QUERY trigger before inserting it into the last field. It will be a trial and error learning experience but you can see the whole sql statement that is generated by pressing [DISPLAY ERROR] after an error occurs on the query. Good luck.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

|                               |                                             |
|      Rick Wright              |    Internet: wrighri_at_jacobs.cs.orst.edu     |
|      Systems Developer        |                                             |
|      Teledyne Wah Chang       |    Phone:    (503) 926-4211   ext. 6294     |
|      Albany, Oregon           |                                             |
|                               |                                             |

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Received on Fri Apr 17 1992 - 05:56:51 CEST