Good morning,
I wonder if any of you could help with a query. It look like a simple query. Still,I can't find a way to handle it.
The scenario is like this;
I have a table with a date and a type and a value.
CREATE TABLE logs
(
log_id NUMBER,
log_date DATE,
log_type VARCHAR2(1000),
value NUMBER
);
INSERT INTO logs(log_id, log_date, log_type, value) VALUES (1, TO_DATE('01/01/2025', 'DD/MM/YYYY'), 'info', 1);
INSERT INTO logs(log_id, log_date, log_type, value) VALUES (2, TO_DATE('01/01/2025', 'DD/MM/YYYY'), 'warn', 2);
INSERT INTO logs(log_id, log_date, log_type, value) VALUES (3, TO_DATE('02/01/2025', 'DD/MM/YYYY'), 'info', 1);
INSERT INTO logs(log_id, log_date, log_type, value) VALUES (4, TO_DATE('03/01/2025', 'DD/MM/YYYY'), 'warn', 1);
Every now and then, we insert a record in that table. But not for all the types. But, when I query I would like to have FOR EACH log date, ALL the possible log_types and if they are missing, have a record with 0 instead.
In this sample data, I would like to have:
log_date log_type value
------------ --------- ---------
01/01/2025 info 1
01/01/2025 warn 2
02/01/2025 info 1
02/01/2025 warn 0
03/01/2025 info 0
03/01/2025 warn 1
In the reality, I have a more complex structure with a date every minute. And I have about 20 types. But, I simplified as much as possible to make the question easy to understand.
If you need more details, feel free to ask. I'll be happy to complement.
I'm using Oracle 19c.
Regards,