This discussion is archived
1 Reply Latest reply: May 7, 2013 12:46 AM by FrançoisDegrelle RSS

Problems with query on a non-database field

1007378 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points