DBConnect 3.1 query with JOINS, Alias', non-* and Rising Column

[nickstone]

Struggling a little bit with this, essentially I'm trying to pull some specific fields from multiple tables to form "log messages" however I'm having issues when my SQL statements become "Advanced".

Essentially what I'm looking for is:

     SELECT T1.id, T2.name FROM Catalog.Table1 AS T1
     LEFT OUTER JOIN Catalog.Table2 AS T2 ON T1.id = T2.id

     WHERE T1.id > ?
     ORDER BY T1.id ASC

Here's what I've tried:
The most basic query I can think of works fine:

SELECT * FROM table
WHERE id> ?
ORDER BY id ASC

cool, so moving forward let's try making a join:

SELECT * FROM Catalog.Table1
LEFT OUTER JOIN Catalog.Table2 ON Catalog.Table1.id = Catalog.Table2.id

WHERE Catalog.Table1.id > ?
ORDER BY Catalog.Table1.id ASC

This will result in "java.lang.IllegalStateException: Column name conflicted, please set shortnames option to false and retry"

So to alleviate this, we can either do some alias' or we can be specific and do some renames:

     SELECT * FROM Catalog.Table1 AS T1
     LEFT OUTER JOIN Catalog.Table2 AS T2 ON T1.id = T2.id

 WHERE T1.id > ?
 ORDER BY T1.id ASC

    SELECT Catalog.Table1.id FROM Catalog.Table1
    LEFT OUTER JOIN Catalog.Table2 ON Catalog.Table1.id = Catalog.Table2.id

    WHERE Catalog.Table1.id > ?
    ORDER BY Catalog.Table1.id ASC

Both of which result in "java.sql.SQLException: Parameter #1 has not been set."

I'm hoping not to create a view or a stored procedure as the DB is not mine and I don't have access.

I'd also like to avoid using Splunk Search or Datamodels to do the joins for ease of support.

Any ideas how to get this to work?

[tmuth_splunk · Accepted solution]

Can you try:

SELECT t1.*
FROM Catalog.Table1 AS T1 LEFT OUTER
JOIN Catalog.Table2 AS T2
    ON T1.id = T2.id
WHERE T1.id > ?
ORDER BY  T1.id ASC

[nickstone]

Its not clear why, but my original query is working now. The only difference between yours and mine is "Select t1.*"

I'm going to mark this right because it is the most similar answer, but I think my original issue could have been user error or permissions (the only thing to have changed)

[tmuth_splunk]

One of your examples had "select * ..." from a 2 table join. Both tables contain the column "id", so you got an ambiguous column error. The "table_alias.*" trick is nice for disambiguating select columns. For example:

 SELECT t1.*, t2.id as id2. t2.foo, t2.bar 
 FROM Catalog.Table1 AS T1 LEFT OUTER
 JOIN Catalog.Table2 AS T2
     ON T1.id = T2.id
 WHERE T1.id > ?
 ORDER BY  T1.id ASC

In short, give me all of the columns from t1, then get t2.id and alias it to id2 since both tables contain "id", then a few more select columns from t2.

[ejenson_splunk]

This issue does not occur in DBX 3.0.x so in 3.1.x the columns from T2 all need to specified as opposed to using the "*" option to return all rows from T2.

[nickstone]

It seems the only thing it doesnt like is the '?' in the WHERE statement. If I replace this with something like '1' it works.... kinda....

[nickstone]

Just trying with Microsoft's Common Table Expressions (CTE) and I can get the sattement to work as expected, however (like above) adding in the WHERE clause for rising column results in "java.sql.SQLException: Parameter #1 has not been set."
https://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

Taken from: https://answers.splunk.com/answers/490846/splunk-db-connect-how-does-the-rising-column-work.html?utm...

[jkat54]

Have you considered creating a stored proceedure and just selecting it instead? It's a better practice anyways.

[nickstone]

Like I said above, I only have read-only access and was hoping I could avoid this....