14 Replies Latest reply: Sep 27, 2013 7:46 AM by User508474-OC RSS

    Syntax 10g vs 11g

    User508474-OC

      Hello all,

       

      We have just migrated from DB version 10 to version 11.2.0.3.

      We have found out, that we have to do a revision of old queries, because there are probably differencies in the syntax.

       

      Could I ask you, if somebody knows, where are these differencies described?

       

      Here is an example, what google didn't told me....

      I have some condition where table_1.id = table_2.id ( +)

       

      What doesn't mean ( +)?

       

      Thank you for your help.

      Zdenek

        • 1. Re: Syntax 10g vs 11g
          Ramin Hashimzadeh

          928655 wrote:

           

          Hello all,

           

          We have just migrated from DB version 10 to version 11.2.0.3.

          We have found out, that we have to do a revision of old queries, because there are probably differencies in the syntax.

           

          Could I ask you, if somebody knows, where are these differencies described?

           

          Here is an example, what google didn't told me....

          I have some condition where table_1.id = table_2.id ( +)

           

          What doesn't mean ( +)?

           

          Thank you for your help.

          Zdenek

           

          from table_1,table2

          where where table_1.id = table_2.id ( +)


          equal


          LEFT JOIN table_2 ON table_2.id = table_1.id


          ----

          Ramin Hashimzade

          • 2. Re: Syntax 10g vs 11g
            User508474-OC

            Hello Ramin,

             

            Thank you for your reply.

             

            I know that left join is equal.

            My question is, what is difference between:

             

            Select * from table 1

            where table1.id = table2.id ( +)

             

            and

             

            Select * from table 1

            where table1.id = table2.id

             

            What is meaning of ( +)?

             

            Thank you

            • 3. Re: Syntax 10g vs 11g
              Hoek

              It's the OUTER JOIN operator, see:  Joins

              • 4. Re: Syntax 10g vs 11g
                Ramin Hashimzadeh

                ( +) means "left join" if you didn't type ( +) it will mean "inner join".


                ----

                Ramin Hashimzade

                • 5. Re: Syntax 10g vs 11g
                  User508474-OC

                  Thank you guys, that was what I needed.

                   

                  Zdenek

                  • 6. Re: Syntax 10g vs 11g
                    Frank Kulash

                    Hi, Zdenek,

                     

                    You don't need to change anything.  Any Oracle version, 9.1 or higher, supports either way of writing outer joins.  Code that worked in Oracle 10 will work just as well in Oracle 11.


                    • 7. Re: Syntax 10g vs 11g
                      User508474-OC

                      Hello,

                       

                      yes I found out it before few minutes ago.

                      Now I know, that problem is somewhere between visual studio and oracle client.

                      Do you have some experience with visual studio 2010?

                      Oracle client version is the same as database - 11.2.0.3.

                       

                      Zdenek

                      • 8. Re: Syntax 10g vs 11g
                        BluShadow
                        • 9. Re: Syntax 10g vs 11g
                          User508474-OC

                          Hello,

                           

                          problem is that I have web page with sqldatasource.

                          This datasource used oracle client for database version - 10.2.0.5

                          Everything worked fine till we migrate database to version 11.2.0.3.

                           

                          Then we installed oracle client 11.2.0.3.

                          When we go to datasource and try to open query, we get an error that query builder can't parse query.

                          You know the message "Incorect syntax in where clausule near '(' ....", we confirm it, execute query and query is executed correctly.

                           

                          Tested query was

                          Select * from table 1

                          where table1.id = table2.id ( +)

                           

                          If I try query without ( +), it is without any error...

                           

                          Some more difficult queries don't work - when we try to execute them, we get error that query couldn't be parsed, but it will be solved in second step...

                           

                          We have tried it in visual studio 2010 and 2012, I have found references Oracle.DataAccess in web.config and try to change them to last version, but behavior is still the same.

                           

                          Thank you for your help....

                          Zdenek

                          • 10. Re: Syntax 10g vs 11g
                            Pablolee

                            Select * from table 1

                            where table1.id = table2.id ( +)

                            Are you sure  that this is the query?

                            • 11. Re: Syntax 10g vs 11g
                              BluShadow

                              that's odd, it sounds like some issue with Visual Studio, as the Oracle client and server both support oracle outer join syntax as well as ansi join syntax.

                               

                              Is the connection being made through an ODBC driver?  if so, is that an Oracle supplied/supported ODBC driver, or some 3rd party or generic driver?

                              • 12. Re: Syntax 10g vs 11g
                                User508474-OC

                                Hello,

                                 

                                Connection is done with Oracle Database/.NET provider for Oracle.

                                 

                                The exact query is this:

                                 

                                SELECT   INV.MTL_SYSTEM_ITEMS_B.SEGMENT1, INV.MTL_SYSTEM_ITEMS_B.DESCRIPTION, XX_CROSS_REFERENCES_EAN.CROSS_REFERENCE,

                                               XXOEZ_LEON_STRATEGIC_UNITS_V.CATEGORY_CODE, XXOEZ_LEON_STRATEGIC_UNITS_V.CATEGORY_DESCRIPTION, INV.MTL_SYSTEM_ITEMS_B.ITEM_TYPE,

                                               INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE, XXEIGNER_POLOZKY_AL_ECCN.AL, XXEIGNER_POLOZKY_AL_ECCN.ECCN         

                                FROM     INV.MTL_SYSTEM_ITEMS_B, XX_CROSS_REFERENCES_EAN, XX_STRATEGICKA_JEDNOTKA, XXOEZ_LEON_STRATEGIC_UNITS_V,

                                               XXEIGNER_POLOZKY_AL_ECCN         

                                WHERE INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = XX_CROSS_REFERENCES_EAN.INVENTORY_ITEM_ID(+) AND

                                             INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = XX_STRATEGICKA_JEDNOTKA.INVENTORY_ITEM_ID(+) AND

                                             XX_STRATEGICKA_JEDNOTKA.STRATEG_JEDN = XXOEZ_LEON_STRATEGIC_UNITS_V.CATEGORY_CODE AND

                                             INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = XXEIGNER_POLOZKY_AL_ECCN.INVENTORY_ITEM_ID(+) AND

                                             (INV.MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = 106) AND (INV.MTL_SYSTEM_ITEMS_B.ITEM_TYPE IN ('LFIN', 'LOZ')) AND

                                             (INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE = 'Active')

                                ORDER BY XXOEZ_LEON_STRATEGIC_UNITS_V.CATEGORY_DESCRIPTION, INV.MTL_SYSTEM_ITEMS_B.DESCRIPTION

                                 

                                Zdenek

                                • 13. Re: Syntax 10g vs 11g
                                  SomeoneElse

                                  AND INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID   = XX_STRATEGICKA_JEDNOTKA.INVENTORY_ITEM_ID(+)

                                  AND XXOEZ_LEON_STRATEGIC_UNITS_V.CATEGORY_CODE = XX_STRATEGICKA_JEDNOTKA.STRATEG_JEDN

                                   

                                  Are you sure these two lines are correct?  In the first, you're doing an outer join to XX_STRATEGICKA_JEDNOTKA but in the second line you're doing an inner join.

                                  • 14. Re: Syntax 10g vs 11g
                                    BluShadow

                                    From what I can gather, your query looks like this in ANSI syntax:

                                     

                                    SELECT   M.SEGMENT1

                                            ,M.DESCRIPTION

                                            ,C.CROSS_REFERENCE

                                            ,L.CATEGORY_CODE

                                            ,L.CATEGORY_DESCRIPTION

                                            ,M.ITEM_TYPE

                                            ,M.INVENTORY_ITEM_STATUS_CODE

                                            ,P.AL

                                            ,P.ECCN

                                    FROM                     INV.MTL_SYSTEM_ITEMS_B   M

                                             LEFT OUTER JOIN XX_CROSS_REFERENCES_EAN  C ON (M.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID)

                                             LEFT OUTER JOIN (     XX_STRATEGICKA_JEDNOTKA       S

                                                              JOIN XXOEZ_LEON_STRATEGIC_UNITS_V  L

                                                              ON  (S.STRATEG_JEDN = L.CATEGORY_CODE)

                                                             )                          ON (M.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID)

                                            ,LEFT OUTER JOIN XXEIGNER_POLOZKY_AL_ECCN P ON (M.INVENTORY_ITEM_ID = P.INVENTORY_ITEM_ID)

                                    WHERE    M.ORGANIZATION_ID = 106

                                    AND      M.ITEM_TYPE IN ('LFIN', 'LOZ')

                                    AND      M.INVENTORY_ITEM_STATUS_CODE = 'Active'

                                    ORDER BY L.CATEGORY_DESCRIPTION, M.DESCRIPTION

                                     

                                    I've applied aliases to the table names to make the query easier to read.  (Nothing worse than fully qualified table table.column names to make queries hard to read).