Kronos SQL Query-ORA-00923: FROM keyword not found where expected

Question

So I'm trying to query Kronos production and I'm coming against the error ORA-00923: "FROM keyword not found where expected ". I've read similar posts and as you can see below, the keyword FROM is the last selected item in the statement.

SELECT  A.NAME AS 'Accrual Profile',
        A3.NAME AS 'Accrual Policy',
       "Hidden Accrual Policy" = CASE 
                                      WHEN A2.DISPLAYSW = 1 THEN 'Not Hidden'
                                      ELSE 'Hidden' 
                                 END,
        A4.NAME AS 'Accrual Code',
        A5.NAME AS 'Overflow Accrual Code',
        D.NAME AS 'Length of Service Reference',
        A3.GRANULARITYAMT AS 'Grant',
        A3.FUTOVERDRFTERRAMT AS 'Future overdrafts - disallow amount',
        A3.FUTOVERDRFTWARNAMT AS 'Future overdrafts - warn amount',
        A3.IMPORTOVERRIDE_SW AS 'Override Warnings during Imports',
        A3.GRPEDITOVERRIDE_SW AS 'Override Warnings during Group Edits',
        A3.MAXTAKENAMOUNT,
        A3.MINTAKENAMOUNT,
        A3.USEFTEBALANCEADJSW,
        A3.USESCHEDAMTTYPESW,
        A3.USETODAYSBALNCE_SW,
        P.NAME AS 'Probation',
        P.TYPE AS 'Probation Type',
        T.NAME AS 'Taking Limit',
        D2.NAME AS 'Taking Limit Date Pattern',
        G.NAME AS 'Grant/Limit Name',
        "Limit or Grant Type" = CASE
                                   WHEN G.GRANTTYPE = 'E' THEN 'Earned Grant'
                                   WHEN G.GRANTTYPE = 'F' THEN 'Fixed Grant'
                                   WHEN G.GRANTTYPE = 'B' THEN 'Earning Balance Limit'
                                   WHEN G.GRANTTYPE = 'A' THEN 'Earning Amount Limit'
                                   WHEN G.GRANTTYPE = 'C' THEN 'Carry Over Limit'
                                   ELSE 'Other/Cap on Earnings?'
                                END,
         D3.NAME AS 'Grant/Limit Date Pattern'
FROM ACCRUALPROFILE A
RIGHT OUTER JOIN ACCRUALPROFILEMM A2 ON A.ACCRUALPROFILEID = A2.ACCRUALPROFILEID
LEFT OUTER JOIN ACCRUALRULE A3 ON A2.ACCRUALRULEID = A3.ACCRUALRULEID
LEFT OUTER JOIN ACCRUALCODE A4 ON A3.ACCRUALCODEID = A4.ACCRUALCODEID
LEFT OUTER JOIN ACCRUALCODE A5 ON A3.OVRFLWACCRLCODEID = A5.ACCRUALCODEID
LEFT OUTER JOIN PROBATIONRULE P ON A3.PROBATIONRULEID = P.PROBATIONRULEID
LEFT OUTER JOIN DATESEQUENCE D ON A3.DATESEQUENCEID = D.DATESEQUENCEID
LEFT OUTER JOIN ACCRRLTAKELIMITMM A6 ON A3.ACCRUALRULEID = A6.ACCRUALRULEID
LEFT OUTER JOIN TAKINGLIMIT T ON A6.TAKINGLIMITID = T.TAKINGLIMITID
LEFT OUTER JOIN DATESEQUENCE D2 ON T.DATESEQUENCEID = D2.DATESEQUENCEID
LEFT OUTER JOIN ACCRRLGRANTRLMM A7 ON A3.ACCRUALRULEID = A7.ACCRUALRULEID
LEFT OUTER JOIN GRANTRULE G ON A7.GRANTRULEID = G.GRANTRULEID
LEFT OUTER JOIN DATESEQUENCE D3 ON G.DATESEQUENCEID = D3.DATESEQUENCEID

Moreover, I tried another solution by changing the enclosed alias in double quotation marks to see if that would work but that produced another set of errors. Lastly, I don't think I'm using any Oracle reserved words as an alias. I was hoping someone could check my code to see where I have got it wrong.

