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

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0

    Retrieving Extra Rows


    Hello everyone, I have this SQL that returns the correct amount of rows which should be 2:

    Select Distinct A.File_Name, A.File_Desc, A.file_location,
    A.location_date, A.downloaded_date, A.downloaded_id, A.file_size, A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
    from SDT_LOG A Inner Join ACTIVITY_LOG B
    On A.file_name = B.file_name
    and A.downloaded_date = B.date_loaded

    I need to add another field which is B.Act_Code. When I do, I get 2 extra rows and this only happens when the Act_Code is different. I do not know how to make these rows distinct.

    The A table's structure is along with sample
    data for 1st record:
    (I'm sorry this is hard to read. I cannot separate the
    information neatly for the forum for some reason).

    FILE_NAME VARCHAR2(50) STLMK.txt
    FILE_DESC VARCHAR2(50) NON-RESIDENT
    FILE_LOCATION VARCHAR2(50) L:\\NonResFiles
    YEAR NUMBER(4) 2008
    LOCATION_DATE DATE 10/10/2007
    DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
    DOWNLOADED_ID VARCHAR2(50) Cindy
    FILE_SIZE CHAR(10) 16212
    DAYS_TO_REQUEST NUMBER(3) 60



    The B table's structure is along with sample
    data for 1st record:

    FILE_NAME VARCHAR2(50) STLMK.txt
    ACT_CODE CHAR(2) D
    ACT_DATE DATE 10/10/2007
    ACTIVITY_ID VARCHAR2(50) downloaded on
    DATE_LOADED DATE 09/04/2008 9:17:00 AM


    The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.

    There should only be 2 rows, one with Cindy with an act_code of D and one with Jason with an act_code of S.

    Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.

    Also, I get the 2 extra rows when I add activity_id field to the select.

    We use Oracle 10.

    What am I doing wrong?

    Thanks in advance!!
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Originally Posted by marge0512
    ... Etc ...
    I need to add another field which is B.Act_Code. When I do, I get 2 extra rows and this only happens when the Act_Code is different. I do not know how to make these rows distinct.
    ... Etc ...
    If the account numbers are DIFFERENT, that makes them "distinct", therefore you get one row for each act_code.

    Post a sample of the source data and expected results, use the "code" tags to format your posted data and sql code (click the # above).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    Thanks for responding!

    I was getting something like this (shortened of course):

    Code:
    File_name       Downloaded_ID      Act_Code
    
    STLMK.txt          Cindy               D
    STLMK.txt          Cindy               S
    STLMK.txt          Jason               D
    STLMK.txt          Jason               S
    There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.

    Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    I'm thinking now that I need to add another field in one of the tables and add another line in the where clause.


    The A table's structure is along with sample
    data for 1st record:
    Code:
                                                  Sample Data
    NAME                         TYPE             of 1st Record
    ------                        -----           ------------
    FILE_NAME                 VARCHAR2(50)        STLMK.txt 
    FILE_DESC                 VARCHAR2(50)        NON-RESIDENT 
    FILE_LOCATION             VARCHAR2(50)        L:\\NonResFiles
    YEAR                      NUMBER(4)           2008
    LOCATION_DATE             DATE               10/10/2007 
    DOWNLOADED_DATE      DATE           09/04/2008 9:17:00 AM 
    DOWNLOADED_ID             VARCHAR2(50)        Cindy
    FILE_SIZE                 CHAR(10)            16212
    DAYS_TO_REQUEST           NUMBER(3)           60
    The B table's structure is along with sample
    data for 1st record:
    Code:
                                          Sample Data
    NAME                    TYPE          of 1st Record
    ------                  -----        ------------
    FILE_NAME             VARCHAR2(50)    STLMK.txt
    ACT_CODE              CHAR(2)         D
    ACT_DATE              DATE            10/10/2007 
    ACTIVITY_ID           VARCHAR2(50)    downloaded on
    DATE_LOADED           DATE            09/04/2008 9:17:00 AM
    The second record has the same information except downloaded_id would be 'Jason', act_code would be an 'S'
    and activity_id would be 'sent to'. Date_loaded and downloaded_date could be the same or different, all depending on how fast the user entered the information. The first record shows that Cindy downloaded the file and the second record means Jason sent the file to someone else. Only those two records should be returned. Instead I get the 4 rows with each name having downloaded and sending the file.
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    Try something like this:
    Code:
    SELECT *
    FROM (SELECT a.file_name,
                 a.file_desc,
                 a.file_location,
                 a.location_date,
                 a.downloaded_date,
                 a.downloaded_id,
                 a.file_size,
                 a.days_to_request,
                 b.file_name,
                 b.act_date,
                 b.date_loaded,
                 ROW_NUMBER( ) OVER (PARTITION BY b.file_name, b.file_name
                                     ORDER BY b.date_loaded DESC)
                   rn
          FROM   sdt_log a
               INNER JOIN
                 activity_log b
               ON a.file_name = b.file_name AND a.downloaded_date = b.date_loaded)
    WHERE rn = 1;
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    Thanks!

    I'm getting the error ORA-00918 column ambiguously defined. Would that be because activity_log is referenced with 'b' after the Inner Join and not before?? I did change the 'b' to an 'a' on f.file_name after the Partition By.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    Originally Posted by marge0512
    Thanks!

    I'm getting the error ORA-00918 column ambiguously defined. Would that be because activity_log is referenced with 'b' after the Inner Join and not before?? I did change the 'b' to an 'a' on f.file_name after the Partition By.
    Yes, was a typo...
    Code:
    SELECT *
    FROM (SELECT a.file_name,
                 a.file_desc,
                 a.file_location,
                 a.location_date,
                 a.downloaded_date,
                 a.downloaded_id,
                 a.file_size,
                 a.days_to_request,
                 b.file_name,
                 b.act_date,
                 b.act_code,
                 b.date_loaded,
                 ROW_NUMBER( ) OVER (PARTITION BY b.file_name, b.act_code
                                     ORDER BY b.date_loaded DESC)
                   rn
          FROM   sdt_log a
               INNER JOIN
                 activity_log b
               ON a.file_name = b.file_name AND a.downloaded_date = b.date_loaded)
    WHERE rn = 1;
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    It still gives me the same error. I don't understand because the columns look like they are defined correctly.
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by marge0512
    It still gives me the same error. I don't understand because the columns look like they are defined correctly.
    Can you post the error? copy and paste whole sql you are executing.

    PS: Try:
    Code:
    -- etc --
    PARTITION BY a.file_name, b.act_code
                                     ORDER BY b.date_loaded DESC)
    -- etc --
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    Sure. The error:

    I don't know how to paste the image to the forum.
    I tried.

    Here is the Sql:

    Code:
    SELECT *
    FROM (SELECT a.file_name,
                 a.file_desc,
                 a.file_location,
                 a.location_date,
                 a.downloaded_date,
                 a.downloaded_id,
                 a.file_size,
                 a.days_to_request,
                 b.file_name,
                 b.act_date,
                 b.act_code,
                 b.date_loaded,
                 ROW_NUMBER( ) OVER (PARTITION BY a.file_name, b.act_code
                                     ORDER BY b.date_loaded DESC)
                   rn
          FROM   sdt_log a
               INNER JOIN
                 activity_log b
               ON a.file_name = b.file_name AND a.downloaded_date = b.date_loaded)
    WHERE rn = 1
    But the error is ORA-00918 Column Ambiguously Defined
  20. #11
  21. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by marge0512
    Sure. The error:

    I don't know how to paste the image to the forum.
    I tried.

    But the error is ORA-00918 Column Ambiguously Defined
    OK found it: you have file_name twice, once for table a and another for table b; you need alias for table b:

    Code:
    SELECT *
    FROM (SELECT a.file_name,
                 a.file_desc,
                 a.file_location,
                 a.location_date,
                 a.downloaded_date,
                 a.downloaded_id,
                 a.file_size,
                 a.days_to_request,
                 b.file_name b_file_name,
                 b.act_date,
                 b.act_code,
                 b.date_loaded,
                 ROW_NUMBER( ) OVER (PARTITION BY a.file_name, b.act_code
                                     ORDER BY b.date_loaded DESC)
                   rn
          FROM   sdt_log a
               INNER JOIN
                 activity_log b
               ON a.file_name = b.file_name AND a.downloaded_date = b.date_loaded)
    WHERE rn = 1;
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    Sorry, I was away from my desk for a few days but.........

    Yes!!!!!!!!!!!!!!!!!! It worked!!!! Thank you so much!!!!

    Question though, why do I need the alias?
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    17
    Rep Power
    0
    Uh oh, i just noticed that some rows are missing.

IMN logo majestic logo threadwatch logo seochat tools logo