• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

Outer Query not returning data - SQL HELP

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
Mike R
Asked:
Mike R
  • 7
  • 4
  • 3
  • +1
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
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
 
Mike RAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
johnsoneSenior Oracle DBACommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
 
johnsoneSenior Oracle DBACommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
 
johnsoneSenior Oracle DBACommented:
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
 
johnsoneSenior Oracle DBACommented:
Naveen, also, can you show how you tested your query?  If I try to run that query I get a ORA-00936.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Mike RAuthor Commented:
Thanks a lot guys. What did the trick for me was Nvl(enddate, To_date(1,'J') as suggested by @johnsone . Appreciate it.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now