Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: Cannot compile procedure in package.

    I got the following compilation error when I tried to compile procedure bellow

    --Compare data for ALL accounts for a given date in
    -- F_ADV_PUB_EVENT fact table with CJO_ACTION_TXN table.

    PROCEDURE testFactEqualsCJOAll (p_date1 IN DATE, p_date2 IN DATE)
    IS

    v_test_name VARCHAR2(50) := 'testFactEqualsCJOALL';
    v_err VARCHAR2(500);

    v_cjo_date DATE;

    v_lead_count NUMBER;
    v_sale_count NUMBER;
    v_cjo_sale_adv_curr NUMBER;
    v_cjo_pub_comm_usd NUMBER;
    v_cjo_cjfee_usd NUMBER;
    v_cjo_sale_amount_pub_curr NUMBER;
    v_cjo_sale_amountusd NUMBER;
    v_cjo_comm_adv_curr NUMBER;
    v_cjo_comm_pub_curr NUMBER;
    v_cj_fee_adv_curr NUMBER;
    v_cjo_imps NUMBER;
    v_cjo_clicks NUMBER;

    v_fact_date DATE;
    v_fact_sale_adv NUMBER;
    v_fact_sale_pub NUMBER;
    v_fact_comm_adv NUMBER;
    v_fact_comm_pub NUMBER;
    v_fact_sale_usd NUMBER;
    v_fact_comm_usd NUMBER;
    v_fact_fee_adv NUMBER;
    v_fact_fee_usd NUMBER;
    v_fact_imps NUMBER;
    v_fact_clicks NUMBER;
    v_fact_views NUMBER;
    v_views NUMBER;
    v_started DATE := SYSDATE;


    BEGIN





    select or.f1, or.f2, or.f3, or.f4, or.f5, or.f6, or.f7, or.f8, or.f9, or.f10
    union all
    cor.f1, cor.f2, cor.f3, cor.f4, cor.f5, cor.f6, cor.f7, cor.f8, cor.f9, cor.f10

    INTO v_lead_count, v_sale_count, v_cjo_sale_adv_curr, v_cjo_pub_comm_usd, v_cjo_cjfee_usd, v_cjo_sale_amount_pub_curr, v_cjo_sale_amountusd, v_cjo_comm_adv_curr,
    v_cjo_comm_pub_curr, v_cj_fee_adv_curr
    from

    ( SELECT --+ parallel (cat,4)


    nvl(SUM(DECODE(actiontrackertype, 'sim_lead',num_actions, 'item_lead', num_actions, 0)),0 ) AS lead_count f1
    ,nvl(SUM(DECODE(actiontrackertype, 'sim_sale', num_actions, 'item_sale', num_actions, 0)), 0) as sale_count f2
    ,nvl(sum(sale_amount_adv_curr),0) as SALE_ADV_CURR f3
    ,nvl(sum(pub_comm_usd), 0) as comm_usd f4
    ,nvl(SUM(cj_fee_usd), 0) as fee_usd f5
    ,nvl(SUM(sale_amount_pub_curr),0) as SALE_PUB_CURR f6
    ,nvl(sum(sale_amount_usd),0) as SALE_USD f7
    ,nvl(SUM(-adv_comm_adv_curr -cj_fee_adv_curr), 0) as COMM_ADV_CURR f8
    ,nvl(SUM(pub_comm_pub_curr),0) as COMM_PUB_CURR f9
    ,nvl(SUM(cj_fee_adv_curr), 0) as FEE_ADV_CURR f10

    FROM cjo_action_txn@cjo_source cat
    WHERE 1=1
    AND trunc_event_date >= p_date1
    AND trunc_event_date <= p_date2
    AND actiontrackertype IN ('sim_sale', 'sim_lead', 'item_sale', 'item_lead', 'imp', 'click')
    AND is_original = 1
    AND posting_date >= p_date1-1
    AND posting_date <
    ( SELECT
    highmark - 6/24 AS end_posting_date_pst
    FROM dw_load_history
    WHERE id = (SELECT MAX(id) FROM dw_load_history
    WHERE running=0 AND failurenum=1))) or,








    (SELECT --+ parallel (cat,4)


    nvl(SUM(DECODE(actiontrackertype, 'sim_lead',num_actions, 'item_lead', num_actions, 0)),0 ) AS lead_count f1
    ,nvl(SUM(DECODE(actiontrackertype, 'sim_sale', num_actions, 'item_sale', num_actions, 0)), 0) as sale_count f2
    ,nvl(sum(sale_amount_adv_curr),0) as SALE_ADV_CURR f3
    ,nvl(sum(pub_comm_usd), 0) as comm_usd f4
    ,nvl(SUM(cj_fee_usd), 0) as fee_usd f5
    ,nvl(SUM(sale_amount_pub_curr),0) as SALE_PUB_CURR f6
    ,nvl(sum(sale_amount_usd),0) as SALE_USD f7
    ,nvl(SUM(-adv_comm_adv_curr -cj_fee_adv_curr), 0) as COMM_ADV_CURR f8
    ,nvl(SUM(pub_comm_pub_curr),0) as COMM_PUB_CURR f9
    ,nvl(SUM(cj_fee_adv_curr), 0) as FEE_ADV_CURR f10


    FROM cjo_action_txn@cjo_source cat
    WHERE 1=1
    AND trunc_event_date >= p_date1
    AND trunc_event_date <= p_date2
    AND actiontrackertype IN ('sim_sale', 'sim_lead', 'item_sale', 'item_lead', 'imp', 'click')
    AND is_original != 1
    AND posting_date >= p_date1-1
    AND posting_date <
    ( SELECT
    highmark - 7/24 AS end_posting_date_pst
    FROM dw_load_history
    WHERE id = (SELECT MAX(id) FROM dw_load_history
    WHERE running=0 AND failurenum=1))) cor;


    v_views:= v_lead_count + v_sale_count;








    SELECT --+ parallel (f,4)
    nvl(SUM(f.imps), 0), nvl(SUM(f.hits), 0)
    INTO v_cjo_imps, v_cjo_clicks
    FROM cntr_day_p@cjo_source f
    WHERE date_ >= p_date1
    and date_ <= p_date2;




    --
    SELECT --+ parallel (f,4)

    nvl(SUM(f.sale_amount_adv_curr), 0) sale_adv
    , nvl(SUM(f.sale_amount_pub_curr) , 0) sale_pub
    , nvl(SUM(f.comm_amount_adv_curr), 0) comm_adv
    , nvl(SUM(f.comm_amount_pub_curr), 0) comm_pub
    , nvl(SUM(f.sale_amount_usd), 0) sale_usd
    , nvl(SUM(f.comm_amount_usd), 0) comm_usd
    , nvl(SUM(f.fee_adv_curr), 0) fee_adv
    , nvl(SUM(f.fee_usd), 0) fee_usd
    , nvl(SUM(DECODE(f.event_id,1,f.event_count,0)), 0) imps
    , nvl(SUM(DECODE(f.event_id,2,f.event_count,0)), 0) clicks
    , nvl(SUM(DECODE(f.event_id,3,f.event_count,0)), 0) views_
    INTO
    v_fact_sale_adv, v_fact_sale_pub, v_fact_comm_adv, v_fact_comm_pub, v_fact_sale_usd,
    v_fact_comm_usd, v_fact_fee_adv, v_fact_fee_usd, v_fact_imps, v_fact_clicks, v_fact_views
    FROM f_adv_pub_event f ,d_time t
    WHERE 1= 1
    AND f.time_key = t.dim_id
    AND t.day >= p_date1
    AND t.day <= p_date2;




    --
    assertEquals(v_test_name,'sale_amount_adv_curr',p_ date1,v_cjo_sale_adv_curr,v_fact_sale_adv);
    assertEquals(v_test_name,'sale_amount_pub_curr',p_ date1,v_cjo_sale_amount_pub_curr,v_fact_sale_pub);
    assertEquals(v_test_name,'comm_adv_curr',p_date1,v _cjo_comm_adv_curr,v_fact_comm_adv);
    assertEquals(v_test_name,'comm_pub_curr',p_date1,v _cjo_comm_pub_curr,v_fact_comm_pub);
    assertEquals(v_test_name,'sale_amount_usd',p_date1 ,v_cjo_sale_amountusd, v_fact_sale_usd);
    assertEquals(v_test_name,'comm_amount_usd',p_date1 ,v_cjo_pub_comm_usd, v_fact_comm_usd);
    assertEquals(v_test_name,'fee_adv_curr',p_date1,v_ cj_fee_adv_curr,v_fact_fee_adv);
    assertEquals(v_test_name,'fee_usd',p_date1, v_cjo_cjfee_usd,v_fact_fee_usd);
    assertEquals(v_test_name,'imps',p_date1,v_cjo_imps ,v_fact_imps);
    assertEquals(v_test_name,'clicks',p_date1,v_cjo_cl icks,v_fact_clicks);
    assertEquals(v_test_name,'views',p_date1,v_views,v _fact_views);




    EXCEPTION WHEN OTHERS THEN
    v_err := SQLCODE || ' - ' || SQLERRM;
    INSERT INTO dw_tests VALUES( SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL, SYSTIMESTAMP, 'N',v_err);
    COMMIT;
    RAISE;
    END;


    1118/1 PL/SQL: SQL Statement ignored
    1118/8 PL/SQL: ORA-00936: missing expression



    Does anybody know how to modify my select statement?

    Thanks,

    Katya

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Don't use Oracle keywords for your own purposes - OR shouldn't be used as a table alias - change it to something else.

    At the begiinning of the script: you have this:

    SELECT column_list
    UNION ALL
    another_column_list
    INTO variables
    FROM ...

    That's not the way it should be written - you can't UNION column lists, but SELECT statements.

    Furthermore, such a query can easily end up with a TOO-MANY-ROWS error if the WHERE clause isn't restrictive enough. Will it return one and only one record, always? How about a cursor loop instead, for example?

Posting Permissions

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