Re: SQL Check If File Exists (via RPG)

On 03-Nov-2015 10:03 -0600, Mike Jones wrote:

I'd use this SQL to check for file existence, unless I had a reason
not to. I've not used this in a trigger before, but I've used code
like this in lots of SQL objects.

select char( case when TABLE_NAME is null then '0' else '1'
end )
from ( values( 1 ) ) as V
into :TABLE_EXISTS
left join QSYS2.SYSTABLES
on TABLE_SCHEMA = :MY_SCHEMA_NAME
and TABLE_NAME = :MY_TABLE_NAME
;

FWiW, the following eliminates the LEFT JOIN or an empty scalar fullselect being used to force a NULL result to then be [literally or effectively] coalesced, by using the COUNT instead; and using the COUNT(TABLE_NAME) would be essentially the same, but naming a column in the aggregate function results in a tiny bit of unnecessary work:

select char( dec( count(*), 1 ) ) as exists_ind
from qsys2.systables
into :table_exists
where table_schema = :my_schema_name
and table_name = :my_table_name
fetch first 1 row only /* optional hint */

--Or, if you prefer:

values
coalesce ( (
select char( '1', 1 )
from QSYS2.SYSTABLES
where TABLE_SCHEMA = :MY_SCHEMA_NAME
and TABLE_NAME = :MY_TABLE_NAME
), '0' ) into :TABLE_EXISTS

I don't recall the RPG embedding rules for the 2nd SQL, but the 1st
one should embed with simple static SQL.

And FWiW, similar to the above request using the COUNT aggregate instead:

values( select char( dec( count(*), 1 ) ) as exists_ind
from qsys2.systables
where table_schema = :my_schema_name
and table_name = :my_table_name
fetch first 1 row only /* optional hint */
)

I still expect the OP is best served by *not* performing any proactive check for the existence, and instead relying on reactively dealing with a missing\not-found object, as diagnosed by the failing SQL DML. Why check in advance of the SQL statement when both, that the upcoming SQL statement will do the existence-check anyhow and that the expectation was noted that the upcoming SQL statement will rarely, if ever, fail with the -204; i.e. the proactive check essentially becomes\serves-as busywork for all but the rare exception-cases.