6 Replies Latest reply on Feb 19, 2003 4:57 PM by 388062

    ORA-00936: missing expression  - using SubQueries using OLEDB Driver

    388039
      Oracle has added support for sub-queries since Oracle 8.1, but I'm getting an error when I try to perform a simple select.
      ORA-00936: missing expression
      select ITEMS.ITEM_NAME
      , (select count(*) from WORK_FLOW.ITEM_ID = ITEMS.ITEM_ID) as ItemsBeingUse
      from ITEMS
      I do not want to use any other SQL code to perform this action. I have to see how the count could be placed in the FROM clause

      It does work from Oracle SQL Plus Worksheet. I just don't understand why it is not working through the Oracle OLE DB driver. Oracle ODBC is not an option as it crashes endlessly.
        • 1. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
          John Spencer
          There is no way that the statement you posted runs in any environemnt. There is no WHERE clause. To do this the way you are trying, you need:
          SELECT items.item_name, 
                 (SELECT COUNT(*)
                  FROM work_flow
                  WHERE work_flow.item_id = items.item_id) as ItemsBeingUse
          FROM items
          But, unless your real subquery is much more complicated than this, I suggest that a simple
          SELECT items.item_name,COUNT(*) itemsbeinguse
          FROM items, workflow
          WHERE items.item_id = work_flow.item_id
          GROUP BY items.item_name
          would be significantly faster.

          TTFN
          John
          • 2. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
            388062
            The main query is dramatically more complicated that what I have provided. Part of my problem with your solution is that one of my output columns is an NCLOB, which I cannot be used in a GROUP clause.

            Also, the WHERE clause in the main query is optional (as documented in both SQL-89 and SQL-92 specifications). The query does indeed work on MS SQL Server, and it does indeed work in Oracle if a view were created. The problem is that I don't want to create another 40+ views in my system that currently has 500 views. I just don't have that kind of time.

            Thanks for trying to help,
            Paul
            • 3. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
              Kamal Kishore
              The missing WHERE clause that John was mentioning was for the inner SELECT Count(*) query that joins WORK_FLOW with ITEMS table (outer select).

              Look at the following two statements. The first one gives the output whereas the second one (which is similar to your posted code), generates the error. This is what John is mentioning in his earlier post:

              =========================================================
              SQL> select dept.dname, (select count(*) from emp where emp.deptno = dept.deptno) as x from dept ;

              DNAME X
              -------------- ----------
              ACCOUNTING 3
              RESEARCH 5
              SALES 6
              OPERATIONS 0

              4 rows selected.

              SQL> select dept.dname, (select count(*) from emp.deptno = dept.deptno) as x from dept ;
              select dept.dname, (select count(*) from emp.deptno = dept.deptno) as x from dept
              *
              ERROR at line 1:
              ORA-00907: missing right parenthesis


              SQL>
              ==========================================================
              • 4. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
                388062
                My bad. In my haste, I did not create a valid sample. I'm going to include the actual queries that are giving me trouble plus one that works. So it seems that Oracle does support Subqueries but that it has bugs in its code. All of the following queries work fine in MS SQL Server.

                Can anyone else confirm these bugs in Oracle or the Oracle OLE DB driver? I have just tested the three queries in DBArtisan and in SQL Plus Worksheet and they all work fine. I just can't explain why they don't work within ASP using the Oracle OLE DB driver.

                -- This select works.
                select UserID
                , UserName
                , (select count(*)
                from VWR_LMS_HISTORY_INCOMPLETE
                where VWR_LMS_HISTORY_INCOMPLETE.USER_ID = VW_ADM_ASSN_USERS.UserID
                and (LCID = 1033)
                ) as Removed
                , CompanyName
                , ManagerName
                from VW_ADM_ASSN_USERS
                where UserID <> '2FFC4007024ED411B7D800508B35A0D7'
                order by 2;


                --ORA-00936: missing expression
                select GroupID
                , GroupName
                , (select count(*)
                from VWR_ADM_GROUP_USERS
                where VWR_ADM_GROUP_USERS.GROUP_ID = VW_ADM_ASSN_GROUP.GroupID
                and VWR_ADM_GROUP_USERS.USER_ID <> '2FFC4007024ED411B7D800508B35A0D7'
                ) as Members
                from VW_ADM_ASSN_GROUP
                order by 2;

                --ORA-00972: identifier is too long
                select GroupID
                , GroupName
                , (select count(distinct(USER_ID))
                from VWR_LCMS_GROUP_LT_SUMMARY
                where VWR_LCMS_GROUP_LT_SUMMARY.GROUP_ID = VW_ADM_ASSN_GROUP.GroupID
                and VWR_LCMS_GROUP_LT_SUMMARY.USER_ID <> '2FFC4007024ED411B7D800508B35A0D7'
                and (LCID = 1033)
                ) as Records
                from VW_ADM_ASSN_GROUP
                order by 2;

                • 5. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
                  388062
                  We were able to narrow down the problem to the OLE DB driver. It seems that we only get these errors if the Oracle OLE DB provider is used and not if the Oracle ODBC provider is used.

                  Has anyone else seen this problem? Is there some configuration setting for The OLE DB driver that can correct this problem?
                  • 6. Re: ORA-00936: missing expression  - using SubQueries using OLEDB Driver
                    388062
                    Even better, the Oracle OLE DB 9.2.0.2 driver fixes the problem.