In the SQL Query of a Database Input, is it possible to join multiple tables while preserving the rising column as the tailing value?
For example the following returns a "SQL command not properly ended" error :
SELECT *
FROM employee a, dept b
{{WHERE a.$rising_column$ > ?}}
AND a.salary = b.salary
But when I input the following SQL, the command works but infinite copies of the same rising_column value are put into the index flooding the index until I stop Splunk:
SELECT *
FROM employee a, dept b
{{WHERE a.salary = b.salary AND a.$rising_column$ > ?}}
I further tried writing the query this way, but no results were returned:
select *
from employee a NATURAL JOIN dept b
{{WHERE a.$rising_column$ > ?}}
Is there a way to write a multiple table join another way?
A word of advice: always explicitly define the columns you are selecting rather than using an implicit wildcard like "*". It makes troubleshooting easier down the road and prevents changes to underlying tables from causing unintended problems in unrelated code.
That said, try the following (substituting your column names as appropriate):
select a.col_1, a.col_2, b.col_1, b.col_2
from employee a
join dept b using (salary)
{{where a.$rising_column$ > ?}}