Solved

Default_Where clause question

Posted on 2006-06-27
2
671 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now