1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora 923 or ORA904

Discussion in 'SQL PL/SQL' started by mtiller, Dec 19, 2012.

  1. mtiller

    mtiller Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Hi All,

    I'm running against an Oracle 11g database.

    I'm trying to get totals for the day for our different merchant aquirers. As displayed I get an 923 error FROM keyword not found where expected. If I remove the quotes around the word Merch on line 6 then I get Ora 904 invalid identifier.

    Any suggestions gratefully accepted!

    thanks in advance.

    Mark


    SELECT
    Case
    When PAYM.REFERENCENO like '37%' Then 'Amex'
    When PAYM.REFERENCENO like '36%' Then 'Diners'
    Else 'Barclays'
    END 'Merch',
    SUM(PAYM.AMOUNT/100) Amount
    FROM
    CDSPROD1.TDM_PAYMENTS PAYM
    WHERE
    PAYM.REFERENCENO <> '?' AND
    CPAYM.DAYKEY=12006
    GROUP BY
    Merch
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    When we remove quotes its working fine. Where you are trying to run this query like SQL*Plus, or any other tool?
     
  3. mtiller

    mtiller Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Hi Bharat,

    thanks for getting back to me. I'm running it in Business Objects ver 5.0.3 using Hand Coded SQL
     
  4. mtiller

    mtiller Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Ah OK, when it's like this, the MERCH it's complaining about is the one on the GROUP BY clause.

    SELECT
    Case
    When PAYM.REFERENCENO like '37%' Then 'Amex'
    When PAYM.REFERENCENO like '36%' Then 'Diners'
    Else 'Barclays'
    END MERCH,
    SUM(PAYM.AMOUNT/100) Amount
    FROM
    CDSPROD1.TDM_PAYMENTS PAYM
    WHERE
    PAYM.REFERENCENO <> '?' AND
    PAYM.DAYKEY=12006
    GROUP BY
    MERCH
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Use PAYM.REFERENCENO in group by clause. By that the issue can be solved. Its not supposed to accept the alias names of columns in group by clause.
     
  6. mtiller

    mtiller Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Thanks Bharat, I changed it to use a subquery which works and gives me 3 lines of output which is what I want. So:

    SELECT
    Merch,
    SUM(Amt)/100
    FROM
    (SELECT
    (Case
    When PAYM.REFERENCENO like '37%' Then 'Amex'
    When PAYM.REFERENCENO like '36%' Then 'Diners'
    Else 'Barclays'
    END) Merch,
    PAYM.AMOUNT Amt
    FROM
    CDSPROD1.TDM_PAYMENTS PAYM
    WHERE
    PAYM.REFERENCENO <> '?' AND
    PAYM.DAYKEY=12006)
    GROUP BY
    Merch
     
    Bharat likes this.