Retrieving Extra Rows

Dev Shed Forums Sponsor:

December 5th, 2011, 08:55 AM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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!!

December 5th, 2011, 10:29 AM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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).

__________________

December 5th, 2011, 10:52 AM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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.

December 5th, 2011, 02:00 PM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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.

December 5th, 2011, 02:52 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

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;

December 6th, 2011, 07:36 AM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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.

December 6th, 2011, 09:46 AM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

Originally Posted by marge0512

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;

December 6th, 2011, 10:03 AM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation Power: 0

It still gives me the same error. I don't understand because the columns look like they are defined correctly.

December 6th, 2011, 02:41 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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 --

#10

December 6th, 2011, 03:06 PM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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

#11

December 7th, 2011, 10:10 AM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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;

#12

December 13th, 2011, 10:38 AM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation 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?

#13

December 13th, 2011, 01:53 PM

marge0512

Registered User

Join Date: Dec 2011

Posts: 8

Time spent in forums: 1 h 36 m 44 sec
Reputation Power: 0

Uh oh, i just noticed that some rows are missing.

Retrieving Extra Rows

Developer Shed Advertisers and Affiliates