9 Replies Latest reply on Jun 14, 2011 10:38 AM by 858930

    ORA-00904 invalid identifier [nested select problem]

    858930
      SELECT DISTINCT BATCHDETAIL.BATCHNO ,
      BATCHDETAIL.CUSTOMERID CUSTCODE ,
      CUSTDATA.customerName CUSTNAME ,
      CUSTDATA.mailFlag ,
      veh.ENGINENO ,
      veh.COLOR carColor ,

      ( SELECT BATCHACTIVITY.RETURNMAILREASON
      FROM T_CIMS_BATCH_ACTIVITIES BATCHACTIVITY
      WHERE BATCHACTIVITY.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
      AND BATCHACTIVITY.COMMODE = 'MAIL' ) RETURNMAILREASON,

      ( SELECT *
      FROM ( SELECT BATCHACTIVITYRESENT.RETURNMAILREASON
      FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
      *WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID*
      AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
      ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC )
      WHERE ROWNUM <= 1 ) RESENTMAILREASON


      FROM ( T_CIMS_CUSTOMER_VEHICLE_INFO veh LEFT JOIN T_CIMS_FAMILY_MODEL_RELATION rel ON rel.MODELTYPECODE = veh.MODELTYPECODE)
      RIGHT JOIN T_CIMS_BATCH_DETAIL BATCHDETAIL
      LEFT JOIN V_CIMS_BATCH_CUSTOMER CUSTDATA ON BATCHDETAIL.CUSTOMERID = CUSTDATA.customerCode
      ON veh.VEHICLEID = BATCHDETAIL.VEHICLEID
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Error at Command Line:16 Column:64
      Error report:
      SQL Error: ORA-00904: "BATCHDETAIL"."BATCHDETAILID": invalid identifier
      00904. 00000 - "%s: invalid identifier"
      *Cause:   
      *Action:
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Some help to fix this. this is actually taken from a View converted from MSSQL via SQL Developer. The original code in mssql is

      (SELECT TOP 1 BATCHACTIVITYRESENT.RETURNMAILREASON
      FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
      WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
      ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC) AS RESENTMAILREASON,

      as you can see mssql uses top 1 while oracle uses

      select * from (
      (select x from y)
      where rownum <= 1)

      Is the problem caused by Double inner select and thus couldn`t read BATCHDETAIL.CUSTOMERID?
      Any help or guidance would be appreciated. * i have tried first_value. but doesn`t seem to work.

      Edited by: DominicNg85 on Jun 8, 2011 4:02 AM

      Edited by: DominicNg85 on Jun 8, 2011 4:02 AM

      Edited by: DominicNg85 on Jun 8, 2011 4:02 AM
        • 1. Re: ORA-00904 invalid identifier [nested select problem]
          Om
          Hi,

          Yes that error is coming due to, in your select part you have double inner select. you have to convert in single inner select or use BATCHDETAIL table in outer select and put join condition with from clause BATCHDETAIL table.

          you can also try with max or min function instead of rownum.

          ( SELECT max(BATCHACTIVITYRESENT.RETURNMAILREASON)
          FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
          WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
          AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
          ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC )
          1 person found this helpful
          • 2. Re: ORA-00904 invalid identifier [nested select problem]
            858930
            hey i tried that max function but it`s giving me

            SQL Error: ORA-00907: missing right parenthesis
            00907. 00000 - "missing right parenthesis"

            i did some lookup on site and it seems like you can`t max with order by. damn

            any other workaround
            • 3. Re: ORA-00904 invalid identifier [nested select problem]
              867681
              Hi,
              There must be some thing else you r missing because of which u r getting error as "ORA-00907: missing right parenthesis".
              I have used Max with Order by many times and gives correct output.

              Paste your exact query which is giving 00907 error
              • 4. Re: ORA-00904 invalid identifier [nested select problem]
                858930
                SELECT DISTINCT BATCHDETAIL.BATCHNO ,
                BATCHDETAIL.CUSTOMERID CUSTCODE ,
                CUSTDATA.customerName CUSTNAME ,
                CUSTDATA.mailFlag ,
                veh.ENGINENO ,
                veh.COLOR carColor ,

                +( SELECT BATCHACTIVITY.RETURNMAILREASON+
                FROM T_CIMS_BATCH_ACTIVITIES BATCHACTIVITY
                WHERE BATCHACTIVITY.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                AND BATCHACTIVITY.COMMODE = 'MAIL' ) RETURNMAILREASON,

                +( SELECT max(BATCHACTIVITYRESENT.RETURNMAILREASON)+
                FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC ) RETURNMAILREASON

                +/*+
                +( SELECT *+
                FROM ( SELECT BATCHACTIVITYRESENT.RETURNMAILREASON
                FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC )
                WHERE ROWNUM <= 1 ) RESENTMAILREASON
                */
                FROM ( T_CIMS_CUSTOMER_VEHICLE_INFO veh LEFT JOIN T_CIMS_FAMILY_MODEL_RELATION rel ON rel.MODELTYPECODE = veh.MODELTYPECODE)
                RIGHT JOIN T_CIMS_BATCH_DETAIL BATCHDETAIL
                LEFT JOIN V_CIMS_BATCH_CUSTOMER CUSTDATA ON BATCHDETAIL.CUSTOMERID = CUSTDATA.customerCode
                ON veh.VEHICLEID = BATCHDETAIL.VEHICLEID

                this is the full SQL
                • 5. Re: ORA-00904 invalid identifier [nested select problem]
                  Vivek L
                  problem is with the order by clause in scalar subquery:
                  SELECT DISTINCT BATCHDETAIL.BATCHNO ,
                  BATCHDETAIL.CUSTOMERID CUSTCODE ,
                  CUSTDATA.customerName CUSTNAME ,
                  CUSTDATA.mailFlag ,
                  veh.ENGINENO ,
                  veh.COLOR carColor ,

                  +( SELECT BATCHACTIVITY.RETURNMAILREASON+
                  FROM T_CIMS_BATCH_ACTIVITIES BATCHACTIVITY
                  WHERE BATCHACTIVITY.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                  AND BATCHACTIVITY.COMMODE = 'MAIL' ) RETURNMAILREASON,

                  +( SELECT max(BATCHACTIVITYRESENT.RETURNMAILREASON)+
                  FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                  WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                  AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                  ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC ) RETURNMAILREASON
                  remove it and check whether it works.

                  Vivek L
                  • 6. Re: ORA-00904 invalid identifier [nested select problem]
                    860443
                    DominicNg85 wrote:
                    SELECT DISTINCT BATCHDETAIL.BATCHNO ,
                    BATCHDETAIL.CUSTOMERID CUSTCODE ,
                    CUSTDATA.customerName CUSTNAME ,
                    CUSTDATA.mailFlag ,
                    veh.ENGINENO ,
                    veh.COLOR carColor ,

                    +( SELECT BATCHACTIVITY.RETURNMAILREASON+
                    FROM T_CIMS_BATCH_ACTIVITIES BATCHACTIVITY
                    WHERE BATCHACTIVITY.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITY.COMMODE = 'MAIL' ) RETURNMAILREASON,

                    +( SELECT max(BATCHACTIVITYRESENT.RETURNMAILREASON)+
                    FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                    WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                    ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC ) RETURNMAILREASON

                    +/*+
                    +( SELECT *+
                    FROM ( SELECT BATCHACTIVITYRESENT.RETURNMAILREASON
                    FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                    WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                    ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC )
                    WHERE ROWNUM <= 1 ) RESENTMAILREASON
                    */
                    FROM ( T_CIMS_CUSTOMER_VEHICLE_INFO veh LEFT JOIN T_CIMS_FAMILY_MODEL_RELATION rel ON rel.MODELTYPECODE = veh.MODELTYPECODE)
                    RIGHT JOIN T_CIMS_BATCH_DETAIL BATCHDETAIL
                    LEFT JOIN V_CIMS_BATCH_CUSTOMER CUSTDATA ON BATCHDETAIL.CUSTOMERID = CUSTDATA.customerCode
                    ON veh.VEHICLEID = BATCHDETAIL.VEHICLEID

                    this is the full SQL
                    ORDER BY clause is not allowed in scalar sub query...try this
                    SELECT DISTINCT BATCHDETAIL.BATCHNO ,
                    BATCHDETAIL.CUSTOMERID CUSTCODE ,
                    CUSTDATA.customerName CUSTNAME ,
                    CUSTDATA.mailFlag ,
                    veh.ENGINENO ,
                    veh.COLOR carColor ,
                    ( SELECT BATCHACTIVITY.RETURNMAILREASON
                    FROM T_CIMS_BATCH_ACTIVITIES BATCHACTIVITY
                    WHERE BATCHACTIVITY.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITY.COMMODE = 'MAIL' ) RETURNMAILREASON,
                    ( SELECT max(BATCHACTIVITYRESENT.RETURNMAILREASON)
                    FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                    WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                    --ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC
                    ) RETURNMAILREASON
                    /*
                    ( SELECT *
                    FROM ( SELECT BATCHACTIVITYRESENT.RETURNMAILREASON
                    FROM T_CIMS_BATCH_ACTIVITIES_RESENT BATCHACTIVITYRESENT
                    WHERE BATCHACTIVITYRESENT.BATCHDETAILID = BATCHDETAIL.BATCHDETAILID
                    AND BATCHACTIVITYRESENT.COMMODE = 'MAIL'
                    ORDER BY BATCHACTIVITYRESENT.EXECUTEDDATETIME DESC )
                    WHERE ROWNUM <= 1 ) RESENTMAILREASON
                    */
                    FROM ( T_CIMS_CUSTOMER_VEHICLE_INFO veh LEFT JOIN T_CIMS_FAMILY_MODEL_RELATION rel ON rel.MODELTYPECODE = veh.MODELTYPECODE)
                    RIGHT JOIN T_CIMS_BATCH_DETAIL BATCHDETAIL
                    LEFT JOIN V_CIMS_BATCH_CUSTOMER CUSTDATA ON BATCHDETAIL.CUSTOMERID = CUSTDATA.customerCode
                    ON veh.VEHICLEID = BATCHDETAIL.VEHICLEID
                    • 7. Re: ORA-00904 invalid identifier [nested select problem]
                      858930
                      i need that order by as it supposed to get the latest record.
                      • 8. Re: ORA-00904 invalid identifier [nested select problem]
                        Vivek L
                        DominicNg85 wrote:
                        i need that order by as it supposed to get the latest record.
                        Hi Dominic,

                        max(BATCHACTIVITYRESENT.RETURNMAILREASON) will always give you same record irrespective of the order of fetch.

                        here is an example
                        SQL> With Data As
                          2  (Select  200 Sal From Dual Union All
                          3  Select 300 From Dual Union All
                          4  Select 400 From Dual
                          5  )
                          6  Select Max(Sal) From Data Order By Sal; -- Ordering in Ascending order
                        
                          MAX(SAL)
                        ----------
                               400
                        
                        SQL>
                        SQL> With Data As
                          2  (Select  200 Sal From Dual Union All
                          3  Select 300 From Dual Union All
                          4  Select 400 From Dual
                          5  )
                          6  Select Max(Sal) From Data Order By Sal Desc; ---- Ordering in desc order
                        
                          MAX(SAL)
                        ----------
                               400
                        
                        SQL>
                        SQL> With Data As
                          2  (Select  200 Sal From Dual Union All
                          3  Select 300 From Dual Union All
                          4  Select 400 From Dual
                          5  )
                          6  Select Max(Sal) From Data;  ---No order
                        
                          MAX(SAL)
                        ----------
                               400
                        did you observe, result remains same.
                        Hope it is clear now.

                        Vivek L
                        • 9. Re: ORA-00904 invalid identifier [nested select problem]
                          858930
                          yeah i get it but... the value return is not integer, i getting the latest record sorted from the date... not the maximum num.
                          MAX is wrong to begin with. My fren told me have to use first_value ?