8 Replies Latest reply: Aug 10, 2012 8:25 AM by 954337 RSS

    MS Access Query to Oracle

    954337
      Hi Gurus

      I am trying to convert a MS Access query to Oracle query, Having some trouble Need some help Please.

      This is the MS Access Query:

      Sum(IIf(LOAD1!ARRIVDATE<+Date()+7,LOADLINE1!QTY,0)/ITEM1!UOM) AS Loadtbl

      What would be the Oracle Query???


      Thank you
        • 1. Re: MS Access Query to Oracle
          wkobargs-Oracle
          Hello,

          what trouble do you have?

          Have you tried the Translation Scratch Editor? From the SQL Developer documentation:


          Using the Translation Scratch Editor


          You can use the translation scratch editor to enter third-party database SQL statements and have them translated to Oracle PL/SQL statements. You can specify translation from Microsoft SQL Server T-SQL to PL/SQL, from Sybase T-SQL to PL/SQL, or from Microsoft Access SQL to PL/SQL.

          You can display the scratch editor by clicking Tools, then Migration, then Translation Scratch Editor.

          Best regards
          Wolfgang
          • 2. Re: MS Access Query to Oracle
            954337
            Thank for the replay. By using Translation Scratch Editor can I Translate MS Access query to Oracle SQL query? Not PL/SQL. I am using Toad does Toad have any Translating features ?

            This is the code I came up with for Oracle:

            Sum(DECODE(LOAD1.ARRIVDATE<+SYSDATE+7,LOADLINE1.QTY,0)/ITEM1.UOM) AS Loadtbl

            I am getting an Missing right Parenthesis at ARRIVDATE.

            THANK YOU
            • 3. Re: MS Access Query to Oracle
              wkobargs-Oracle
              Hello,

              well, the problem is the name of the column, ARRIVDATE< - while MS Access allows the "<" inside a column name, Oracle doesn't if you don't be careful. Please see this:

              SQL> create table testtab1 (arrivdate varchar2(100));

              Table created.

              SQL> desc testtab1
              Name Null? Type
              ----------------------------------------- -------- ----------------------------

              ARRIVDATE VARCHAR2(100)

              SQL> create table testtab2 (arrivdate< varchar2(100));
              create table testtab2 (arrivdate< varchar2(100))
              *
              ERROR at line 1:
              ORA-00902: invalid datatype

              But this works:

              SQL> create table testtab2("ARRIVDATE<" varchar2(100));

              Table created.

              SQL> desc testtab2
              Name Null? Type
              ----------------------------------------- -------- ----------------------------

              ARRIVDATE< VARCHAR2(100)

              So your query might work if you put ARRIVDATE< in double quotes:

              Sum(DECODE(LOAD1."ARRIVDATE<"+SYSDATE+7,LOADLINE1.QTY,0)/ITEM1.UOM) AS Loadtbl

              Regards
              Wolfgang

              Edited by: wkobargs on Aug 8, 2012 7:51 AM

              Edited by: wkobargs on Aug 8, 2012 7:59 AM

              Edited by: wkobargs on Aug 8, 2012 8:00 AM
              • 4. Re: MS Access Query to Oracle
                wkobargs-Oracle
                My previous update seems to have been corrupted, so I post it again:


                Well, the problem is the name of the column, ARRIVDATE< - while MS Access allows the "<" inside a column name, Oracle doesn't if you don't be careful. Please see this:

                SQL> create table testtab1 (arrivdate varchar2(100));

                Table created.

                SQL> desc testtab1
                Name Null? Type
                ----------------------------------------- -------- ----------------------------

                ARRIVDATE VARCHAR2(100)

                SQL> create table testtab2 (arrivdate< varchar2(100));
                create table testtab2 (arrivdate< varchar2(100))
                *
                ERROR at line 1:
                ORA-00902: invalid datatype

                But this works:

                SQL> create table testtab2("ARRIVDATE<" varchar2(100));

                Table created.

                SQL> desc testtab2
                Name Null? Type
                ----------------------------------------- -------- ----------------------------

                ARRIVDATE< VARCHAR2(100)

                So your query might work if you put ARRIVDATE< in double quotes:

                Sum(DECODE(LOAD1."ARRIVDATE<"+SYSDATE+7,LOADLINE1.QTY,0)/ITEM1.UOM) AS Loadtbl

                Regards
                Wolfgang
                • 5. Re: MS Access Query to Oracle
                  wkobargs-Oracle
                  Argh... I am still missing what I wanted to post. So here it is again:

                  You need to put double quotes around the column name:

                  Sum(DECODE(LOAD1."ARRIVDATE<"+SYSDATE+7,LOADLINE1.QTY,0)/ITEM1.UOM) AS Loadtbl

                  Edited by: wkobargs on Aug 8, 2012 8:06 AM

                  Edited by: wkobargs on Aug 8, 2012 8:09 AM
                  • 6. Re: MS Access Query to Oracle
                    954337
                    Less than Sign is not a column name. ARRIVDATE IS the column name.
                    • 7. Re: MS Access Query to Oracle
                      wkobargs-Oracle
                      Sorry for the misunderstanding. Now I believe that I understood your problem.

                      You have simply replaced the IIF function by the DECODE function. But they are not similar in the arguments.

                      The syntax for the iif function is:

                      iif ( condition, value_if_true, value_if_false )

                      The syntax for the decode function is:

                      decode( expression , search , result [, search , result]... [, default] )

                      So while IIF has a condition as the first parameter, DECODE has an expression. You need to turn the condition into an expression. I found a good example here:

                      http://www.techonthenet.com/oracle/functions/decode.php

                      The first Q/A on that page should help you to correct your query.

                      Regards
                      Wolfgang
                      • 8. Re: MS Access Query to Oracle
                        954337
                        Thank you. Decode worked fine.

                        Thanks again.