Multi-table SQLSelects

Question

I'm trying to figure out how these statements really work, and am having some trouble with the documentation (and the software!).

Using the example publishers database (Mathematica 8), the following works fine (as documented):

SQLSelect[conn,{"TITLES","ROYSCHED"},{{"TITLES","TITLE"},
    {"TITLES","TITLE_ID"},{"ROYSCHED","TITLE_ID"},{"ROYSCHED","ROYALTY"}},
  "MaxRows"->5]

Now, there is no column ROYALTY in the TITLES table, so perhaps I should be able to write

SQLSelect[conn,{"TITLES","ROYSCHED"},{{"TITLES","TITLE"},
    {"TITLES","TITLE_ID"},{"ROYSCHED","TITLE_ID"},"ROYALTY"},
  "MaxRows"->5]

But this results in the error

DatabaseLink`SQL`Private`SQLValue::illegalvalue: 
    The value {{TITLES,TITLE},{TITLES,TITLE_ID},{ROYSCHED,TITLE_ID},ROYALTY} 
    cannot be converted to a value in an SQL statement.

So apparently either none or all of the entries in the columns list must themselves be lists. Is this right, or a bug, or what?

So assuming that this is correct, and all the columns must be lists in any multi-table inner join, how can I then apply a function such as COALESCE to the results to cast NULLs to zero? I tried {"TITLES","COALESCE(TITLE_ID,0) AS TITLE_ID"}, which failed because the string passed to the JDBC driver was TITLES.COALESCE(TITLE_ID,0) AS TITLE_ID, which is of course incorrect.

At the risk of asking too many questions in one question, let me add: I'm perfectly OK with simply returning the rows I want, with whatever NULLs may be in them. But can someone tell me the right way to turn all those NULLs to zeros once the result set is a Mathematica list?

It does not sound like a bug to me. It wants to know what table each field belongs to even when there's no conflict, not necessary in all strains of SQL but not a very strange requirement for a multi-table query either. For more advanced queries you can turn to SQLExecute. To replace NULL, simply do list /. Null->0. — C. E., Aug 04 '13 at 09:52
@Anon Sure, but SQLSELECT[conn,{"TITLES","ROYSCHED"},{"TITLE","ROYALTY"}] works just fine --- and TITLE is a column only in TITLES, ROYALTY only in ROYSCHED. — rogerl, Aug 05 '13 at 00:23
Then I guess it is as you say it is. You can report it as a bug I guess and they may tell you if it's a bug or not. — C. E., Aug 05 '13 at 07:15

score 3 · Accepted Answer · answered Jan 09 '14 at 20:08

If you examine Information[SQLSelect] you'll see the various input patterns allowed for SQLSelect. It's quite long but the relevant parts are:

DatabaseLink`SQL`Private`columns:_SQLColumn|{__SQLColumn}

DatabaseLink`SQL`Private`columns:_String|{__String}

DatabaseLink`SQL`Private`columns:{{_String,_String}..}

Indeed, it allows either strings, lists of strings, and lists of lists of strings (and of course SQLColumn). However, there is no pattern for a mixture of these inputs. Whether this is by design or caused by oversight I'm unable to tell, but since this is the exposed interface I wouldn't call the behavior you noticed a bug.

To enter your COALESCE function you could use raw SQL:

SQLExecute[conn, "
 SELECT TITLES.TITLE, COALESCE(TITLES.TITLE_ID,0) AS TITLE_ID, ROYSCHED.TITLE_ID, ROYALTY
 FROM TITLES, ROYSCHED
"]

Multi-table SQLSelects

1 Answers1