Link to home
Start Free TrialLog in
Avatar of chand pb
chand pbFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do your dates have a time component? If they do, do the times match?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>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;"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial