#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0

    Problem with looping SP


    Hi all,
    I am new to this forum and am experiencing problems with a stored procedure I tried to implement.

    I am also new to programming SPs but fairly firm in SQL in general.

    I tried to create a looping interval of some files we have at certain points of time. I have a SP that calculates the sums of different costs in this file at certain points of time. What I do need now is a loop that goes back over the lifetime of this file on a quarterly basis until the beginning of this file.
    I think in theory the structure is this. I copied the body of my SP only.

    create or alter procedure TRIANGULATION (
    FILEID integer)
    returns (
    FILEIDTRIA integer,
    INCURREDUSDTRIA numeric(18,2),
    PAIDUSDTRIA numeric(18,2),
    OSUSDTRIA numeric(18,2))
    as
    declare variable QUARTER date;
    declare variable I integer;
    declare variable FILE_ID integer;
    declare variable ENTRY_ID integer;
    declare variable CLAIMBEGIN date;
    declare variable QUARTERDATEBEGIN varchar(27);
    declare variable CLAIMEND date;
    declare variable QUARTERDATEEND varchar(27);
    BEGIN
    FOR
    select
    s2.file_id, s2.entry_id,
    s2.CLAIMBEGIN,
    case
    when QUARTERCLAIMBEGIN = 1 then '01.04.' || extract(year from s2.CLAIMBEGIN)
    when QUARTERCLAIMBEGIN = 2 then '01.07.' || extract(year from s2.CLAIMBEGIN)
    when QUARTERCLAIMBEGIN = 3 then '01.10.' || extract(year from s2.CLAIMBEGIN)
    when QUARTERCLAIMBEGIN = 4 then '01.01.' || (extract(year from s2.CLAIMBEGIN)+1)
    end as QUARTERDATEBEGIN,
    s2.CLAIMEND,
    case
    when QUARTERCLAIMEND = 1 then '01.04.' || extract(year from s2.CLAIMEND)
    when QUARTERCLAIMEND = 2 then '01.07.' || extract(year from s2.CLAIMEND)
    when QUARTERCLAIMEND = 3 then '01.10.' || extract(year from s2.CLAIMEND)
    when QUARTERCLAIMEND = 4 then '01.01.' || (extract(year from s2.CLAIMEND)+1)
    end as QUARTERDATEEND
    from
    (select
    s1.file_id, ve.entry_id,
    s1.CLAIMBEGIN,
    ceil(cast(extract(month from s1.CLAIMBEGIN) as numeric(18,2))/3) as QUARTERCLAIMBEGIN,
    s1.CLAIMEND,
    ceil(cast(extract(month from s1.CLAIMEND) as numeric(18,2))/3) as QUARTERCLAIMEND
    from
    (select f.file_id, f.policy_id, f.vessel_no,
    (select first 1 a.action_date from actions a
    where a.file_id = f.file_id
    order by a.action_date desc) as CLAIMBEGIN,
    (select first 1 a.action_date from actions a
    where a.file_id = f.file_id
    order by a.action_date asc) as CLAIMEND
    from files f
    where f.company in ('HPI-EU','OCT','TRITON','HDP')
    and f.file_id = :FILEID
    ) s1

    left join pool_vesselentries ve
    on s1.policy_id = ve.policy_id
    and s1.vessel_no = ve.vessel_no
    and ve.entry_type = 'PREMIUM') s2
    INTO :FILE_ID,
    :ENTRY_ID,
    :CLAIMBEGIN,
    :QUARTERDATEBEGIN,
    :CLAIMEND,
    :QUARTERDATEEND
    DO
    BEGIN
    I= 0;
    Quarter = dateadd(-1 day to (dateadd(:i month to QUARTERDATEEND)));
    while (:quarter > CLAIMBEGIN) do
    begin
    select
    file_overview_file.file_id,
    file_overview_file.total_incurred_net_usd,
    file_overview_file.total_paid_net_usd,
    file_overview_file.total_os_net_usd,
    file_overview_file.total_fees_eur
    from file_overview (0,Quarter,FILE_ID)
    into
    :FILEIDTRIA,
    :INCURREDUSDTRIA,
    :PAIDUSDTRIA,
    :OSUSDTRIA;
    i= i-3;
    SUSPEND;
    end
    END
    END

    But I only get the error "expression evaluation not supported" just before it even goes into the loop where the variabel QUARTER gets assigned. I found that dateadd is usable in PSQL but it seems not to work.

    Can anyone help?

    Thanks very much in advance
    Christian
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    It seems to hang at this point

    QUARTER = dateadd(-1 day to (dateadd(I month to QUARTERDATEEND)));

    And gives the "expression evaluation is not supported" error.

    Kind regards
    Christian
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Hi there,
    just found the correct way myself.

    It has to be
    QUARTER = dateadd(-1 day to (dateadd(I month to cast(QUARTERDATEEND as date))));

    Now I am wondering how to output the variabel QUARTER in the resulting set as well.

    Something like
    :QUARTEROUT=QUARTER
    always throws an error.

    Kind regards
    Christian
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Hi again,
    just found out myself. The : has to be left out and then it workes.

    Kind regards
    Christian

IMN logo majestic logo threadwatch logo seochat tools logo