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