We have an RDBMS Interface up and working, it's been running fine for a year or more. It's connected to an Oracle Database. Now, from the external database side, they are no longer sending us a row if there's not a total for that device. Meaning, instead of sending us a zero, they just don't send a row for this specific device number. I need to rewrite my select statement to be able to account for a no data situation.
My current select statement is: select total from PI.View where loc_id = '139660'.
This works great as long as there's a value in the total column and a row for the loc_id. The database owners have recently made a change where if there's no total, they just don't put the row in our view. So, I need to adjust.
I've come up with a new select statement that looks like:
select nvl((select total
where loc_id = '139660'
select null from dual where not exists (select 1 from PI.View where loc_id = '139660')), 0)
This works in Oracle SQL Developer but when I try to get RDBMS working with it I just get random errors in the logs.
In my dev system I get: Bad bind column iPosition 2!
In my prod system I get: cannot open SQL file D:\ORS\RDBMS\SQL\PQQM\NOM2.sql.
Tue Mar 03 15:13:31 2020 [IFC-E]: analyzeTag> Point - QtyTESTING : Wrong keyword definition in Extended Descriptor: P1="139660" P2="139660"
In my ExDesc field for the PI Tag I have P1="139660" P2="139660"
Anyone have any ideas on what I'm missing?