Solved

Outer Query not returning data - SQL HELP

Posted on 2016-11-05
16
39 Views
Last Modified: 2016-11-06
SELECT * FROM TABLEB WHERE (ID,STARTDATE,ENDDATE) IN
(SELECT DISTINCT(ID,STARTDATE,ENDDATE) FROM TABLEB
WHERE UPDATED_DATE>SELECT MAX(UPDATED_DATE) FROM TABLEA)

What is the problem with this query. The inner query is returning me data. The value
for ENDDATE is null in the table. However when executing the complete query its returing
me zero results.How do we solve it .
0
Comment
Question by:Mike R
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
I think multiple columns in IN clause will not work, Single columns will work..

--

SELECT * FROM TABLEB WHERE ID IN
(
       SELECT DISTINCT ID FROM TABLEB
       WHERE UPDATED_DATE > SELECT MAX(UPDATED_DATE ) FROM TABLEA 
)


--

Open in new window


if you want to compare multiple columns then use join , try below

--

SELECT * FROM 
(
 SELECT DISTINCT ID,STARTDATE,ENDDATE FROM TABLEB
 WHERE UPDATED_DATE > SELECT MAX(UPDATED_DATE ) FROM TABLEA 
)  X
INNER JOIN TableB AS B ON B.ID = X.ID AND B.StartDate = X.StartDate AND B.EndDate = X.EndDate

--

Open in new window



You can use coalesce also if the values of a column like EndDate is null.

--

SELECT * FROM 
(
 SELECT DISTINCT ID,STARTDATE,ENDDATE FROM TABLEB
 WHERE UPDATED_DATE > SELECT MAX(UPDATED_DATE ) FROM TABLEA 
)  X
INNER JOIN TableB AS B ON B.ID = X.ID AND B.StartDate = X.StartDate AND coalesce(B.EndDate,' ') = coalesce(X.EndDate,' ') 

--

Open in new window

0
 

Author Comment

by:Mike R
Comment Utility
Thanks for the answers Pawan.

I am having the same issue. The issue seems to be because ENDDATE is NULL. If I remove ENDDATE its returning me values. However what i need is a distinct value of ID,START and END Date.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Since when are mulitple columns not allow in an in-list?  Did you try it?
create table mytab (c1 number, c2 number, c3 number);
insert into mytab values (1,1,1);
insert into mytab values (1,2,2);
insert into mytab values (1,1,2);
insert into mytab values (1,2,3);
insert into mytab values (2,1,1);
insert into mytab values (2,1,3);
insert into mytab values (3,1,2);
commit;
select * from mytab where (c1,c2,c3) in ((1,1,1),(3,1,4),(3,1,2));

Open in new window


Your issue is really the NULL.  Since the NULL will fail an equality test, you need to fool it.  The way that I would do that is to us NVL on both sides and use a value that couldn't possibly occur within the data.  Something like this:
SELECT * 
FROM   tableb 
WHERE  ( 
         id,startdate,Nvl(enddate, To_date(1,'J')
       ) IN 
       ( 
         SELECT DISTINCT(id,startdate,nvl(enddate, to_date(1,'J')))
         FROM            tableb 
         WHERE           updated_date > 
                         ( 
                           SELECT max(updated_date)
                           FROM   tablea
                         )
       )

Open in new window

Now, that being said, I'm trying to determine what you are really trying to get to.  I don't think you really need a query that complicated.  You really shouldn't have to hit tableb twice.  The simpler version I see would be this:

SELECT * 
FROM   tableb 
WHERE  updated_date > (SELECT Max(updated_date) 
                       FROM   tablea) 

Open in new window

But I have a feeling you aren't using that for a reason.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Also, the DISTINCT is not necessary on the inner query.  An in list processes the list as a distinct list, you won't get duplicates and you are doing a totally unnecessary sort operation.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - use dump(column) to see what's really in it

with employee as
   (select 1 emp, cast('  ' as varchar2(50)) Hdate from dual
    union all
    select 2 emp, to_char(sysdate) Hdate from dual
   )
select emp,Hdate, dump(Hdate) from employee

===============================================================
       EMP               Hdate  DUMP(Hdate)
       1                  Typ=1 Len=2: 32,32
       2      04.11.16      Typ=1 Len=8: 48,52,46,49,49,46,49,54
===============================================================

Code you need to try

SELECT DISTINCT ID,STARTDATE,ENDDATE , DUMP(ENDDATE) FROM TABLEB
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
What does copying someone else's comment (specifically this one ID #41873714  ) from another question have to do with this question.

That is from a question where the issue couldn't be duplicated and people were trying to see what was actually in the field.  In this question, it is quite clear that it is a date and that it is null.  I can quite simply duplicate the issue and have given a working solution to solve it.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Hi Mike,

since you know that you have null value for ENDDATE column, you can just try this as well which is the same as your query but including the nvl for the ENDDATE. I have tested this and it should work fine for you.

SELECT * FROM TABLEB WHERE (ID,STARTDATE,nvl(ENDDATE,'01-Jan-2015')) IN
(SELECT DISTINCT(ID,STARTDATE,nvl(ENDDATE,'01-Jan-2015')) FROM TABLEB
WHERE UPDATED_DATE>SELECT MAX(UPDATED_DATE) FROM TABLEA);

Thanks,
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
Comment Utility
@johnsone - Sorry about the above post.
@Author- Try..using decode ( B.EndDate , X.EndDate , 1, 0 )  = 1 ) .

