Oracle query not returning any rows

Hello,
  I have the following  oracle query which return no rows . However, if I execute the sub queries individually there is a match.

select
        t.pk_id,
        t.cand_id,
        t.assignment_id,
         TO_CHAR(t.assignment_begin_date,
        'yyyy-mm-dd') as assignment_begin_date,
        TO_CHAR(t.assignment_end_date,
        'yyyy-mm-dd') as assignment_end_date
       
    from
        cand_rfo t
    join
        (
            select
                cand_id,
                max(pk_id) as pk_id,
                max(modified_date) as modified_date
            from
                cand_rfo
            where
                cand_id = '3100'
            group by
                cand_id
        ) m
            on m.cand_id = t.cand_id
             and  m.modified_date = t.modified_date
            and  m.pk_id = t.pk_id

Open in new window


sub query result
select
        t.pk_id,
        t.cand_id,
        t.assignment_id,
        TO_CHAR(t.assignment_begin_date,
        'yyyy-mm-dd') as assignment_begin_date,
        TO_CHAR(t.assignment_end_date,
        'yyyy-mm-dd') as assignment_end_date, t.modified_date
    from
        cand_rfo t
    where cand_id = '3100'

Open in new window


OUTPUT
pk_id    cand_id   assignment_id   startdate           enddate              modified_date
76          3100               9320798        2015-10-17          2015-11-01      27-AUG-15
72          3100              9322640                2015-10-17          2015-11-01      27-AUG-15
73          3100              9322203                2015-10-17          2015-11-01      27-AUG-15
75          3100              9322201                2015-10-17          2015-11-01      27-AUG-15



select
                cand_id,
                max(pk_id) as pk_id,
                max(modified_date) as modified_date
            from
                cand_rfo
            where
                cand_id = '3100'
            group by
                cand_id

Open in new window


OUTPUT
pk_id    cand_id   assignment_id   startdate           enddate              modified_date
76          3100               9320798        2015-10-17          2015-11-01      27-AUG-15

The field modified is a date field in oracle and it is populate based on a tigger
BEGIN 
	:new.modified_date := sysdate; 
END;

Open in new window


Thanks in advance for the help
chand pbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I think the issue is here:  and  m.modified_date = t.modified_date

Dates in Oracle have a time portion built in.

When selecting the dates, look at the time:
to_char(max(modified_date),'MM/DD/YYYY HH24:MI:SS') as modified_date


If there are indexes on the date columns, DO NOT do this but if not, make the join:
and  trunc(m.modified_date) = trunc(t.modified_date)

The trunc call zeros out the time.

If there are indexes, we can adjust to not use the trunc function.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
You are joining on a date field.  What you are not showing in your sample data is the time portion of those date fields.  I'm guess those are not matching and that is why you are getting no rows.
0
Shaun KlineLead Software EngineerCommented:
Do your dates have a time component? If they do, do the times match?
0
slightwv (䄆 Netminder) Commented:
>>Do your dates have a time component? If they do, do the times match?

Yes, they have 'time'.  They showed the trigger code ":new.modified_date := sysdate;"
0
awking00Commented:
If this query returns a count > 0, you'll know that the problem is with the time portion of the modified_date column as has been stated herein.

select count(*) from
cand_rfo rfo1 inner join
(select cand_id, max(pk_id) as pk_id, max(trunc(modified_date) as mod_dt
 from cand_rfo
 where cand_id = '3100'
 group by cand_id) rfo2
on rfo2.cand_id = rfo1.cand_id
and rfo2.pk_id = rfo1.pk_id
and rfo2.mod_dt = trunc(rfo1.modified_date);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.