Solved

Default_Where clause question

Posted on 2006-06-27
2
676 Views
Last Modified: 2007-12-19
In my form, I have two different data blocks. The first is the key block followed by block A. In the key block, depending on the value entered in the Prior_Years textbox by the user, I would like to change the where clause for block A. I have written the new where clause in the when-validate-item trigger for the Prior_Years textbox. The new where clause is as follows:

v_where := '
A_CODE = B_RSTS_CODE
    AND A_INCL_SECT_ENRL = 'Y'
    AND B_TERM_CODE BETWEEN TO_CHAR(TO_NUMBER(:KEY_BLOCK.TERM_CODE)-num_years)
    AND :KEY_BLOCK.TERM_CODE AND B_ID BETWEEN NVL(:KEY_BLOCK.START_ID, 0) AND NVL(:KEY_BLOCK.END_PIDM, 99999999)
    MINUS
    SELECT C_ID FROM C WHERE   C_XREF_CODE = 'PAR')    AND EXISTS (
    SELECT 'X' FROM D
    WHERE D_ID = E_ID
    AND D_ATYP_CODE = E_ATYP_CODE
    AND D_STATUS_IND IS NULL
    )
    AND LIB.BIO.AGE(E_ID) <= :KEY_BLOCK.AGE_LIMIT
    and E_relt_code in ('M','F')'

I want this new where clause to take effect once the user presses the PgDn button, or moves to the next block. Where should I set the block property? Should it be in the pre-block trigger in block A or the post-block trigger for the key block?  If the user does not enter a value in the Year_Range textbox, then the default where clause stays. Can I leave the default where clause in the property palette in block A? Lastly, how do I properly quote the where clause statement since there are already single quotes in the query?
0
Comment
Question by:geeta_m9
2 Comments
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 100 total points
ID: 16996748
I think you could set the block property in the post - block trigger for the key block.

Also regarding the quote, you could use two double quotes to negate the problem

v_where := '
A_CODE = B_RSTS_CODE
    AND A_INCL_SECT_ENRL = ''Y''
    AND B_TERM_CODE BETWEEN TO_CHAR(TO_NUMBER(:KEY_BLOCK.TERM_CODE)-num_years)
    AND :KEY_BLOCK.TERM_CODE AND B_ID BETWEEN NVL(:KEY_BLOCK.START_ID, 0) AND NVL(:KEY_BLOCK.END_PIDM, 99999999)
    MINUS
    SELECT C_ID FROM C WHERE   C_XREF_CODE = ''PAR'')    AND EXISTS (
    SELECT ''X'' FROM D
    WHERE D_ID = E_ID
    AND D_ATYP_CODE = E_ATYP_CODE
    AND D_STATUS_IND IS NULL
    )
    AND LIB.BIO.AGE(E_ID) <= :KEY_BLOCK.AGE_LIMIT
    and E_relt_code in (''M'',''F'')'
0
 
LVL 12

Accepted Solution

by:
jwahl earned 100 total points
ID: 16998796
when do you execute the query for block A?

in my opinion the query always depends on entries in key block, so you only have to set the DEFAULT_WHERE condition in the PRE-QUERY trigger of block A.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question