Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: data load and ETL

    I have a question about the data. When I load data from one instance to another, it shows 40 rows in original one but in the Destination it shows 124040. Where am I going wrong?? I was performing few basic ETL operations as shown below:

    INSERT INTO COST C
    ( BOOK
    ,CLASS
    ,ID
    ,DATE
    ,SEQNO
    ,type
    ,GROUP
    ,MEM
    ,..
    ..
    ,TAX
    ,COST
    ,act
    ,count
    )
    SELECT B.BOOK
    ,C.CLASS
    ,B.ID
    ,C.DATE
    ,C.SEQNO
    ,c.type
    ,C.GROUP
    ,C.MEM
    ..
    ..
    ,C.TAX
    ,' 'COST
    ,G.ACT
    ,(select COUNT(distinct type) from COST@mylink T where
    T.type='F')

    FROM COST@mylink C,
    BOOK@mylink B,
    GACCOUNT G
    WHERE C.ID = B.ID and
    C.BOOK = B.BOOK and
    C.act=G.act
    And I also get this error:
    C.act=G.act
    *
    ORA-00904: "C"."ACT": invalid identifier
    Column 'act' exists in both the cost and gaccount tables.
    And moreover column 'act' in cost table has a FK on act in gaccount table.
    Can someone please help me with this?
    Last edited by nandinir; 10-31-06 at 13:29.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > WHERE C.ID = B.ID and
    >C.BOOK = B.BOOK
    >C.act=G.act
    Above is INVALID syntax.
    Perhaps there should be "AND" on the right end of line #2 above
    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
    Jul 2005
    Posts
    276
    There is an 'and' I changed my code when I was pasting it here....

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    paste the exact code and then we can help you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    This is the exact code, I just changed the column names and table names.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    desc COST@mylink
    desc GACCOUNT

    PHP Code:
    SELECT count(*) 
    FROM 
      COST
    @mylink C
      
    BOOK@mylink B,
      
    GACCOUNT G
    WHERE C
    .ID B.ID and C.BOOK B.BOOK and C.act G.act
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    SQL> ED
    Wrote file afiedt.buf

    1 SELECT count(*) from
    2 cost@mylink c,
    3 book@mylink B,
    4 where c.book=b.book and
    5* c.ID=b.ID
    SQL> /

    COUNT(*)
    ----------
    21433075
    I didnt understand what exactly you meant. But here is the result.
    My mistake, c.account = g.account was throwing an error because, account doesn't exist in cost table. I took care of that point. But what about the count on the rows. How do I check if I'm getting all the rows satisfying my condition?

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    well, earlier you said that ACCOUNT exists in both tables.
    evidentaly it does not. can we agree on this?
    do we need GACCOUNT table at all? if we need it then what are we going
    to join with it?

    once we get that query setup properly then we can move forward with how
    many rows will get moved and how you check it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    Yes, we need gaccount table as 'act' column in cost is being populated from gaccount. But the problem is the only column common in gaccount and cost is account and account does not exist in cost@mylink. So how do we set the join condition now?

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    You are really confusing me. What are the common columns between the two tables???
    please describe both tables: gaccount and cost@mylink

    also, it looks like you are populating the LOCAL Cost table and also selecting
    from a REMOTE Cost table. I wouldn't use the same alias for both.

    try insert into cost local_c
    also, cost@mylink remote_c
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jul 2005
    Posts
    276
    Sorry for the confusion. Hope this will make it clear.
    desc cost
    BOOK FK
    ID FK
    ACT FK
    CLASS
    DATE
    SEQNO
    type
    GROUP
    MEM
    TAX
    ------------------
    desc cost@mylink
    BOOK
    ID
    CLASS
    DATE
    SEQNO
    type
    GROUP
    MEM
    TAX
    --------------------
    desc book@mylink
    book pk
    id pk
    ..
    ..
    ---------------
    desc gaccount
    ACT pk
    ..
    ..
    ------------------------
    where cost is populated as follows:
    all columns are populated from cost@mylink except
    id,book from book@mylink and
    act from gaccount

    WHERE C.ID = B.ID and
    C.BOOK = B.BOOK and
    condn for gaccount??
    I used a case for
    (select COUNT(distinct type) from COST@mylink T where T.type='F')
    and it works fine now. I'm worried about the g.act now and the huge# of rows in cost table.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    ok, we are now getting somewhere.
    now, how is GACCOUNT related to book@myling or cost@mylink??

    I know you want the gaccount.act information, but we need to find out how
    this table is related to the others (ie: what columns join to get an account?)

    it looks like gaccount MUST have ID and BOOK columns.
    otherwise, how do you know what accounts ordered what books?
    are we missing a 4th table in this join?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Jul 2005
    Posts
    276
    Thanks very much for being so patient. I spoke to my PM today and he told me that it was his mistake and he included a wrong column.

    Well, now I end up with the count on the rows. As I have said,
    # of rows in Cost@mylink-- 97
    #rows book--68
    #rows in cost--3mil

    What is wrong and what can I do?

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    should we start over with you describing what you need?
    how did the 3 mil in cost get loaded? what script loaded it?
    can you start over? (ie: truncate cost)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Jul 2005
    Posts
    276
    Its the same script what I posted before. Each time I load the table,I truncate it and then load the table.

Posting Permissions

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