Help - Search - Members - Calendar
Full Version: Retrieving Extra Rows
Oracle DBA Forums > Oracle > Oracle Forum
marge0512
Hello, 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 in the Select query which is B.Act_Code. When I do,
I get 2 extra rows. I do not know how to make these rows distinct.

The A table's structure is along with sample
data for 1st record:
CODE
                                   Example of
Name               Type            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
                                Example of
Name               Type         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.

I am 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.
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.

I use Oracle 10.

What am I doing wrong?

Thanks in advance!!
burleson
Hi Marge,

>> I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.


That' because you use a "delect distinct" that eliminates duplicate rows.

*********************************************

In a standard "equi" join, you get ONLY the number of rows that match:

CODE
A.file_name = B.file_name \[[code]

If you want to see non-matching rows, use an ouyer join:

[code]
A.file_name = B.file_name (+)


See Here:

http://dba-oracle.com/googlesearchsite_pro...mp;q=outer+join

If you want to learn it fast, try the Easy Oracle SQL book:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

Good Luck!


marge0512
Thanks so much for this info!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.