AnsweredAssumed Answered

RDBMS Oracle Select Statement

Question asked by jirvine on Mar 3, 2020
Latest reply on Mar 4, 2020 by gmichaud-verreault

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
from PI.View
where loc_id = '139660'
union all
select null from dual where not exists (select 1 from PI.View where loc_id = '139660')), 0)
from dual;


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?