The column alias for your `CASE` statements should be after the `END`, not `"Limit or Grant Type" = CASE ...`. — Filburt, Mar 09 '22 at 20:55
You need to double-quote your aliases, not single quote them. — Andrew, Mar 09 '22 at 20:59

score 0 · Answer 1 · answered Mar 10 '22 at 20:59

So the fix involved replacing single quotes with double quotes and then fixing the CaseStatement.


SELECT  A.NAME AS "Accrual Profile",
        A3.NAME AS "Accrual Policy",
       CASE 
           WHEN A2.DISPLAYSW = 1 THEN 'Not Hidden'
           ELSE 'Hidden' 
       END,
        A4.NAME AS "Accrual Code",
        A5.NAME AS "Overflow Accrual Code",
        D.NAME AS "Length of Service Reference",
        A3.GRANULARITYAMT AS "Grant",
        A3.FUTOVERDRFTERRAMT AS "Future overdrafts - disallow amount",
        A3.FUTOVERDRFTWARNAMT AS "Future overdrafts - warn amount",
        A3.IMPORTOVERRIDE_SW AS "Override Warnings during Imports",
        A3.GRPEDITOVERRIDE_SW AS "Override Warnings during Group Edits",
        A3.MAXTAKENAMOUNT,
        A3.MINTAKENAMOUNT,
        A3.USEFTEBALANCEADJSW,
        A3.USESCHEDAMTTYPESW,
        A3.USETODAYSBALNCE_SW,
        P.NAME AS "Probation",
        P.TYPE AS "Probation Type",
        T.NAME AS "Taking Limit",
        D2.NAME AS "Taking Limit Date Pattern",
        G.NAME AS "Grant/Limit Name",
       CASE
            WHEN G.GRANTTYPE = 'E' THEN 'Earned Grant'
            WHEN G.GRANTTYPE = 'F' THEN 'Fixed Grant'
            WHEN G.GRANTTYPE = 'B' THEN 'Earning Balance Limit'
            WHEN G.GRANTTYPE = 'A' THEN 'Earning Amount Limit'
            WHEN G.GRANTTYPE = 'C' THEN 'Carry Over Limit'
            ELSE 'Other/Cap on Earnings?'
        END,
         D3.NAME AS "Grant/Limit Date Pattern"
FROM ACCRUALPROFILE A
RIGHT OUTER JOIN ACCRUALPROFILEMM A2 ON A.ACCRUALPROFILEID = A2.ACCRUALPROFILEID
LEFT OUTER JOIN ACCRUALRULE A3 ON A2.ACCRUALRULEID = A3.ACCRUALRULEID
LEFT OUTER JOIN ACCRUALCODE A4 ON A3.ACCRUALCODEID = A4.ACCRUALCODEID
LEFT OUTER JOIN ACCRUALCODE A5 ON A3.OVRFLWACCRLCODEID = A5.ACCRUALCODEID
LEFT OUTER JOIN PROBATIONRULE P ON A3.PROBATIONRULEID = P.PROBATIONRULEID
LEFT OUTER JOIN DATESEQUENCE D ON A3.DATESEQUENCEID = D.DATESEQUENCEID
LEFT OUTER JOIN ACCRRLTAKELIMITMM A6 ON A3.ACCRUALRULEID = A6.ACCRUALRULEID
LEFT OUTER JOIN TAKINGLIMIT T ON A6.TAKINGLIMITID = T.TAKINGLIMITID
LEFT OUTER JOIN DATESEQUENCE D2 ON T.DATESEQUENCEID = D2.DATESEQUENCEID
LEFT OUTER JOIN ACCRRLGRANTRLMM A7 ON A3.ACCRUALRULEID = A7.ACCRUALRULEID
LEFT OUTER JOIN GRANTRULE G ON A7.GRANTRULEID = G.GRANTRULEID
LEFT OUTER JOIN DATESEQUENCE D3 ON G.DATESEQUENCEID = D3.DATESEQUENCEID

Kronos SQL Query-ORA-00923: FROM keyword not found where expected

1 Answers1