OraFAQ Forum: Forms » Query Problem

Home » Developer & Programmer » Forms » Query Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Problem [message #339689]

Fri, 08 August 2008 02:27

alammas
Messages: 46
Registered: July 2008

Member

Hi,
Have a Look.
--
Declare
Cursor Curlist(ID VARCHAR2) is select AssetID,AssetName
from FixedAssetD where AssetID IN (ID);
WhereListVar VARCHAR2(5000):=:GLOBAL.WhereList;
BEGIN
FOR GRlist IN CURLIST(WhereListVar) LOOP
:SolddFMB.AssetID:=Glist.AssetID;
:SolddFMB.AssetName:=Glist.ASSETINFONAME;
End Loop;
end;
--
when :GLOBAL.WhereList contains one id cursor works properly but more than one id return no row, any problem here Thanx.

Report message to a moderator

Re: Query Problem [message #339707 is a reply to message #339689]

Fri, 08 August 2008 03:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"Varying elements in the IN list" might be a term you should search for.

Report message to a moderator

Re: Query Problem [message #339751 is a reply to message #339689]

Fri, 08 August 2008 06:31

alammas
Messages: 46
Registered: July 2008

Member

I think That was due to bind variable or single variable, so how can i do this alternatively.

Report message to a moderator

Re: Query Problem [message #339758 is a reply to message #339751]

Fri, 08 August 2008 06:48

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you don't like my previous post (by the way, did you - at least - search for what I've suggested? Which pages did you visit?), you might store the IN list values into a table and use them in the cursor declaration.

It would then look like

Cursor Curlist is 
select AssetID, AssetName 
  from FixedAssetD where AssetID IN (select ID from new_table
                                     where <some condition which will let you select desired IDs>
                                     );

Report message to a moderator

Re: Query Problem [message #339860 is a reply to message #339689]

Sat, 09 August 2008 00:47

alammas
Messages: 46
Registered: July 2008

Member

That i Searches..
create or replace type myTableType as table of number;
create or replace function str2tbl( p_str in varchar2 ) return myTableType
as
l_str long default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
select A.AssetID,A.AssetNAme from FixedAssetD A
where A.AssetID ( select *
from THE ( select cast( str2tbl( ':Global.Trans' ) as mytableType ) from dual ) );

Thanx Little_foot.

Report message to a moderator

Previous Topic:	DISABLE COMMIT ON database BLOCK
Next Topic:	LOV Item

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 00:31:46 CST 2025