Home » SQL & PL/SQL » SQL & PL/SQL » Return all rows if no matching condition found (Oracle 12c)
Return all rows if no matching condition found [message #660753] Sun, 26 February 2017 05:08 Go to next message
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
Re: Return all rows if no matching condition found [message #660755 is a reply to message #660753] Sun, 26 February 2017 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
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.
Re: Return all rows if no matching condition found [message #660757 is a reply to message #660755] Sun, 26 February 2017 05:36 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
Thanks Smile
Re: Return all rows if no matching condition found [message #660792 is a reply to message #660753] Mon, 27 February 2017 06:50 Go to previous messageGo to next message
EdStevens
Messages: 985
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.
Re: Return all rows if no matching condition found [message #660795 is a reply to message #660792] Mon, 27 February 2017 07:21 Go to previous message
Michel Cadot
Messages: 65849
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.

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


Current Time: Sun Oct 21 05:33:31 CDT 2018