Hi All
I am getting the following error while executing this function.

CREATE FUNCTION SABA_REPORT.SCF_OVERVIEWFORTRAININGADMIN_ASSIGN_CO ST222
(@plan varchar(20) ,
@manager varCHAR(20),
@currentUser varCHAR(20),
@organization varchar(25) ,
@javaLocale VARCHAR(25) )
RETURNS float
LANGUAGE SQL
READS SQL
DATA format:
BEGIN ATOMIC
declare @n_cost float;
declare @n_cost1 float;
declare @n_cost2 float;
declare @@currency char(20);

set @@currency=( select acurr.currency_id from scv_acc_party_account pacct
inner join tpv_pub_account_currency acurr on pacct.ACCOUNT_ID=acurr.account_id
inner join cmv_pub_smp_person p on p.company_id=pacct.PARTY_ID
where p.id=@currentUser and left(acurr.flags,1)='1');
set @n_cost =0.00;

set @n_cost1=( select
coalesce(scf_off_cost222(ot.id,pl.assignee_id,@cur rentUser, @javaLocale),0) cost
from
scv_plan pl
inner join cmv_pub_plan_activity plact on pl.id=plact.plan_id
inner join scv_cmt_activity act on act.id=plact.activity_id
inner join TPV_PUB_I18N_SMP_OFFERING_TEMP ot on act.act_defn_id=ot.id
inner join fgv_sys_list_of_val lov on act.srcbl_status=lov.name
and lov.list_id =( case when act.srcbl_status='100' then 'sysli000000000000173'else 'sysli000000000000163' end)
inner join cmv_pub_smp_person p3 on pl.assignee_id=p3.id
inner join tpv_pub_all_org cmp on p3.company_id=cmp.id
inner join cmv_pub_smp_person p4 on act.source_id=p4.id
where
lov.locale_id=ot.locale_id
and ot.locale_id=@javaLocale
and ( cast(p3.terminated_on as date) > current_date or p3.terminated_on is null )
and lov.description not in ('Rejected','On Hold')
and pl.status not in ('600','400')
and pl.plan_form_id=@plan
and cmp.id=@organization
and coalesce(p3.manager_id,'abc') like @manager ||'%' ******I think This is a problem area*****
);

set @n_cost = @n_cost1;
return @n_cost;
end format


While executing this function i am getting following error.

DB2 SQL error: SQLCODE: -132, SQLSTATE: 42824, SQLERRMC: null
Message: A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string. A LOCATE or POSITION scalar function is not valid because the first operand is not a string or the second operand is not a string expression.
Line: 24 



Regards
Vishal

Just have a look at the long description of SQL0132. It will tell you that the 2nd operand of a LIKE predicate must be constant and not based on a variable.

I suggest that you change your statement to use "SUBSTR(p3.manager_id, 1, LENGTH(@manager)) = @manager" (plus coalesce, plus better length handling). That will get rid of the LIKE altogether.

Hey thanks for reply.

( @manager) parameter is optional. So if we pass the value to parameter then it will pull data for that manager only.
otherwise it will pull data for all.

Regards
Vishal

So why don't you use a CASE expression like this:
Or use SUBSTR() in the ELSE branch if you don't want to check for exact equality of the manager name.

Hi Knut,
Could you please merge your case code in my function???
Because i have tried it and its not working.......i think i am doing something wrong.....


Regards
Vishal

My bad... Try this:
You could even simplify it to this:
If "@manager" is set (i.e. not NULL), it will compare "@manager" with "p3.manager_id". Otherwise, (@manager is null), it will compare "p3.manager_id" with "p3.manager_id", which is always true, so the predicate is ignored (unless p3.manager_id would be NULL itself).

Hi Knut,
Hats Of You Man!!!!!!!!
Its working now.
Thank you very very much.......
Take Care..



Regards
Vishal