Peoplesoft App Engine SQL с помощью предложения WITH

Question

Мой движок приложений продолжает возвращать 'фатальную ошибку sql', когда я пытаюсь использовать предложение sql "WITH". Любые другие предложения?

Вот мой sql, который работает в sql dev, но не везет в App Engine...

WITH bj AS ( 
 SELECT FSU.EMPLID 
 ,FSU.EMPL_RCD 
 ,FSU.PLAN_TYPE 
 ,FSU.EFFDT 
 ,FSU.COVERAGE_BEGIN_DT 
 ,FSU.COVERAGE_ELECT 
 ,FSU.COVERAGE_ELECT_DT 
 ,FSU.ANNUAL_PLEDGE 
 ,FSU.COVERAGE_ELECT || LEAD(FSU.COVERAGE_ELECT) OVER (PARTITION BY FSU.EMPLID 
 , FSU.PLAN_TYPE 
  ORDER BY FSU.COVERAGE_ELECT_DT DESC 
  , FSU.COVERAGE_BEGIN_DT DESC) AS CODE 
  FROM PS_FSA_BENEFIT FSU 
 WHERE FSU.COVERAGE_ELECT_DT <= '29-NOV-13' 
   AND FSU.COVERAGE_BEGIN_DT BETWEEN '01-JAN-13' AND '31-DEC-13' 
  ORDER BY FSU.EMPLID, FSU.PLAN_TYPE, FSU.COVERAGE_ELECT_DT DESC, FSU.COVERAGE_BEGIN_DT DESC ) 
 SELECT * 
  FROM bj 
 WHERE bj.CODE IN ('TE', 'WE', 'EE', 'ET', 'EW', 'E') 
   AND bj.COVERAGE_ELECT_DT BETWEEN '16-NOV-13' AND '29-NOV-13' 
   AND bj.EFFDT = ( 
 SELECT MAX(A_ED.EFFDT) 
  FROM bj A_ED 
 WHERE bj.EMPLID = A_ED.EMPLID 
   AND bj.EMPL_RCD = A_ED.EMPL_RCD 
   AND bj.PLAN_TYPE = A_ED.PLAN_TYPE 
   AND A_ED.EFFDT <= SYSDATE) 
  ORDER BY bj.code, bj.emplid

Answer 1

Обнаружена та же проблема в 8.48, но, похоже, недавний выпуск peopletools справился с ней. Какой бы ни была ваша версия, следующий код должен сработать:

 SELECT * 
  FROM ( 
 SELECT FSU.EMPLID 
 ,FSU.EMPL_RCD 
 ,FSU.PLAN_TYPE 
 ,FSU.EFFDT 
 ,FSU.COVERAGE_BEGIN_DT 
 ,FSU.COVERAGE_ELECT 
 ,FSU.COVERAGE_ELECT_DT 
 ,FSU.ANNUAL_PLEDGE 
 ,FSU.COVERAGE_ELECT || LEAD(FSU.COVERAGE_ELECT) OVER (PARTITION BY FSU.EMPLID 
 , FSU.PLAN_TYPE 
  ORDER BY FSU.COVERAGE_ELECT_DT DESC 
  , FSU.COVERAGE_BEGIN_DT DESC) AS CODE 
  FROM PS_FSA_BENEFIT FSU 
 WHERE FSU.COVERAGE_ELECT_DT <= '29-NOV-13' 
   AND FSU.COVERAGE_BEGIN_DT BETWEEN '01-JAN-13' AND '31-DEC-13' 
  ORDER BY FSU.EMPLID, FSU.PLAN_TYPE, FSU.COVERAGE_ELECT_DT DESC, FSU.COVERAGE_BEGIN_DT DESC ) bj 
 WHERE bj.CODE IN ('TE', 'WE', 'EE', 'ET', 'EW', 'E') 
   AND bj.COVERAGE_ELECT_DT BETWEEN '16-NOV-13' AND '29-NOV-13' 
   AND bj.EFFDT = ( 
 SELECT MAX(A_ED.EFFDT) 
  FROM bj A_ED 
 WHERE bj.EMPLID = A_ED.EMPLID 
   AND bj.EMPL_RCD = A_ED.EMPL_RCD 
   AND bj.PLAN_TYPE = A_ED.PLAN_TYPE 
   AND A_ED.EFFDT <= SYSDATE) 
  ORDER BY bj.code, bj.emplid