Re: SQL Check If File Exists (via RPG)

Hi Roger,

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
;

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

Mike

date: Mon, 2 Nov 2015 15:48:12 -0800
from: Roger Harman <roger.harman@xxxxxxxxxxx>
subject: SQL Check If File Exists (via RPG)

I have a situation where I need to update/add/delete records in a
historical snapshot file (or files) if the current live file is changed
(retro-active changes).?

The historical files are named by year/month and may or may not exist for
a?selected?record.

Looking for suggestions on the quickest easiest way to verify a file's
existence. ?This will be in a trigger program.

Thanks.


Roger Harman
COMMON Certified Application Developer ? ILE RPG on IBM i on Power