OraFAQ Forum: SQL & PL/SQL » how to write this sql query

Home » SQL & PL/SQL » SQL & PL/SQL » how to write this sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to write this sql query [message #324962]

Wed, 04 June 2008 11:36

bztom33
Messages: 95
Registered: June 2005

Member

Hi,

How can I query a unique list of users?

create table report(
user_id number,
first varchar2(20),
last varchar2(20),
report_date date,
actual_date date,
constraint report_pk primary key (user_id,report_date,actual_date);

insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'John','Doe','04/01/2008','06/04/2008 10:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','06/01/2008','06/05/2008 11:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','04/02/2008','06/07/2008 08:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Lisa','Simpson','03/01/2008','06/14/2008 19:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Bart','Simpson','03/01/2008','06/17/2008 19:37');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Bart','Simpson','05/01/2008','06/11/2008 15:37');

I would like to get a unique list of of all the users.

Thanks,

Tom

Report message to a moderator

Re: how to write this sql query [message #324963 is a reply to message #324962]

Wed, 04 June 2008 11:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

SELECT DISTINCT

Report message to a moderator

Re: how to write this sql query [message #324988 is a reply to message #324962]

Wed, 04 June 2008 12:51

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

bztom33 wrote on Wed, 04 June 2008 12:36

orcl10g SCOTT>l
  1  create table report(
  2  user_id number,
  3  first varchar2(20),
  4  last varchar2(20),
  5  report_date date,
  6  actual_date date,
  7* constraint report_pk primary key  (user_id,report_date,actual_date)
orcl10g SCOTT>/
constraint report_pk primary key (user_id,report_date,actual_date)
                                                                 *
ERROR at line 7:
ORA-00907: missing right parenthesis

So, I took the effort to fix that problem, then

orcl10g SCOTT>l
  1  insert into report(user_id,first,last,report_date,actual_date)
  2* values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36')
orcl10g SCOTT>/
values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36')
                      *
ERROR at line 2:
ORA-01843: not a valid month

[Updated on: Wed, 04 June 2008 12:52]

Report message to a moderator

Re: how to write this sql query [message #325050 is a reply to message #324962]

Wed, 04 June 2008 22:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>values(1,'Bart','Simpson','05/01/2008','06/11/2008 15:37');
Please remember that with Oracle when characters exist between single quote marks they are STRINGS; not date datatype.

'This is a string; 2008-06-06, not a date'
>'Bart','Simpson'
both above look like strings to me.

When you want/need a DATE datatype, then always use TO_DATE() function.

Report message to a moderator