December 2, 2011 at 8:27 am
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. 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).
Name Type
----------------- -------------------
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:
Name Type
---------------- -------------
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 2, 2011 at 9:13 am
marge0513 (12/2/2011)
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. I do not know how to make these rows distinct.
Without data and/or ddl, the best I can do is to take a shot in the dark. My guess is that when you add b.Act_Code you are getting extras because that field is not the same in each row for b.
There is no real sample data for us to look at. It would be somewhat hard to provide usable ddl since you are obviously using Oracle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2011 at 9:20 am
Thanks for responding!
Yes, you are correct. If "Cindy" had only downloaded the file and that was all that was recorded for that file then one row would be retrieved and all is well. It's when the act_code is different, then there is a problem.
I did cut and paste an actual row from each table to the right of the table's structure information (of course changing the names) but it didn't line up nicely.
December 2, 2011 at 9:27 am
marge0513 (12/2/2011)
Thanks for responding!Yes, you are correct. If "Cindy" had only downloaded the file and that was all that was recorded for that file then one row would be retrieved and all is well. It's when the act_code is different, then there is a problem.
I did cut and paste an actual row from each table to the right of the table's structure information (of course changing the names) but it didn't line up nicely.
There lies the issue. You have two values so you have to get both them. There is no way the engine can decide for you which one to get. You have to decide which one is "correct". Without knowing your data or being able to create a table the best I can do is offer some pointers.
You could get the most recent one with a subquery. In sql server you could use row_number() or cross apply. Not being too familiar with oracle I don't know if they have something similar.
Something like this should be pretty close:
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
(
select top 1 B.File_Name, B.Act_Date, B.date_loaded, b.Act_Code
from ACTIVITY_LOG
order by B.Date_loaded --or whatever your criteria for the "correct" one is
) B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2011 at 9:35 am
Yes, that makes sense. That tells me why the "Distinct" does not work in this case.
I would need to show all activities for every file and not just the most recent. I researched this site to find a command that would help and tested a couple but I could not find anything that worked.
December 2, 2011 at 9:40 am
marge0513 (12/2/2011)
Yes, that makes sense. That tells me why the "Distinct" does not work in this case.I would need to show all activities for every file and not just the most recent. I researched this site to find a command that would help and tested a couple but I could not find anything that worked.
It sounded like that is what you had when you were getting multiples? I can help but I would need some ddl (create table statements) and sample data (insert statements). This is a little more challenging because I would have to convert your datatypes from oracle to sql server but not too much issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2011 at 9:51 am
We're not an Oracle site, but you might try a couple things.
First examine the data in the "b" table and look at the results for the additional filter. My guess is you have some other rows that are matching, but not completely duplicate.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 2, 2011 at 10:19 am
When I looked at the B Table, the information was exactly the same. I was getting something like this (shortened of course):
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.
We have an Oracle guy here that does the actual creating and inserting of tables but I will research and give you some examples in a few.
Thanks!
December 2, 2011 at 11:54 am
Here are the statements:
CREATE TABLE SDT_LOG ( FILE_NAME VARCHAR2(50),
FILE_DESC VARCHAR2(50),
FILE_LOCATION VARCHAR2(50),
LOCATION_DATE DATE,
DOWNLOADED_DATE DATE,
DOWNLOADED_ID VARCHAR2(50),
FILE_SIZE CHAR(10),
DAYS_TO_REQUEST NUMBER(3));
INSERT INTO SDT_LOG
(FILE_NAME, FILE_DESC, FILE_LOCATION, LOCATION_DATE, DOWNLOADED_DATE, DOWNLOADED_ID, FILE_SIZE, DAYS_TO_REQUEST)
VALUES
('STLMK.txt', 'NON-RESIDENT', 'L:\\NonResFiles', '2008',
'09/04/2008 9:17:00 AM', 'Cindy', '16212', '60');
INSERT INTO SDT_LOG
(FILE_NAME, FILE_DESC, FILE_LOCATION, LOCATION_DATE, DOWNLOADED_DATE, DOWNLOADED_ID, FILE_SIZE, DAYS_TO_REQUEST)
VALUES
('STLMK.txt', 'NON-RESIDENT', 'L:\\SamsFiles', '2008',
'09/04/2008 9:17:00 AM', 'Jason', '16212', '60');
(The file_location and downloaded_id was changed in the second insert.)
CREATE TABLE ACTIVITY_LOG ( FILE_NAME VARCHAR2(50),
ACT_CODE CHAR(2)
ACT_DATE DATE
ACTIVITY_ID VARCHAR2(50)
DATE_LOADED DATE);
INSERT INTO ACTIVITY_LOG
(FILE_NAME, ACT_CODE, ACT_DATE, ACTIVITY_ID, DATE_LOADED)
VALUES
('STLMK.txt', 'D', '10/10/2007', 'Cindy',
'09/04/2008 9:17:00 AM');
INSERT INTO ACTIVITY_LOG
(FILE_NAME, ACT_CODE, ACT_DATE, ACTIVITY_ID, DATE_LOADED)
VALUES
('STLMK.txt', 'S', '10/10/2007', 'Jason',
'09/04/2008 9:17:00 AM');
(The act_code and activity_id changed with the second insert.)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply