1. Vertabelo
  2. Errors
0
Under review

Error in view builder using LEAD function

Kent Graziano fa 9 anys • updated by Jack Polkowski fa 9 anys • 3
When I try to create a view using this SQL I get a parsing error:
SELECT
dbms_obfuscation_toolkit.md5(upper(trim(HC.COUNTRY_ABBRV))
|| '^'
|| TO_CHAR(SC.SAT_Load_DTS, 'YYYY-MM-DD')) AS DIM2_COUNTRY_KEY,
HC.COUNTRY_ABBRV,
SC.COUNTRY_NAME,
SC.SAT_Load_DTS AS EFFECTIVE_DTS,
LEAD(SC.SAT_Load_DTS) OVER (PARTITION BY SC.HUB_COUNTRY_KEY ORDER BY
SC.SAT_Load_DTS) AS EXPIRE_DTS,
SC.SAT_Rec_SRC AS REC_SRC
FROM
HUB_COUNTRY HC,
SAT_COUNTRIES SC
WHERE
HC.HUB_COUNTRY_KEY = SC.HUB_COUNTRY_KEY
Kent Graziano fa 9 anys
Sorry this should be a BUG.
Kent Graziano fa 9 anys
Here is another example. If I remove the last section (in bold) then it works fine.

SELECT
PIT.HUB_EMPLOYEE_KEY AS DIM1_EMPLOYEE_KEY,
PIT.PIT_LOAD_DTS AS EFFECTIVE_DATE,
HE.EMPLOYEE_ID,
NM.FIRST_NAME,
NM.LAST_NAME,
NM.GENDER,
NM.MARITAL_STATUS,
NM.SSN,
ADDR.ADDRESS1,
ADDR.ADDRESS2,
ADDR.CITY,
ADDR.STATE,
ADDR.POSTAL_CODE,
ADDR.COUNTRY,
CNTC.EMAIL,
CNTC.PHONE_NUMBER,
COMP.SALARY_BEGIN_DATE,
COMP.SALARY,
COMP.COMMISSION_PCT,
SAL.MIN_SALARY,
SAL.MAX_SALARY,
SAL.AVG_SALARY,
SAL.TOTAL_SALARY_TODATE
FROM
HUB_EMPLOYEE HE,
EMPLOYEE_PIT PIT,
SAT_EMPLOYEE_ADDRESS ADDR,
SAT_EMPLOYEE_NAME NM,
SAT_EMPLOYEE_CONTACTS CNTC,
SAT_EMPLOYEE_COMPENSATION COMP,
SAT_EMPLOYEE_SAL_SUM SAL
WHERE
HE.HUB_EMPLOYEE_KEY = PIT.HUB_EMPLOYEE_KEY
AND HE.HUB_EMPLOYEE_KEY = ADDR.HUB_EMPLOYEE_KEY
AND HE.HUB_EMPLOYEE_KEY = NM.HUB_EMPLOYEE_KEY
AND HE.HUB_EMPLOYEE_KEY = CNTC.HUB_EMPLOYEE_KEY
AND HE.HUB_EMPLOYEE_KEY = COMP.HUB_EMPLOYEE_KEY
AND HE.HUB_EMPLOYEE_KEY = SAL.HUB_EMPLOYEE_KEY
AND PIT.SAL_SUM_LOAD_DTS = SAL.SAT_LOAD_DTS
AND PIT.NAME_LOAD_DTS = NM.SAT_Load_DTS
AND PIT.ADDRESS_LOAD_DTS = ADDR.SAT_Load_DTS
AND PIT.CONTACT_LOAD_DTS = CNTC.SAT_Load_DTS
AND PIT.COMPENSATION_LOAD_DTS = COMP.SAT_Load_DTS
AND PIT.PIT_LOAD_DTS =
(
SELECT
MAX(PIT2.PIT_LOAD_DTS)
FROM
EMPLOYEE_PIT PIT2
WHERE
HE.HUB_EMPLOYEE_KEY = PIT2.HUB_EMPLOYEE_KEY
)
+1
Under review
anonymous fa 9 anys
Yes, we know about this limitation. SQL parser for views is quite simple and it parses only basic queries. We'll need to work a bit more on this in the future.

Customer support service by UserEcho