Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Dec 2009
    Posts
    18

    Wink Unanswered: Cursor populate data

    Hi everyone,
    using this cursor fine but I am not getting data into my dw_time table and dw_spend_fact
    Any help please !!!

    SQL> DECLARE
    2 cursor c1 is select
    3 a.transaction_id,a.supplier_id,a.CATEGORY_ID,a.gro ss,a.order_date,c.cardholder_id, d.department_id
    4 FROM transaction_cleansig1 a,card_cleansig b,cardholder_cleansig c,department_cleansig d
    5 WHERE a.card_id=b.card_id
    6 AND
    7 b.cardholder_id=c.cardholder_id
    8 AND
    9 c.department_id=d.department_id
    10 order by a.transaction_id;
    11
    12 icount integer := 0;
    13 spid varchar2(20);
    14 ctid varchar2(20);
    15 chid varchar2(20);
    16 depid varchar2(20);
    17 month integer;
    18 year integer;
    19
    20 BEGIN
    21 for rec in c1 loop
    22
    23 icount := icount + 1;
    24
    25 month := to_char(rec.order_date, 'MM');
    26 year := to_char(rec.order_date, 'YYYY');
    27 insert into dw_time values (icount,rec.order_date,year,month);
    28
    29 select supplier_id into spid from dw_supplier where supplier_id=rec.supplier_id;
    30 select category_id into ctid from dw_category where CATEGORY_ID=rec.CATEGORY_ID;
    31 select cardholder_id into chid from dw_cardholder where cardholder_id=rec.cardholder_id;
    32 select department_id into chid from dw_department where department_id=rec.department_id;
    33 insert into dw_spend_fact values (icount, depid, spid, ctid, chid, rec.gross);
    34 end loop;
    35 END;
    36 /

    PL/SQL procedure successfully completed.

    SQL> select *from dw_time;

    no rows selected

    SQL> select *from dw_spend_fact;

    no rows selected

    I am not getting data into my dw_time table and dw_spend_fact
    Any help please !!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How many rows are returned by SQL below?
    Code:
    SELECT   a.transaction_id,
             a.supplier_id,
             a.category_id,
             a.gro ss,
             a.order_date,
             c.cardholder_id,
             d.department_id
    FROM     transaction_cleansig1 a,
             card_cleansig b,
             cardholder_cleansig c,
             department_cleansig d
    WHERE    a.card_id = b.card_id
             AND b.cardholder_id = c.cardholder_id
             AND c.department_id = d.department_id
    ORDER BY a.transaction_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2009
    Posts
    18
    As you noticed from my dw_time table and dw_spend_fact table don't got data in

    these tables are empty dw_time and dw_spend_fact table

    data comming from order_date held by transaction_cleansig1 table to be into dw_time
    data from transaction_cleansig1 a,card_cleansig b,cardholder_cleansig c,department_cleansig d tables to be into dw_spend_fact table

    SQL> desc dw_spend_fact
    Name Null? Type
    ----------------------------------------- -------- ---------------
    TIME_ID NUMBER
    DEPARTMENT_ID VARCHAR2(20)
    SUPPLIER_ID VARCHAR2(20)
    CATEGORY_ID VARCHAR2(20)
    CARDHOLDER_ID VARCHAR2(20)
    GROSS BINARY_DOUBLE

  4. #4
    Join Date
    May 2009
    Posts
    13
    I think that anacedent was trying to ask how many rows the cursor query returns.

  5. #5
    Join Date
    Dec 2009
    Posts
    18
    for each id has to return at least more than one data

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by rolmau View Post
    for each id has to return at least more than one data
    while the statement above may be true, it provides no additional meaningful details, so you are no closer to solving your mystery than when you started.

    One of TWO realities exist.
    1) code is doing exactly what it should be doing.
    2) You need to report this behaviour as a bug to Oracle with a documented test case.

    It would be helpful if you provided DDL for tables involved.
    It would be helpful if you provided DML for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2009
    Posts
    18
    any suggestion to make it works !!!

    I mean even for the new cursor ???

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from following SQL below

    Code:
    SELECT   count(*)
    FROM transaction_cleansig1 a,
             card_cleansig b,
             cardholder_cleansig c,
             department_cleansig d
    WHERE    a.card_id = b.card_id
             AND b.cardholder_id = c.cardholder_id
             AND c.department_id = d.department_id;

    post results from SQL below
    Code:
    SELECT   count(*)
    FROM transaction_cleansig1 a,
             cardholder_cleansig c,
             department_cleansig d
    WHERE   (a.card_id,c.cardholder_id) IN (SELECT b.card_id, b.cardholder_id 
                                                           from  card_cleansig b)
          AND c.department_id = d.department_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Dec 2009
    Posts
    18
    Anacedent thanks a lot I really appreciate all you are doing.

  10. #10
    Join Date
    Dec 2009
    Posts
    18
    getting this error
    order by a.transaction_id;
    *
    ERROR at line 10:
    ORA-06550: line 10, column 7:
    PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
    begin function pragma procedure subtype type <an identifier>
    <a double-quoted delimited-identifier> current cursor delete
    exists prior

  11. #11
    Join Date
    Dec 2009
    Posts
    18
    if I have to cancelled the line 10
    this is error
    month := to_char(rec.order_date, 'MM');
    *
    ERROR at line 25:
    ORA-06550: line 25, column 27:
    PLS-00302: component 'ORDER_DATE' must be declared
    ORA-06550: line 25, column 6:
    PL/SQL: Statement ignored
    ORA-06550: line 26, column 26:
    PLS-00302: component 'ORDER_DATE' must be declared
    ORA-06550: line 26, column 6:
    PL/SQL: Statement ignored
    ORA-06550: line 27, column 47:
    PLS-00302: component 'ORDER_DATE' must be declared
    ORA-06550: line 27, column 47:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 27, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 29, column 76:
    PLS-00302: component 'SUPPLIER_ID' must be declared
    ORA-06550: line 29, column 72:
    PL/SQL: ORA-00904: "REC"."SUPPLIER_ID": invalid identifier
    ORA-06550: line 29, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 30, column 75:
    PLS-00302: component 'CATEGORY_ID' must be declared
    ORA-06550: line 30, column 71:
    PL/SQL: ORA-00904: "REC"."CATEGORY_ID": invalid identifier
    ORA-06550: line 30, column 7:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 31, column 82:
    PLS-00302: component 'CARDHOLDER_I

  12. #12
    Join Date
    Dec 2009
    Posts
    18
    hello everyone

    having a problem to convert varchar to date

    I have two tables tr1 with recorded and tr2 empty


    create table tr1
    (
    Fname varchar2,
    Lname varchar2,
    Order_date varchar2);

    I created a new table to convert order_date to DATE

    create table tr2(
    Fname varchar2,
    Lname varchar2,
    Order_date date);

    Now to insert data from tr1 to tr2 having a problem

    INSERT INTO tr2 (Fname,Lname,order_date)
    SELECT Fname,Lname,order_date(order_date, 'dd/mm/yyyy'),
    FROM tr1;

    any help please
    ORA-00904: "ORDER_DATE": invalid identifier

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT INTO tr2 (Fname,Lname,order_date) SELECT Fname,Lname,order_date FROM tr1;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Shouldn't ORDER_DATE(order_date, 'dd/mm/yyyy') be TO_DATE(order_date, 'dd/mm/yyyy')?

  15. #15
    Join Date
    Dec 2009
    Posts
    18
    This is I dit
    TO_DATE(ORDER_DATE,'DD/MM/YYYY')

    INSERT INTO tr2 (Fname,Lname,TO_DATE(ORDER_DATE 'DD/MM/YYYY'));
    *
    ERROR at line 1:
    ORA-00917: missing comma

Posting Permissions

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