OraFAQ Forum: SQL & PL/SQL » Return all rows if no matching condition found

Home » SQL & PL/SQL » SQL & PL/SQL » Return all rows if no matching condition found (Oracle 12c)

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

Return all rows if no matching condition found [message #660753]

Sun, 26 February 2017 05:08

pratik4891
Messages: 73
Registered: February 2011

Member

Hi ,

Can you please let me know the query if we don't find any matching condition we have to return all the rows in the table :

create table friends as

select 'Jon' as Name , 'Y' as Decision , '01-Jan-2016' as date1 from dual
union all
select 'Jon' as Name , 'N' as Decision , '02-Jan-2016' as date1 from dual
union all
select 'Linda' as Name , 'Y' as Decision , '01-Jan-2016' as date1 from dual
union all
select 'Mark' as Name , 'Y' as Decision , '05-Jan-2016' as date1 from dual
union all
select 'Rob' as Name , 'Y' as Decision , '05-Jan-2016' as date1 from dual
union all
select 'Rob' as Name , 'N' as Decision , '06-Jan-2016' as date1 from dual

it should return all rows if we run a query like

select * from friends where date1 = '15-Jan-2016'

If we any matching record found it will return only that record -

select * from friends where date1 = '15-Jan-2016'

will return records on 15th Jan only

Report message to a moderator

Re: Return all rows if no matching condition found [message #660755 is a reply to message #660753]

Sun, 26 February 2017 05:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with
  2    result as (select * from friends where date1='&date')
  3  select * from result
  4  union all
  5  select * from friends where not exists(select * from result)
  6  /
Enter value for date: 01-Jan-2016
NAME  D DATE1
----- - -----------
Jon   Y 01-Jan-2016
Linda Y 01-Jan-2016

2 rows selected.

SQL> /
Enter value for date: 11-Jan-2016
NAME  D DATE1
----- - -----------
Jon   Y 01-Jan-2016
Jon   N 02-Jan-2016
Linda Y 01-Jan-2016
Mark  Y 05-Jan-2016
Rob   Y 05-Jan-2016
Rob   N 06-Jan-2016

6 rows selected.

Report message to a moderator

Re: Return all rows if no matching condition found [message #660757 is a reply to message #660755]

Sun, 26 February 2017 05:36

pratik4891
Messages: 73
Registered: February 2011

Member

Thanks

Report message to a moderator

Re: Return all rows if no matching condition found [message #660792 is a reply to message #660753]

Mon, 27 February 2017 06:50

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

I know that this is just a POC, but you need to get in the habit of ALWAYS using the DATE data type for dates. In your sample, you created a table with a char(11) for what is obviously meant to be a date.

Your method:

SQL> --
SQL> create table friends as
  2  select 'Jon' as Name , 'Y' as Decision , '01-Jan-2016' as date1 from dual
  3  union all
  4  select 'Jon' as Name , 'N' as Decision , '02-Jan-2016' as date1 from dual
  5  union all
  6  select 'Linda' as Name , 'Y' as Decision , '01-Jan-2016' as date1 from dual
  7  union all
  8  select 'Mark' as Name , 'Y' as Decision , '05-Jan-2016' as date1 from dual
  9  union all
 10  select 'Rob' as Name , 'Y' as Decision , '05-Jan-2016' as date1 from dual
 11  union all
 12  select 'Rob' as Name , 'N' as Decision , '06-Jan-2016' as date1 from dual
 13  ;

Table created.

SQL> desc friends
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(5)
 DECISION                                           CHAR(1)
 DATE1                                              CHAR(11)

Proper method:

SQL> create table friends as
  2  select 'Jon' as Name , 'Y' as Decision , to_date('01-Jan-2016','dd-Mon-yyyy') as date1 from dual
  3  union all
  4  select 'Jon' , 'N' , to_date('02-Jan-2016','dd-Mon-yyyy')	from dual
  5  union all
  6  select 'Linda' , 'Y' , to_date('01-Jan-2016','dd-Mon-yyyy')  from dual
  7  union all
  8  select 'Mark' , 'Y' , to_date('05-Jan-2016','dd-Mon-yyyy')  from dual
  9  union all
 10  select 'Rob' , 'Y' , to_date('05-Jan-2016','dd-Mon-yyyy')	from dual
 11  union all
 12  select 'Rob' , 'N' , to_date('06-Jan-2016','dd-Mon-yyyy') from dual
 13  ;

Table created.

SQL> desc friends
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(5)
 DECISION                                           CHAR(1)
 DATE1                                              DATE

Note also that when creating a table this way, it is not necessary to repeat the column names after the initial declaration.

Report message to a moderator

Re: Return all rows if no matching condition found [message #660795 is a reply to message #660792]

Mon, 27 February 2017 07:21

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Well, the proper method is to also specify the language if you use names (for instance, the month names), otherwise use numbers.

Report message to a moderator

Previous Topic:	Why difference between count() and count()
Next Topic:	Deletes with respect to table sizes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:35:33 CDT 2024