hello friends
i m trying to execute following code in "sqlplus" to make a function.
create or replace function GETADVANCESEARCHRESULT
(
keyword in varchar, pkgtype in varchar, catname in varchar, datefrom in date, todate in date
)
return packagesinfo.packagechildinfo_refcur
as
advancesearchresult packagesinfo.packagechildinfo_refcur;
basequery varchar(100);
condition1 varchar(100);
condition2 varchar(100);
condition3 varchar(100);
condition4 varchar(100);
condition5 varchar(100);
condition6 varchar(100);
begin
basequery := 'select
distinct(tblpackage.packageid), tblpackage.packagename
from
tblpackage, tblpackageattributes,
tblpackagetype, tblcategory, tblpackagecategory
where ';
condition1 := '( tblpackage.packagename like keyword or
(
tblpackageattributes.attributename in
('+ 'Description'+','+'System Requirements'+') and
tblpackageattributes.attributevalue like keyword
)
and
tblpackage.packageid = tblpackageattributes.packageid ';
condition2 := '( tblcategory.categoryname like catname or
tblcategory.categorydescription like catname ) and
tblpackagecategory.categoryid = tblcategory.categoryid and
tblpackagecategory.packageid = tblpackage.packageid ';
condition3 := '( tblpackagetype.packagetype like pkgtype or
tblpackagetype.packagetypedesc like pkgtype) and
tblpackagetype.packagetypeid = tblpackage.packageid ';
condition4 := ' tblpackageattributes.createdon
date between datefrom to todate ';
condition5 := ' tblpackageattributes.createdon >= datefrom ';
condition6 := ' tblpackageattributes.createdon <= todate ';
open advancesearchresult for
( if( pkgtype != null ) then
basequery := basequery + '(' + condition3 + ')';
end if;
if( catname != null) then
if( pkgtype != null ) then
basequery := basequery + 'or' + '(' + condition2 + ')';
else
basequery := basequery + '(' + condition2 + ')';
end if;
end if;
if ( datefrom != null ) then
if ( todate != null ) then
if ( pkgtype != null or catname != null ) then
basequery := basequery + 'and' + '(' + condition4 + ')';
end if;
else
basequery := basequery + 'and' + '(' + condition6 + ')';
end if;
else
basequery := basequery + 'and' + '(' + condition5 + ')';
end if;
end if;
if ( keyword != null ) then
if( datefrom != null) then
if( todate != null ) then
basequery := basequery + '(' + condition1 + ')' +
'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition4 + ')';
else
basequery := basequery + '(' + condition1 + ')'
+ 'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition5 + ')';
end if;
elseif (datefrom = null and todate != null )
basequery := basequery + '(' + condition1 + ')'
+ 'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition6 + ')';
end if;
end if;
end if;
but this is giving following error. i know there is some syntax error but i m unable to understand the error please someone help me. its urgent... thanks in advance
Errors for FUNCTION GETADVANCESEARCHRESULT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
42/5 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
63/5 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
LINE/COL ERROR
-------- -----------------------------------------------------------------
; <an identifier> <a double-quoted delimited-identifier>
delete exists prior <a single-quoted SQL string>
