Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Help on button Trigger for Query

    Hi to All,

    I have create create a Form with:

    1) Entries in an Header Block;
    2) An Execute Query Button in the Header Block;
    3) A Grid Block that must be populated from Execute Query Button.

    In the button trigger WHEN-BUTTON-PRESSED I have placed the code:

    Code:
    Declare
    LC$Req Varchar2(2000) ;
    AC$Req Varchar2(256);
    TD$Req Varchar2(256);
    TI$Req Varchar2(256);
    DT$Req Varchar2(256);
     
    Begin
    LC$Req := '';
    AC$Req := '';
    TD$Req := '';
    TI$Req := '';
    DT$Req := '';
     
    -- Value of Radio's in Header
    If :BLOCK_HEADER.ACCOUNT_SITUATION = 1 then
    TI$Req := ' ';
    Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 2 then
    TI$Req := ' AND (D_MYTABLE.DOC_PAID <> 0) ';
    Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 3 then
    TI$Req := ' AND (D_MYTABLE.DOC_PAID = 0) ';
    Else
    TI$Req := ' AND (D_MYTABLE.DOC_INS <> 0) ';
    END IF;
     
    -- Date Filter in Header
    If ((:BLOCK_HEADER.ACCOUNT_FROM_DATE is not null) AND (:BLOCK_HEADER.ACCOUNT_TO_DATE is not null)) then
    DT$Req := ' AND (D_MYTABLE.DOC_DATE >= :BLOCK_HEADER.ACCOUNT_FROM_DATE OR D_MYTABLE.DOC_DATE <= :BLOCK_HEADER.ACCOUNT_TO_DATE)';
    End if; 
     
    -- Account Type and Code in Header
    If ((:BLOCK_HEADER.TEXT_ANA_TYPE is not null) AND (:BLOCK_HEADER.TEXT_ANA_CODE is not null)) Then
    AC$Req := 'SELECT TYPE_CODE, DOC_CODE, DOC_DATE, DOC_TOTAL, DOC_INS, DOC_PAID, DOC_NOPAID FROM D_MYTABLE
    WHERE (D_MYTABLE.ANA_TYPE = :BLOCK_HEADER.TEXT_ANA_TYPE AND D_MYTABLE.ANA_CODE = :BLOCK_HEADER.TEXT_ANA_CODE)' ;
     
    -- Construction of Query String in order to populate the Balance Block
    LC$Req := RTRIM(AC$Req) || RTRIM(TI$Req) || RTRIM(DT$Req) ;
     
    Go_Block('BLOCK_BALANCE');
    Set_Block_Property( 'BLOCK_BALANCE', QUERY_DATA_SOURCE_NAME, LC$Req ) ;
    Execute_Query ;
    Go_Block('BLOCK_HEADER');
    End if;
    Go_Block('BLOCK_HEADER');
    End ;
    When I execute the Form and I Press on the Query Button, the error FRM-40505 is returned. I have searched the cause with Display Error in the menu bar. This SQL Instructions are execute:

    Code:
    SELECT TYPE_CODE, DOC_CODE, DOC_DATE, ANA_TYPE, ANA_CODE, DOC_TOTAL, DOC_INS, DOC_PAID, DOC_NOPAID
    FROM SELECT TYPE_CODE, DOC_CODE, DOC_DATE, DOC_TOTAL, DOC_INS, DOC_PAID, DOC_NOPAID FROM D_MYTABLE
    WHERE (D_MYTABLE.ANA_TYPE = :BLOCK_HEADER.TEXT_ANA_TYPE AND D_MYTABLE.ANA_CODE = :BLOCK_HEADER.TEXT_ANA_CODE)
    The SQL Query executed is different from the Query that I need to execute and that I have tried to write in the trigger.

    I suppose that I mistake something, but I am not able to correct the error without an help, because I am a newbie.

    I hope that someone can help me.

    Thank You and Best Regards
    Gaetano N.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that trigger result isn't the same you'd expect it to.

    You might create text/displlay item(s) and, in the WHEN-BUTTON-PRESSED trigger, display $req variables to make sure you did it correctly. After you figure out what is wrong, fix it and then execute query.

  3. #3
    Join Date
    Jan 2006
    Posts
    11
    Quote Originally Posted by Littlefoot
    It seems that trigger result isn't the same you'd expect it to.

    You might create text/displlay item(s) and, in the WHEN-BUTTON-PRESSED trigger, display $req variables to make sure you did it correctly. After you figure out what is wrong, fix it and then execute query.
    Ok, I have solved with this corrections, but I have another error :
    Code:
    Declare
    LC$Req Varchar2(2000) ;
    AC$Req Varchar2(256);
    TD$Req Varchar2(256);
    TI$Req Varchar2(256);
    DT$Req Varchar2(256);
     
    Begin
    LC$Req := '';
    AC$Req := '';
    TD$Req := '';
    TI$Req := '';
    DT$Req := '';
     
    -- Radio button Value in the Header block
    If :BLOCK_HEADER.ACCOUNT_SITUATION = 1 then
    TI$Req := ' ';
    Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 2 then
    TI$Req := ' AND DOC_PAID <> 0 ';
    Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 3 then
    TI$Req := ' AND DOC_PAID = 0 ';
    Else
    TI$Req := ' AND DOC_INS <> 0 ';
    END IF;
     
    -- Type of Doc in the Header Block
    If :BLOCK_HEADER.DOC_TYPE is not null then
    TD$Ref := ' AND TYPE_DOC = :BLOCK_HEADER.DOT_TYPE' ;
    End if;
     
    -- Date Filters in the Header Block
    If ((:BLOCK_HEADER.ACCOUNT_FROM_DATE is not null) AND (:BLOCK_HEADER.ACCOUNT_TO_DATE is not null)) then
    DT$Req := ' AND (DOC_DATE >= :BLOCK_HEADER.ACCOUNT_FROM_DATE OR DOC_DATE <= :BLOCK_HEADER.ACCOUNT_TO_DATE)';
    End if; 
     
    If ((:BLOCK_HEADER.TEXT_ANA_TYPE is not null) AND (:BLOCK_HEADER.TEXT_ANA_CODE is not null)) Then
    AC$Req := ' D_MYTABLE WHERE ANA_TYPE = :BLOCK_HEADER.TEXT_ANA_TYPE AND ANA_CODE = :BLOCK_HEADER.TEXT_ANA_CODE' ;
    -- Set SQL Statement
    LC$Req := RTRIM(AC$Req) || RTRIM(TD$Req) || RTRIM(TI$Req) || RTRIM(DT$Req) ;
    Go_Block('BLOCK_BALANCE');
    Set_Block_Property( 'BLOCK_BALANCE', QUERY_DATA_SOURCE_NAME, LC$Req ) ;
    Execute_Query ;
    Go_Block('BLOCK_HEADER');
    End if;
    Go_Block('BLOCK_HEADER');
    End ;
    With This correction the SQL string is e.g.
    Code:
    SELECT TYPE_CODE, DOC_CODE, DOC_DATE, ANA_TYPE, ANA_CODE, DOC_TOTAL, DOC_INS, DOC_PAID, DOC_NOPAID
    FROM  D_MYTABLE WHERE ANA_TYPE = :BLOCK_HEADER.TEXT_ANA_TYPE AND ANA_CODE = :BLOCK_HEADER.TEXT_ANA_CODE AND TYPE_CODE = :BLOCK_HEADER.CODE_TYPE
    This is correct, but when I run the Form and press the Query button, the error ORA-01008 is returned.

    How can resolve this problem ?

    Best Regards
    Gaetano N.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What the error is refering to is that one of your :block_header bind variables does not exist.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •