5 Replies Latest reply on Mar 27, 2006 2:06 PM by Turloch O'Tierney-Oracle

    Access Queries with "IIF" function

    272574
      We have several queries using the "IIF" function in Access 2000. When we try to build the Oracle model an eror is generated:
      Error occured on line 1:
      oracle.mtg.msaccess.parser.queryparser.ParseException: Parse error at line 1, column 232. Encountered: (

      The column number seems to be spurious as is the character reported "(" sometimes we also get "OR", "AND" etc.. However I have traced it to where an "IIF" function is used.

      Does anyone know if this is supported, or if there is any way that I can force the Oracle object to be created in the Oracle model so that I can manually re-engineer it.

      Thanks

      Kevin
        • 1. Re: Access Queries with "IIF" function
          Turloch O'Tierney-Oracle
          Kevin,

          Case when functionality can replace iif:
          SQL> set serveroutput on
          SQL> begin
          2 if (case when 1>2 then 1 else 0 end = 0) then
          3 dbms_output.put_line('match');
          4 end if;
          5 end;
          6 /
          match

          PL/SQL procedure successfully completed.

          SQL>
          and (for within a select):
          SQL> select case when 1>2 then 1 else 0 end from dual;

          CASEWHEN1>2THEN1ELSE0END
          ------------------------
          0
          [on oracle XE 10.2]

          I hope that helps.

          As you have noticed the parser looks further up the stream of data, if it cannot resolve the situation a few tokens ahead it will report an error slightly before the actual problem is.

          The IIF function may not be handled, unfortunately the IIF function may not be simply parsed as an ordinary function as the first argument can be an expression.

          Regards,
          Turloch

          Oracle Migration Workbench Team
          • 2. Re: Access Queries with "IIF" function
            272574
            Thanks Turloch, I have noticed that where OMWB does resolve IIF into a case statement there seems to be a bug in that it misses out the operator e.g.

            CASE WHEN (1 jlday) THEN jlqty ELSE 0 END

            should be

            CASE WHEN (1 = jlday) THEN jlqty ELSE 0 END

            I can work arround this but I thought you may want to know for future ref.

            Cheers

            Kevin
            • 3. Re: Access Queries with "IIF" function
              Turloch O'Tierney-Oracle
              Kevin,

              Can you send a reproducible test case, where IIF leaves out the expression, where it fails completely and where it works, (give the before and after text) so that I can log a bug so this issue can be tracked?

              I apologize that the various options were missed in development and testing.

              Regards,
              Turloch

              Oracle Migration Workbench Team
              • 4. Re: Access Queries with "IIF" function
                272574
                Turloch

                SELECT [TBL-RSITE].RSREC,
                SUM(1) test,
                sum(IIf([sofromrec] And Not IsNull([CUACTDATE]),1,0)) AS Surveyed,
                Sum(IIf([sofromrec],1,0)) AS Leads,
                Sum(IIf([sofromrec] And Not IsNull([cuactdate]) And Not [cureject],1,0)) AS Sales,
                Sum(IIf(Not [sofromrec] And Not IsNull([cuactdate]) And Not [cureject],1,0)) AS Self,
                Sum(IIf([cureject] And [sofromrec] And Not nz([TBL-Reject].[rjnosale],False) And Not nz([TBL-Reject_1].[rjnosale],False) And Not nz([TBL-Reject_2].[rjnosale],False),1,0)) AS NonElig
                FROM (([TBL-REJECT]
                RIGHT JOIN (([TBL-CUSTOMERS]
                INNER JOIN [TBL-RSITE] ON [TBL-CUSTOMERS].CURECJOBKEY = [TBL-RSITE].RSJOBKEY)
                INNER JOIN [TBL-SOURCE] ON [TBL-CUSTOMERS].CUSOURCE = [TBL-SOURCE].SOCODE) ON [TBL-REJECT].RJCODE = [TBL-CUSTOMERS].CUREASON1) LEFT JOIN [TBL-REJECT] AS [TBL-REJECT_1] ON [TBL-CUSTOMERS].CUREASON2 = [TBL-REJECT_1].RJCODE) LEFT JOIN [TBL-REJECT] AS [TBL-REJECT_2] ON [TBL-CUSTOMERS].CUREASON3 = [TBL-REJECT_2].RJCODE WHERE ((([TBL-CUSTOMERS].CUAPPDATE)>=FunDate1() And ([TBL-CUSTOMERS].CUAPPDATE)<=FunDate2()) AND (([TBL-CUSTOMERS].CUTYPE)="D"))
                GROUP BY [TBL-RSITE].RSREC
                HAVING ((([TBL-RSITE].RSREC)=FunGlRec()));

                Fails with 11 occurrences of the following error message:

                Error occured on line 2:
                oracle.mtg.msaccess.parser.queryparser.ParseException: Parse error at line 2, column 1. Encountered: Sum

                Note that I added "SUM(1) test," to confirm that it was the iif causing the problem. Prior to this the error was reported as line 1
                --------------------------------------------------------
                SELECT qry_Labels_TCard_MatMain.Label_Position,
                qry_Labels_TCard_MatMain.SelectedLabel,
                IIf([Label_Position]=[SelectedLabel],[PTJOBKEY],Null) AS [Key],
                IIf([Label_Position]=[SelectedLabel],"Plots",Null) AS H1,
                IIf([Label_Position]=[SelectedLabel],"Houses",Null) AS H2,
                IIf([Label_Position]=[SelectedLabel],"MType",Null) AS H3,
                IIf([Label_Position]=[SelectedLabel],"Areas",Null) AS H4
                FROM qry_Labels_TCard_MatMain;

                Generates case statements with no operators:

                CREATE OR REPLACE FORCE VIEW qry_Labels_TCard_MatMain02 AS
                SELECT qry_Labels_TCard_MatMain.Label_Position,
                qry_Labels_TCard_MatMain.SelectedLabel,
                CASE WHEN (SelectedLabel Label_Position) THEN PTJOBKEY ELSE NULL END Key,
                CASE WHEN (SelectedLabel Label_Position) THEN 'Plots' ELSE NULL END H1,
                CASE WHEN (SelectedLabel Label_Position) THEN 'Houses' ELSE NULL END H2,
                CASE WHEN (SelectedLabel Label_Position) THEN 'MType' ELSE NULL END H3,
                CASE WHEN (SelectedLabel Label_Position) THEN 'Areas' ELSE NULL END H4
                FROM qry_Labels_TCard_MatMain

                I have approximately 100 queries to migrate, I have run an initial test as I was not sure what changes are made to the access DB for migrated queris but I am not seeing any changes made automatically for these. Please could you confirm that this is expected (i.e. the access DB changes for migrated queries is manual) or whether you would expect OMWB to automatically link the migrated queries/views as it does for tables.

                Thanks

                Kevin
                • 5. Re: Access Queries with "IIF" function
                  Turloch O'Tierney-Oracle
                  Logged bug:
                  Bug No: 5120370 MS-ACCESS QUERIES: IFF OPERATOR LOST, AND PARSE ERROR

                  -Operator lost should be easy to fix,
                  -Parse Error could be more difficult and/or specific to the statement reported.
                  Please could you confirm that this is expected (i.e. the access DB changes for migrated queries is manual) or whether you would expect OMWB to automatically link the migrated queries/views as it does for tables.
                  The queries are migrated to Oracle views which to a select appear like tables. If you use parameters there is a get/set procedure api that stores state in per session package variables.
                  (Note also you may run into a problem with create view force where tablename.* is used, this is a known bug that is linked to a base rdbms bug, the problem is the connection disconnects and the workaround is not to force those views and rebuild them later.)

                  I am confirming that the access DB changes, for migrated queries, are manual in the current release.

                  I hope that is of some help.

                  Regards,
                  Turloch

                  Oracle Migration Workbench Team

                  Message was edited by:
                  totierne