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?
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
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
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)
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.
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.
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....
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...
Have you considered creating a stored proceedure and just selecting it instead? It's a better practice anyways.
Like I said above, I only have read-only access and was hoping I could avoid this....