Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    12

    Unanswered: Getting error while executing this function

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2009
    Posts
    12
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So why don't you use a CASE expression like this:
    Code:
    CASE
       WHEN @manager IS NULL
       THEN 1
       ELSE p3.manager_id = @manager
    END
    Or use SUBSTR() in the ELSE branch if you don't want to check for exact equality of the manager name.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2009
    Posts
    12
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My bad... Try this:
    Code:
    CASE
       WHEN @manager IS NULL
       THEN p3.manager_id
       ELSE @manager
    END = p3.manager_id
    You could even simplify it to this:
    Code:
    COALESCE(@manager, p3.manager_id) = p3.manager_id
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2009
    Posts
    12

    Thumbs up

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



    Regards
    Vishal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •