This content has been marked as final.
Show 5 replies
-
1. Re: SQL language change in 11g?
user130038 Aug 9, 2011 5:52 PM (in response to 876267)Could you please provide some example of the issue you are facing?
http://www.asktheoracle.net/oracle-11g-sql-new-features.html
http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf -
2. Re: SQL language change in 11g?
876267 Aug 9, 2011 6:50 PM (in response to user130038)The VBA script, within Microsoft Access 2007, runs up to this SQL script, though strangely even if I comment out the "Is Null" statements:
strSQL = "SELECT DISTINCT IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"") AS AQ_CODE, qsptLOCATION.AQUIFER_CODE, qsptLOCATION.LOC_CODE, qsptLOCATION.CLASS, IIf([tblWellInfo]![CP_TBL_V]=""POC"",""Compliance"",IIf([tblWellInfo]![CP_TBL_V]=""POE"",""Exposure"","""")) AS DESIGNATION, IIf(clng(tblWellInfo.SEG_SCR) > 1,""M"","""") AS MULTI_LEVEL, tblWellInfo.PROPERTY INTO tbl_LOCATION " & _
"FROM qsptLOCATION INNER JOIN tblWellInfo ON qsptLOCATION.LOC_CODE = tblWellInfo.WELL_ID " & _
"WHERE (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
"AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
"AND ((qsptLOCATION.CLASS) Is Null) AND ((qsptLOCATION.DESCRIPT) Is Null)) OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' " & _
"Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
"AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
"AND ((qsptLOCATION.CLASS) Is Null) AND ((qsptLOCATION.DESCRIPT) Not Like '*RENAMED*')) " & _
"OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
"AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
"AND ((qsptLOCATION.CLASS) Not In ('PR','AW','IB','PV','IJ')) AND ((qsptLOCATION.DESCRIPT) Is Null)) OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or " & _
"(IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
"AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
"AND ((qsptLOCATION.CLASS) Not In ('PR','AW','IB','PV','IJ')) AND ((qsptLOCATION.DESCRIPT) Not Like '*RENAMED*')) " & _
"ORDER BY IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala""), qsptLOCATION.LOC_CODE;"
CurrentDb.Execute strSQL
Edited by: Barry on Aug 9, 2011 11:50 AM -
3. Re: SQL language change in 11g?
Srini Chavali-Oracle Aug 11, 2011 2:13 AM (in response to 876267)Pl post the complete error message and text
HTH
Srini -
4. Re: SQL language change in 11g?
876267 Aug 12, 2011 1:45 PM (in response to Srini Chavali-Oracle)We removed "DISTINCT" from the beginning of the script, which solved the problem. But now we are having other problems with the Access database / SQL pass-through queries. Is there much that has changed with Oracle 11g that the SQL queries must be written differently? -
5. Re: SQL language change in 11g?
Srini Chavali-Oracle Aug 12, 2011 2:05 PM (in response to 876267)Details are in the documentation - http://download.oracle.com/docs/cd/E11882_01/server.112/e17128/toc.htm
HTH
Srini