Solved

Default_Where clause question

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creation date for a PDB 5 87
format dd/mm/yyyy parameter 16 60
date show only hh:mm 2 50
UTL_FILE invalid file operation 5 60
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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