Geek Answers Handbook

Oracle group and empty result set

I have the following SQL problem.

Scenario:

I have two tables: Change and ChangeTicket. There is a ratio of 1: n. One Change has cero or many changeTickets. No change no change. ChangeTicket has a status (open / closed / ...) The changeTicket field has a field that indicates how many minutes this change has passed.

A Change has a company and a month. There is at most one change per month.

I have to report for this company and for this month, the amount of minutes this change has occurred.

I wrote the following SQL statement.

select nvl(sum(service_req), 0) as SUM_REQ from Change_Ticket, Change where Change.company_id (+) = '0' and Change.month (+)='07' and Change.Id (+) = Change_Ticket.Change_Id

This is normal.

If there are no changes or tickets for this month and company data, then I get a zero value, which is converted to the cero usgin function of NVL.

The problem arises when I want to group information using status. If I add a grup suggestion

 select Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ from Change_Ticket, Change where Change.company_id (+) = '0' and Change.month (+)='07' and Change.Id (+) = Change_Ticket.Change_Id group by Change_Ticket.status

then my result will be empty. I understand that there is no status, and then the result set is somehow consistent, and then an empty result set is returned.

How can I avoid this problem. In this case, I need to report an empty status and cero as the sum.

(BTW, I also tried setting nvl (Change_Ticket.status, 'none') , but it didn't work)

Thank you very much in advance.

Louis

+1
source share
2 answers

I think that in order to achieve what you want, you will need to do this:

 select Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ from Change_Ticket, Change where Change.company_id (+) = '0' and Change.month (+)='07' and Change.Id (+) = Change_Ticket.Change_Id group by Change_Ticket.status union all select '' as STATUS, 0 as SUM_REQ from dual where not exists (select null from Change_ticket)
+2
source

Assuming you have a list of your statuses in a table called statuses :

 SELECT statuses.id, nvl(sum(service_req), 0) as SUM_REQ FROM statuses LEFT JOIN Change ON Change.company_id = '0' AND Change.month = '07' AND Change.status = statuses.id LEFT JOIN Change_Ticket ON Change_Ticket.Change_Id = Change.Id GROUP BY statuses.id

or this (only if you use 8i , this syntax is deprecated in higher versions):

 SELECT statuses.id, nvl(sum(service_req), 0) as SUM_REQ FROM statuses, Change, Change_Ticket WHERE Change.company_id(+) = '0' AND Change.month(+) = '07' AND Change.status(+) = statuses.id AND Change_Ticket.Change_Id(+) = Change.Id GROUP BY statuses.id

If you want to show existing statuses or NULL if there are no entries, use this:

 SELECT statuses.id, nvl(sum(service_req), 0) as SUM_REQ FROM dual LEFT JOIN Change ON Change.company_id = '0' AND Change.month = '07' LEFT JOIN Change_Ticket ON Change_Ticket.Change_Id = Change.Id GROUP BY statuses.id
+1
source

More articles:


All Articles