Solved

Default_Where clause question

Posted on 2006-06-27
2
679 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

630 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