RE: CREATE TABLE how to avoid empty table

He doesn't even need a CTE to leverage the COUNT function, i.e.:

SELECT COUNT(*) INTO :myHostVariable FROM myTable WHERE .....;

IF myHostVariable > 0 then
CREATE TABLE ....
END IF;

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: CREATE TABLE how to avoid empty table

David -
I'm assuming this is in an RPG program.

One way is to use a Common Table Expression to perform the select statement
beforehand and get a record count of the selected records *before* running
the CREATE TABLE SQL statement, then if the record count is > 0, run the
CREATE TABLE.

Example:

With CTE as
(Select field1, field2, field3 from Table1 where field1 = '1' and field2 =
'2' )
Select count(*) from CTE into :reccount

If reccount > 0;

do your create table here...

endif;