Home » SQL & PL/SQL » SQL & PL/SQL » Need Help With SQL Statement
Need Help With SQL Statement [message #231117] Sat, 14 April 2007 10:21 Go to next message
ronfranks1
Messages: 2
Registered: October 2006
Junior Member
This SQL needs to return 1 NEW record, marked "NEW", and only 1 OLD record, marked "OLD", if one OLD record existed before. The problem is, I am returning multiple records because there are different dates in table2.

This is what I want:

12345 New
12345 Old

This is what I don't want:

12345 New
12345 Old
12345 Old
12345 Old

Here is my script:

SELECT
distinct a.job_number ||'New'
FROM table1 a, table2 b
WHERE a.date = b.date
AND a.job_number = b.job_number;
UNION
SELECT
distinct a.job_number ||'Old'
FROM table1 a, table2 b
WHERE a.date < b.date
AND a.job_number = b.job_number;

Thanks in advance.
Re: Need Help With SQL Statement [message #231118 is a reply to message #231117] Sat, 14 April 2007 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
1) Please read & FOLLOW the guidelines in the STICKY post at top of this forum.
2) Your "requirements" are ambiguous at best.
3) if you really simply want to ensure a single row is returned add AND ROWNUM < 2 to both SELECT statements
4) This is NOT an EXPERT level question.

[Updated on: Sat, 14 April 2007 10:32] by Moderator

Report message to a moderator

Re: Need Help With SQL Statement [message #231250 is a reply to message #231117] Mon, 16 April 2007 02:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't believe you are getting the results you post from the query you have posted.

Part of my reason for thinking this is that both the distinct in the second query and the UNION statement would get rid of any duplicate rows,
Another part of my reasoning is that your posted SQL has a typo in it (hint: count the Wink and wouldn't run.
The final leg to my stool is this little example I knocked up (as you didn't seem to feel obliged to provide one) that shows a query very similar to yours working just fine.
drop table table1;

drop table table2;

create table table1 (job_number  number, job_date  date);

create table table2 (job_number  number, job_date  date);

insert into table1 values (12345,to_date('10.04.2007','dd.mm.yyyy'));
insert into table1 values (12345,to_date('09.04.2007','dd.mm.yyyy'));
insert into table1 values (12345,to_date('08.04.2007','dd.mm.yyyy'));
insert into table1 values (12345,to_date('07.04.2007','dd.mm.yyyy'));

insert into table2 values (12345,to_date('10.04.2007','dd.mm.yyyy'));
insert into table2 values (12345,to_date('08.04.2007','dd.mm.yyyy'));

SELECT a.job_number ||'New'
FROM table1 a, table2 b
WHERE a.job_date = b.job_date 
AND a.job_number = b.job_number
UNION
SELECT a.job_number ||'Old'
FROM table1 a, table2 b
WHERE a.job_date < b.job_date 
AND a.job_number = b.job_number;


A.JOB_NUMBER||'NEW'
-------------------------------------------
12345New
12345Old

So, care to provide a little more detail about what the problem actually is?

[Edited to add missing part of test case setup]

[Updated on: Mon, 16 April 2007 02:23]

Report message to a moderator

Previous Topic: checking rows in a flat file
Next Topic: MERGE - Statement
Goto Forum:
  


Current Time: Sun Dec 04 02:34:02 CST 2016

Total time taken to generate the page: 0.08311 seconds