chand pb
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.
sub query result
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
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
Thanks in advance for the help
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
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'
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
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;
Thanks in advance for the help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do your dates have a time component? If they do, do the times match?
>>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;"
Yes, they have 'time'. They showed the trigger code ":new.modified_date := sysdate;"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.