Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

Default_Where clause question

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
geeta_m9
Asked:
geeta_m9
2 Solutions
 
sathyagiriCommented:
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
 
jwahlCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now