Home » Developer & Programmer » Forms » Query Problem
Query Problem [message #339689] Fri, 08 August 2008 02:27 Go to next message
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.
Re: Query Problem [message #339707 is a reply to message #339689] Fri, 08 August 2008 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Varying elements in the IN list" might be a term you should search for.
Re: Query Problem [message #339751 is a reply to message #339689] Fri, 08 August 2008 06:31 Go to previous messageGo to next message
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.
Re: Query Problem [message #339758 is a reply to message #339751] Fri, 08 August 2008 06:48 Go to previous messageGo to next message
Littlefoot
Messages: 20892
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>
                                     );
Re: Query Problem [message #339860 is a reply to message #339689] Sat, 09 August 2008 00:47 Go to previous message
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.
Previous Topic: DISABLE COMMIT ON database BLOCK
Next Topic: LOV Item
Goto Forum:
  


Current Time: Mon Dec 05 03:12:50 CST 2016

Total time taken to generate the page: 0.11058 seconds