SQL для Peoplesoft App Engine с использованием предложения WITH

Мой механизм приложения продолжает возвращать «фатальную ошибку 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

sql google-app-engine peoplesoft
person user3203601    schedule 16.01.2014    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Столкнулся с той же проблемой в 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
person Telkarion    schedule 16.01.2014
comment
Я в 8.49. Теперь он дает мне таблицу или представление не существует ошибки ... какие-либо другие предложения? - person user3203601; 16.01.2014