--

SELECT * FROM 
(
 SELECT DISTINCT ID,STARTDATE,ENDDATE FROM TABLEB
 WHERE UPDATED_DATE > SELECT MAX(UPDATED_DATE) FROM TABLEA 
)  X
INNER JOIN TableB B ON B.ID = X.ID AND B.StartDate = X.StartDate 
AND ( decode( B.EndDate , X.EndDate , 1, 0 )  = 1 )

--

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Naveen, that is exactly what I posted, except you use a string and not a date, since it is a date, a date comparison would be better.  You also cannot rely on implicit date conversion, you never know what setting of NLS_DATE_FORMAT a user has.

Pawan, why are you trying to do with a DECODE?  Still going to fail, because if B.ENDDATE is null  and X.ENDDATE is null there is no equality there so you would get 0.  You are trying to over complicate the NVL function.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Naveen, also, can you show how you tested your query?  If I try to run that query I get a ORA-00936.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
In this scenario decode is working for me. I have used NULL in both the ctes.

with CTE 
as 
   (
    select 1 a1, NULL EndDate from dual
    union all 
    select 2 a1, 2 EndDate from dual
   )   
,CTE1 AS
(
    select 1 a1, NULL EndDate from dual
    union all 
    select 2 a1, 2 EndDate from dual
)
SELECT * FROM CTE a
INNER JOIN CTE1 b ON a.a1 = b.a1 AND ( decode( a.EndDate , b.EndDate , 1, 0 )  = 1 )

Open in new window


Output

-------------------

       A1      ENDDATE      A1      ENDDATE
1      1      NULL              1      NULL
2      2      2                      2      2


I think below if worth a try..

SELECT * FROM 
(
 SELECT DISTINCT ID,STARTDATE,ENDDATE FROM TABLEB
 WHERE UPDATED_DATE > SELECT MAX(UPDATED_DATE) FROM TABLEA 
)  X
INNER JOIN TableB B ON B.ID = X.ID AND B.StartDate = X.StartDate 
AND ( decode( B.EndDate , X.EndDate , 1, 0 )  = 1 )

Open in new window


Hope it helps !!
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
Comment Utility
OK, I totally take back that DECODE wouldn't work.  Maybe they changed the behavior, but it will consider 2 NULLs to be equal.  It is even in the documentation that it will do it (I guess I should have checked there).  Probably because I never used it to do NULL checking like that.

I would honestly do it this way:
SELECT     * 
FROM       ( 
                           SELECT DISTINCT id, 
                                           startdate, 
                                           Nvl(enddate, To_date(1,'J')) enddate 
                           FROM            tableb 
                           WHERE           updated_date > 
                           select max(updated_date) 
                           FROM   tablea ) x 
inner join tableb b 
ON         b.id = x.id 
AND        b.startdate = x.startdate 
AND        nvl(b.enddate, to_date(1, 'J')) = x.enddate

Open in new window

Not because it is better, but simply to re-enforce that we are doing this for a null check.

I still think there is a simpler way to do this.  I'm just not seeing the requirements.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 125 total points
Comment Utility
Oops...There is a typo there in that with those brackets. This should work with my version but i agree that using a to_date(..) would be much better.

SELECT * FROM TABLEB WHERE (ID,STARTDATE,nvl(ENDDATE,to_date('01-Jan-2015','dd-mon-yyyy'))) IN
(SELECT DISTINCT ID,STARTDATE,nvl(ENDDATE,to_date('01-Jan-2015','dd-mon-yyyy')) FROM TABLEB
WHERE UPDATED_DATE>SELECT MAX(UPDATED_DATE) FROM TABLEA);

I do not need points.

Thanks,
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Naveen, that still generates ORA-00936.  I thought you were testing your solution before you posted it.  FYI - the original posted query also generates a ORA-00936, but if you tested yours, you should have caught that.  The one that I posted still works.
0
 

Author Closing Comment

by:Mike R
Comment Utility
Thanks a lot guys. What did the trick for me was Nvl(enddate, To_date(1,'J') as suggested by @johnsone . Appreciate it.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
This is weird and mycopy/paste seems to have some issues due to some issues in my keyboard. This works for me and this is what i had tested.

SELECT * FROM TABLEB WHERE (ID,STARTDATE,nvl(ENDDATE,to_date('01-Jan-2015','dd-mon-yyyy'))) IN
(SELECT DISTINCT ID,STARTDATE,nvl(ENDDATE,to_date('01-Jan-2015','dd-mon-yyyy')) FROM TABLEB
WHERE UPDATED_DATE>(SELECT MAX(UPDATED_DATE) FROM TABLEA));

Thanks !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now