Oracle SQL Show all month of a year, with or without value ORA-01841-CodePudding

I have a problem with which I despair, I have data distributed over days, and would like to display this for the entire year in months and once in weeks.

My problem with the months that I get in the select my data displayed (for January, September) but I want that all months for a selected year are displayed, even if they are empty. For this I have made myself a "WITH" (copied) and now try to join this, but get an ORA-01841 error.

And how do I implement the whole construct to display only the weeks.

WITH MONAT_ZAEHLER (MZ) AS 
( 
SELECT 
   TO_CHAR(ADD_MONTHS(TO_DATE('01.2022','MM.YYYY'),LEVEL -1),'Month', 'NLS_DATE_LANGUAGE = GERMAN') AS  GRD_ROW_ID
  FROM
  DUAL
  CONNECT BY LEVEL <= 12
)

SELECT 
  TO_CHAR(GEN_DATUM,'Month', 'NLS_DATE_LANGUAGE = GERMAN') AS  GRD_ROW_ID

, COUNT( DISTINCT CASE
    WHEN LP_BELEGUNG.ART = 1 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS "1"

, COUNT( DISTINCT CASE
    WHEN LP_BELEGUNG.ART = 2 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS "2"

, COUNT( DISTINCT CASE
    WHEN LP_BELEGUNG.ART = 3 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS "3"

, COUNT( DISTINCT CASE
    WHEN LP_BELEGUNG.ART = 99 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS "99"
FROM
  LP_BELEGUNG

FULL OUTER JOIN MONAT_ZAEHLER ON  TRUNC(LP_BELEGUNG.GEN_DATUM, 'Month') = MONAT_ZAEHLER.MZ

WHERE 
  TO_CHAR(GEN_DATUM, 'YYYY') = '2022'

GROUP BY
  TO_CHAR(GEN_DATUM,'Month', 'NLS_DATE_LANGUAGE = GERMAN')

CodePudding user response：

The error is because you're converting the month to a name string in the CTE, then trying to convert it again for the GRD_ROW_ID alias.

The solution is basically the same as your previous question, but now you want the CTE to have one row per month - which you are doing, but you should leave it as a date type in the CTE, not convert it to a string there:

with cte (dt) as (
  select add_months(date '2022-01-01', level - 1)
  from dual
  connect by level <= 12
)

... then convert that actual date value to a string:

SELECT
  TO_CHAR(cte.dt, 'Month', 'NLS_DATE_LANGUAGE = GERMAN') AS GRD_ROW_ID
...

... and outer join to your actual table as before, using a date range:

FROM
  cte
LEFT JOIN
  LP_BELEGUNG 
ON
  LP_BELEGUNG.GEN_DATUM >= cte.dt AND LP_BELEGUNG.GEN_DATUM < add_months(cte.dt, 1)
GROUP BY
  cte.dt
ORDER BY
  cte.dt

... this time looking for values where the the GEN_DATUM is greater than or equal to cte.dt value (again, as before), which is midnight on the first day of the first day of the month; and less than add_months(cte.dt, 1), which is midnight on the first day of the first day of the following month. So for January, that will be >= 2022-01-01 00:00:00 and < 2022-02-01 00:00:00, which is all possible dates and times during that month.

GRD_ROW_ID	ANZAHL_ART_1	ANZAHL_ART_2	ANZAHL_ART_3	ANZAHL_ART_4
Januar	0	0	0	0
Februar	0	0	0	0
März	0	0	0	0
April	0	0	0	0
Mai	0	0	0	0
Juni	0	0	0	0
Juli	0	0	0	0
August	0	0	0	0
September	1	1	1	7
Oktober	0	0	0	0
November	0	0	0	0
Dezember	0	0	0	0

fiddle

To get a row for every week of the year you would do something similar again, but in blocks of 7 days:

with cte (dt) as (
  select date '2022-01-01'   7 * (level - 1)
  from dual
  connect by level <= 53
)
SELECT
  TO_CHAR(cte.dt, 'YYYY-WW') AS GRD_ROW_ID
...
FROM
  cte
LEFT JOIN
  LP_BELEGUNG 
ON
  LP_BELEGUNG.GEN_DATUM >= cte.dt AND LP_BELEGUNG.GEN_DATUM < cte.dt   7
  AND LP_BELEGUNG.GEN_DATUM < add_months(trunc(cte.dt, 'YYYY'), 12)
GROUP BY
  cte.dt
ORDER BY
  cte.dt

which has an extra check in the join to stop it including data from week 53 which is actually in the following year - which I'm guessing you woudl want to do.

fiddle