We have just migrated from DB version 10 to version 18.104.22.168.
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.
where where table_1.id = table_2.id ( +)
LEFT JOIN table_2 ON table_2.id = table_1.id
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 22.214.171.124.
Then we installed oracle client 126.96.36.199.
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....
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?
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,
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
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.
From what I can gather, your query looks like this in ANSI syntax:
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).