Outer Query not returning data - SQL HELP

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 
)


--

Select allOpen 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

--

Select allOpen 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,' ') 

--

Select allOpen in new window

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.

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));

Select allOpen 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
                         )
       )

Select allOpen 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) 

Select allOpen in new window

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

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.

@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

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.

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,

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.

Naveen, also, can you show how you tested your query?  If I try to run that query I get a ORA-00936.

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 )

Select allOpen 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 )

Select allOpen in new window

Hope it helps !!

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.

Thanks a lot guys. What did the trick for me was Nvl(enddate, To_date(1,'J') as suggested by @johnsone . Appreciate it.

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 !