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)
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?