ORA-00920:无效的关系操作员从Dual插入

Question

INSERT INTO DIM_TEMPS (ID_DATE, DATE_DU_JOUR, ANNEE_CALENDAIRE,
  SEMESTRE, LIBELLE_SEMESTRE, TRIMESTRE, LIBELLE_TRIMESTRE,
  ANNEE_MOIS,MOIS, LIBELLE_MOIS, SEMAINE, JOUR, LIBELLE_JOUR,
  JOUR_FERIE, JOUR_OUVRE, QUANTIEME_JOUR)
SELECT TO_NUMBER(TO_CHAR(DT_CAL, "YYYYMMDD")) AS ID_CALENDRIER,
  DT_CAL AS DATE_DU_JOUR,
  TO_NUMBER(TO_CHAR(DT_CAL, "YYYY")) AS ANNEE_CALENDAIRE,
  ROUND(TO_NUMBER(TO_CHAR(DT_CAL, "Q"))/2) AS SEMESTRE,
  CASE ROUND(TO_NUMBER(TO_CHAR(DT_CAL, "Q"))/2)
    WHEN 1 THEN "1er semestre"
    ELSE "2ème semestre" END AS LIBELLE_SEMESTRE,
  TO_NUMBER(TO_CHAR(DT_CAL, "Q")) AS TRIMESTRE,
  CASE TO_NUMBER(TO_CHAR(DT_CAL, "Q"))
    WHEN 1 THEN "1er trimestre"
    ELSE TO_NUMBER(TO_CHAR(DT_CAL, "Q")) || "ème trimestre"
    END AS LIBELLE_TRIMESTRE,
  TO_NUMBER(TO_NUMBER(TO_CHAR(DT_CAL, "YYYY"))
    || LPAD(TO_CHAR(DT_CAL, "MM"), 2, "0")) AS ANNEE_MOIS,
  TO_NUMBER(TO_CHAR(DT_CAL, "MM")) AS MOIS,
  TO_CHAR(DT_CAL, "Month") AS LIBELLE_MOIS,
  TO_NUMBER(TO_CHAR(DT_CAL, "IW")) AS SEMAINE,
  TO_NUMBER(TO_CHAR(DT_CAL, "DD")) AS JOUR,
  TO_CHAR(DT_CAL, "Day") AS LIBELLE_JOUR,
  CASE WHEN TO_CHAR(DT_CAL, "D") IN ("6", "7")
    THEN "Oui" ELSE "Non" END AS JOUR_FERIE,
  CASE WHEN TO_CHAR(DT_CAL, "D") IN ("6", "7")
    THEN "Non" ELSE "Oui" END AS JOUR_OUVRE,
  NUM_JOUR AS QUANTIEME_JOUR
FROM (
  SELECT to_date("19000101","YYYYMMDD") + (rownum - 1) AS DT_CAL,
    rownum AS NUM_JOUR
  FROM dual connect BY to_date("19000101","YYYYMMDD") + (rownum - 1)
);

Answer 1

问题是您在（大部分）字符串文字周围使用了双引号。通常会导致"标识符"错误，但在这里它们使解析器更加困惑。

只需将它们全部更改为单引号：

INSERT INTO DIM_TEMPS (ID_DATE, DATE_DU_JOUR, ANNEE_CALENDAIRE,
  SEMESTRE, LIBELLE_SEMESTRE, TRIMESTRE, LIBELLE_TRIMESTRE,
  ANNEE_MOIS,MOIS, LIBELLE_MOIS, SEMAINE, JOUR, LIBELLE_JOUR,
  JOUR_FERIE, JOUR_OUVRE, QUANTIEME_JOUR)
SELECT TO_NUMBER(TO_CHAR(DT_CAL, 'YYYYMMDD')) AS ID_CALENDRIER,
  DT_CAL AS DATE_DU_JOUR,
  TO_NUMBER(TO_CHAR(DT_CAL, 'YYYY')) AS ANNEE_CALENDAIRE,
  ROUND(TO_NUMBER(TO_CHAR(DT_CAL, 'Q'))/2) AS SEMESTRE,
  CASE ROUND(TO_NUMBER(TO_CHAR(DT_CAL, 'Q'))/2)
    WHEN 1 THEN '1er semestre'
    ELSE '2ème semestre' END AS LIBELLE_SEMESTRE,
  TO_NUMBER(TO_CHAR(DT_CAL, 'Q')) AS TRIMESTRE,
  CASE TO_NUMBER(TO_CHAR(DT_CAL, 'Q'))
    WHEN 1 THEN '1er trimestre'
    ELSE TO_NUMBER(TO_CHAR(DT_CAL, 'Q')) || 'ème trimestre'
    END AS LIBELLE_TRIMESTRE,
  TO_NUMBER(TO_NUMBER(TO_CHAR(DT_CAL, 'YYYY'))
    || LPAD(TO_CHAR(DT_CAL, 'MM'), 2, '0')) AS ANNEE_MOIS,
  TO_NUMBER(TO_CHAR(DT_CAL, 'MM')) AS MOIS,
  TO_CHAR(DT_CAL, 'Month') AS LIBELLE_MOIS,
  TO_NUMBER(TO_CHAR(DT_CAL, 'IW')) AS SEMAINE,
  TO_NUMBER(TO_CHAR(DT_CAL, 'DD')) AS JOUR,
  TO_CHAR(DT_CAL, 'Day') AS LIBELLE_JOUR,
  CASE WHEN TO_CHAR(DT_CAL, 'D') IN ('6', '7')
    THEN 'Oui' ELSE 'Non' END AS JOUR_FERIE,
  CASE WHEN TO_CHAR(DT_CAL, 'D') IN ('6', '7')
    THEN 'Non' ELSE 'Oui' END AS JOUR_OUVRE,
  NUM_JOUR AS QUANTIEME_JOUR
FROM (
  SELECT to_date('19000101','YYYYMMDD') + (rownum - 1) AS DT_CAL,
    rownum AS NUM_JOUR
  FROM dual connect BY to_date('19000101','YYYYMMDD') + (rownum - 1)
    <= to_date('29991231','YYYYMMDD')
);

您可以在各个地方简化这一点，但现在将生成数据，然后插入（很多）行。