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.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].