All Apps and Add-ons

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

nickstone
Path Finder

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?

0 Karma
1 Solution

tmuth_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

tmuth_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

nickstone
Path Finder

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)

0 Karma

tmuth_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

ejenson_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

nickstone
Path Finder

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....

0 Karma

nickstone
Path Finder

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...

0 Karma

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

nickstone
Path Finder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!