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 |
|
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 #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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 13:49:35 CDT 2024
|