Splunk DB Connect Tail Command Alias Problem

Sammich — Mon, 28 Sep 2020 14:04:38 GMT

I'm trying to setup a tail input with a query that uses some column aliases. I use one of these column aliases as my rising column. I'm getting confused because on the initial run, I get results, but then with each subsequent execution I get an invalid identifier error.

Can you use a column alias as a rising column and if so does it need to be in all caps (for oracle) or as defined in the sql? When it gives me the error it has the column in all caps, but I've tried it both ways and it doesn't seem to work.

Here are the details of the error
2013-06-12 12:13:37.365 dbx7003:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLSyntaxErrorException: ORA-00904: "WAVE_NUMBER": invalid identifier
java.sql.SQLSyntaxErrorException: ORA-00904: "WAVE_NUMBER": invalid identifier
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)

Once again my alias is "Wave_Number" and I have done both Wave_Number and WAVE_NUMBER and both seem to only work upon initial execution and fail upon subsequent runs.

I have since changed the initial SQL, but it is extremely similar to the below
with
loginfo as
(
select wp.whse, ref_value_1 as wave_nbr, wp.wave_desc as wave_desc,
wp.max_units as max_units, wp.max_orders as max_pkts,
log_date_time as wave_start_time,
(select log_date_time from wmos_efc.msg_log where module = 'WAVE' and msg_id = '2018' and ref_value_1 = ml.ref_value_1) as wave_end_time,
wp.wave_stat_code
from wmos_efc.msg_log ml, wmos_efc.wave_parm wp
where ml.ref_value_1 = wp.wave_nbr
and module = 'WAVE'
and msg_id = '2003'
and wp.create_date_time > SYSDATE - 1
),
pktinfo as
(
select o.rte_wave_nbr, count(tc_order_id) as total_pkts, sum(o.tax_id) as total_units
from wmos_efc.orders o, wmos_efc.wave_parm wp
where o.rte_wave_nbr = wp.wave_nbr
and wp.create_date_time > SYSDATE - 1
group by o.rte_wave_nbr
),
waveinfo as
(
select /*+ OPT_PARAM('_optimizer_cost_based_transformation' 'linear') */
loginfo.whse, loginfo.wave_nbr, loginfo.wave_desc, loginfo.max_pkts, loginfo.max_units,
loginfo.wave_start_time, loginfo.wave_end_time, pktinfo.total_pkts,
pktinfo.total_units, to_char(trunc(sysdate) + (loginfo.wave_end_time-loginfo.wave_start_time), 'HH24":"MI":"SS') as total_wave_time
from loginfo, pktinfo
where loginfo.wave_nbr = pktinfo.rte_wave_nbr
and loginfo.wave_end_time >= SYSDATE - 60/1440
order by loginfo.wave_start_time
)
select whse AS Warehouse, TO_CHAR(wave_start_time, 'YYYY-MM-DD HH24:MI:SS') AS Start Time, wave_nbr AS Wave Number, wave_desc AS Wave Description, max_pkts AS Max Pkts, max_units AS Max Units, TO_CHAR(wave_end_time, 'YYYY-MM-DD HH24:MI:SS') AS End Time, total_pkts AS Total Pkts,
total_units AS Total Units, total_wave_time AS Total Wave Time
from waveinfo
{{where $rising_column$ > ?}}

Rising Column = Wave Number

Re: Splunk DB Connect Tail Command Alias Problem

ziegfried — Wed, 12 Jun 2013 19:56:40 GMT

Could you please add the detailed error message to the question?

Re: Splunk DB Connect Tail Command Alias Problem

ziegfried — Wed, 12 Jun 2013 20:26:01 GMT

Are you using custom SQL or did you just specify a table?

Re: Splunk DB Connect Tail Command Alias Problem

ziegfried — Wed, 12 Jun 2013 23:11:34 GMT

For the initial run (when there is no checkpoint value) of the database input, the part in the double-curly brackets is omitted (ie. {{...}}). For any subsequent run (when there is a checkpoint value), the part is included (without the curly brackets them-selfes) and the rising column placeholder is replaced. In your example it would result in:

 ... where Wave_Number > ?

In your case you can't use the column alias Wave_Number in the WHERE clause (the WHERE clause is actually evaluated before the SELECT clause).

You could probably wrap you whole SQL statement in another, outer SELECT statement like this:

SELECT * FROM (
    <your SQL>
) as inner_table
{{WHERE $rising_column$ > ?}}

Using a HAVING-clause instead of WHERE would probably work as well:

<your SQL>
{{HAVING $rising_column$ > ?}}

Otherwise I'd suggest not to use a column alias.

topic Splunk DB Connect Tail Command Alias Problem in All Apps and Add-ons

Splunk DB Connect Tail Command Alias Problem

Re: Splunk DB Connect Tail Command Alias Problem

Re: Splunk DB Connect Tail Command Alias Problem

Re: Splunk DB Connect Tail Command Alias Problem