PL/SQL ORA-00933: SQL COMMAND NOT PROPERLY ENDED

Question

Referring to this Post 'AS' is not supported, what would be the substitution syntax to implement my with SQL command?

CREATE OR REPLACE PROCEDURE "LCAccountSP" (
    xReceiptType IN VARCHAR2,
    xManual IN VARCHAR2,
    xRCO IN VARCHAR2,
    xMuncID IN VA) AS
BEGIN
    CASE        
        WHEN xReceiptType = 'ROR' THEN 
            SELECT xAccount, xCount, xAmount 
            FROM (
                SELECT 
                    b.ACCT_CODE AS xAccount, 
                    COUNT(*) AS xCount, 
                    SUM(b.ROR_TOTAL_PAID) as xAmount 
                FROM tbl_tax_payment aa
                    INNER JOIN tbl_revenue_official_receipt b
                        ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE
                    aa.RECEIPT_TYPE = xReceiptType
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY xAccount
            ) AS xAccount;

        WHEN xReceiptType = 'OR' THEN 
            SELECT xAccount, xCount, xAmount 
            FROM (
                SELECT b.ACCT_CODE AS xAccount,
                    COUNT(*) AS xCount,
                    SUM(b.OR_TOTAL_PAID) as xAmount  
                FROM tbl_tax_payment aa 
                    INNER JOIN tbl_official_receipt b
                    ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE aa.RECEIPT_TYPE = xReceiptType 
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY xAccount
            ) AS xAccount;

        WHEN xReceiptType = 'AR' THEN 

            SELECT xAccount, xCount, xAmount
            FROM (
                SELECT b.ACCT_CODE AS xAccount,
                    COUNT(*) AS xCount,
                    SUM(b.AR_TOTAL_PAID) as xAmount  
                FROM tbl_tax_payment aa
                    INNER JOIN tbl_ack_receipt b 
                        ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE aa.RECEIPT_TYPE = xReceiptType 
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY xAccount
            ) AS xAccount;
    END CASE;
END;

Just saw `xMuncID IN VA` I though I did this mistake, when I edited question. But when I checked my edit history I saw this issue in original code, so please check your code with proper parameter list also. — Alexander Myshov, Dec 06 '13 at 03:47
yes i also edited that.. regarding my question.. the GROUP BY xAccount from my Query i not recognized as it is from the alias.. — DreamBigAlvin, Dec 06 '13 at 03:50
Yes It is not possible to use pseudonym for `group by` in the same query, but it is possible to use it after subquerying it. Anyway check my code below I correct it and it seems that code should compile. — Alexander Myshov, Dec 06 '13 at 04:05

Alexander Myshov · Accepted Answer · 2013-12-06T04:01:57.417

You need delete only AS when you define pseudonyms of tables. Defining pseudonyms of columns with AS is normal. Check this example please http://sqlfiddle.com/#!4/d41d8/21775

Update also correct parameter of procedure xMuncID IN VA to xMuncID IN VARCHAR2

Try to invoke this code:

CREATE OR REPLACE PROCEDURE "LCAccountSP" (
    xReceiptType IN VARCHAR2,
    xManual IN VARCHAR2,
    xRCO IN VARCHAR2,
    xMuncID IN VARCHAR2)
AS
    -- please check that datatypes is suite for your needs
    v_xAccount varchar2(100);
    v_xCount number;
    v_xAmount number;
BEGIN
    CASE        
        WHEN xReceiptType = 'ROR' THEN 
            SELECT xAccount, xCount, xAmount 
            INTO v_xAccount, v_xCount, v_xAmount 
            FROM (
                SELECT 
                    b.ACCT_CODE AS xAccount, 
                    COUNT(*) AS xCount, 
                    SUM(b.ROR_TOTAL_PAID) as xAmount 
                FROM tbl_tax_payment aa
                    INNER JOIN tbl_revenue_official_receipt b
                        ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE
                    aa.RECEIPT_TYPE = xReceiptType
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY b.ACCT_CODE
            ) xAccount;

        WHEN xReceiptType = 'OR' THEN 
            SELECT xAccount, xCount, xAmount 
            INTO v_xAccount, v_xCount, v_xAmount 
            FROM (
                SELECT b.ACCT_CODE AS xAccount,
                    COUNT(*) AS xCount,
                    SUM(b.OR_TOTAL_PAID) as xAmount  
                FROM tbl_tax_payment aa 
                    INNER JOIN tbl_official_receipt b
                    ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE aa.RECEIPT_TYPE = xReceiptType 
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY b.ACCT_CODE
            ) xAccount;

        WHEN xReceiptType = 'AR' THEN 

            SELECT xAccount, xCount, xAmount
            INTO v_xAccount, v_xCount, v_xAmount 
            FROM (
                SELECT b.ACCT_CODE AS xAccount,
                    COUNT(*) AS xCount,
                    SUM(b.AR_TOTAL_PAID) as xAmount  
                FROM tbl_tax_payment aa
                    INNER JOIN tbl_ack_receipt b 
                        ON aa.TPAY_RECEIPT_NO = b.TPAY_RECEIPT_NO 
                WHERE aa.RECEIPT_TYPE = xReceiptType 
                    AND aa.RECEIPT_MODE = xManual 
                    AND aa.LOC_NO IS NULL 
                    AND aa.RCO_CODE = xRCO 
                    AND aa.MUNC_ID = xMuncID 
                GROUP BY b.ACCT_CODE
            ) xAccount;
    END CASE;
END;

regarding my question.. the GROUP BY xAccount from my Query i not recognized as it is from the alias.. — DreamBigAlvin, Dec 06 '13 at 03:50

PL/SQL ORA-00933: SQL COMMAND NOT PROPERLY ENDED

1 Answers1