Hi!
I created this sample code, I use this in badge list and I want to return 0(when no data found) for my_projects:
select count(tasks) my_tasks,
max((select count(created_by) my_projects
from projects
where created_by = :USER_ID)) as my_rec
FROM tasks WHERE asignee = :USER_ID
Thanks in advance
Vinish Kapoor
Your query for max column seems to be fine. It will return 0, when no data found. But if the parent query does not return any rows then it will not work.
pavlos
This code return null, only for first column "my_tasks"
Vinish Kapoor
Yes, because your main query (From Tasks) is not returning any rows.
VFP George
If the query returns no records could you wrap into NVL(your result,0) ?
pavlos
I tried, but I can't get 0 value in badge list.
afzal
WITH TBL AS(select count(tasks) my_tasks,
max((select count(created_by) my_projects
from projects
where created_by = :USER_ID)) as my_rec
FROM tasks WHERE asignee = :USER_ID
UNION ALL
SELECT count(tasks) my_tasks,0 AS my_projects
FROM tasks WHERE asignee = :USER_ID
)
SELECT my_tasks, SUM(my_projects) AS my_projects
FROM TBL
GROUP BY my_tasks