1 Reply Latest reply: May 7, 2013 2:46 AM by François Degrelle RSS

    Problems with query on a non-database field

    1007378
      Hi,

      I have some problems. I wanna query a non-database field but it does not work for one of them. I have tried the following...

      declare

      --

      vsNewDefaultWhere varchar2(4000);

      vbFirstWhere boolean;

      --

      cursor curArea is

      select a1_area, a1_hbno, a1_seqno

      from b1_in, c1_hdr, a1_bl

      where b1_sequenceno = a1_seqno

      and b1_shipid = a1_hbno

      and b1_id = :c1_shipid;

      rowArea curArea%ROWTYPE;

      --

      procedure mergeStrings (sNewWhereClause varchar2) is

      begin

      if not vbFirstWhere then

      vsNewDefaultWhere := vsNewDefaultWhere || ' and ';

      end if;

      vsNewDefaultWhere := vsNewDefaultWhere || sNewWhereClause;

      vbFirstWhere := false;

      end mergeStrings;

      --

      begin

      --

      navigate.resetselrows('IMP_HDR');

      navigate.resetselrows('IMP_DTL');

      --

      if not navigate.customwhereclause then

      navigate.resetwhereclause('IMP_HDR');

      end if;

      --

      vsNewDefaultWhere := Get_Block_Property('IMP_HDR', DEFAULT_WHERE);

      vbFirstWhere := vsNewDefaultWhere is null;

      --
      ---- does work!

      if :IMP_HDR.c1_usecode is not null then

      mergeStrings('c1_shipid in (select b1_id from b1_in where b1_fac = ''' || :global.fac || ''' and b1_comp = ''' || :global.comp || ''' and b1_code like ''' || :c1_usecode || ''')');

      end if;

      --

      ---- does not work!
      open curArea;

      fetch curArea into rowArea;

      if curArea%found then

      if :IMP_HDR.c1_area is not null then

      mergeStrings('c1_shipid in (select b1_id from b1_in, c1_hdr, a1_bl where b1_fac = ''' || :global.fac || ''' and b1_comp = ''' || :global.comp || ''' and b1_sequenceno = ''' || rowArea.a1_seqno || ''' and b1_shipid = ''' || rowArea.a1_hbno || ''' and rowArea.a1_area like ''' || :c1_area || ''')');

      end if;

      --
      end if;

      end;

      Thanks for your help in advance and let me know if you Need more Information.

      Edited by: 1004375 on 06.05.2013 12:18

      Edited by: 1004375 on 06.05.2013 12:30