How can I work with Excel crash with certain SELECT statements?

Question

The two statements are similar, but in the second case, each time it is executed, a failure occurs. The only difference between model and model return updated rows (I specifically designed this minimal example so that the queries return exactly the same data anyway, my real SQL, of course, is different):

•  select * from( select * from ( select 1 id, 100 val from dual union all select 2 id, 200 val from dual ) model dimension by (id) measures (val) rules ( val[1] = val[cv()]+1 ) ) where val=101 

•  select * from( select * from ( select 1 id, 100 val from dual union all select 2 id, 200 val from dual ) model return updated rows dimension by (id) measures (val) rules ( val[1] = val[cv()]+1 ) ) where val=101 

Whether this is an isolated example of an error in ADO or there is a well-known class of SQL statements that break the parser (I'm not even sure why ADO will parse the statement and not just pass it to the database).

Here's the full VBA code for the version that crashes:

 Option Explicit Sub Go() Dim lConn As ADODB.Connection Dim lRecordset As ADODB.Recordset 'Dim lRecordset Dim sSQL As String Set lConn = New ADODB.Connection Set lRecordset = New ADODB.Recordset 'Set lRecordset = CreateObject("ADODB.Recordset") lConn.Open "Provider=MSDAORA;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devdb)(PORT=1521)))(CONNECT_DATA=(SID=oracle)(SERVER=DEDICATED)));User Id=csuk;Password=thisisnotmyrealpassword;" With lRecordset sSQL = "select * " & _ "from( select * " & _ " from ( select 1 id, 100 val from dual " & _ " Union all " & _ " select 2 id, 200 val from dual ) " & _ " model return updated rows " & _ " dimension by(id) " & _ " measures (val) " & _ " rules ( val[1] = val[cv()]+1 ) ) " & _ "where val=101" .Open sSQL, lConn While Not .EOF Sheets(1).Cells(1, 1) = ![Val] .MoveNext Wend .Close End With Set lRecordset = Nothing lConn.Close Set lConn = Nothing End Sub 

---

In response to the comment, I tried the same SQL using DAO, and to my surprise, we get the same result. The following code crashes Excel, but removing return updated rows is all it takes to get it working as expected:

 Option Explicit Sub Go() Dim lWorkspace As DAO.Workspace Dim lDatabase As DAO.Database Dim lRecordset As DAO.Recordset Dim sSQL As String sSQL = "select * " & _ "from( select * " & _ " from ( select 1 id, 100 val from dual " & _ " Union all " & _ " select 2 id, 200 val from dual ) " & _ " model return updated rows " & _ " dimension by(id) " & _ " measures (val) " & _ " rules ( val[1] = val[cv()]+1 ) ) " & _ "where val=101" Set lWorkspace = DBEngine.Workspaces(0) Set lDatabase = lWorkspace.OpenDatabase("", False, False, "Driver={Microsoft ODBC for Oracle};Server=devdb:1521/oracle;Uid=charts_csuk_uksoft;Pwd=thisisnotmyrealpassword;") Set lRecordset = lDatabase.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough) With lRecordset While Not .EOF Sheets(1).Cells(1, 1) = ![Val] .MoveNext Wend End With Set lRecordset = Nothing Set lDatabase = Nothing Set lWorkspace = Nothing End Sub 

Answer 1

This is more of a workaround than a solution, but it may be possible to hide any SQL statements that violate ADO (or Excel, for that matter), for VIEW . For dynamic statements (i.e., changing views at runtime), you can use procedures with EXECUTE IMMEDIATE CREATE OR REPLACE VIEW ... in them.

Answer 2

You can avoid the complicated syntax by creating a custom version of return updated rows .

Add a fake column to track the updated one and initialize it with false (0). Then add a measure, and for each rule, create a similar rule that sets the flag to true (1). Finally, add a predicate to include only lines in which the flag is 1.

This is clearly not an ideal solution. It will be slower. And you must remember to build all the additional rules that fill the flag.

 select id, val from( select * from ( select 1 id, 100 val, 0 is_updated_or_inserted from dual union all select 2 id, 200 val, 0 is_updated_or_inserted from dual union all select 3 id, 101 val, 0 is_updated_or_inserted from dual ) model dimension by (id) measures (val, is_updated_or_inserted) rules ( val[1] = val[cv()]+1 , is_updated_or_inserted[1] = 1) ) where val=101 and is_updated_or_inserted = 1 

---

Update

Here are some more ideas.

• Can you switch to Oracle software? Your first routine works for me if I use Provider=ORAOLEDB.ORACLE . And your second routine works if I use Oracle DSN. I cannot get MSDAORA or {Microsoft ODBC for Oracle} to connect at all on my machine. (Although this is probably my mistake.)
• Try changing the model return updated rows to model keep nav return updated rows main main_model . This is a semantically meaningless change; it simply explicitly lists the default values. But perhaps this is enough to avoid the parsing error you are encountering.