Create Table with Data Returns 0 rows

Database

Create Table with Data Returns 0 rows

The following query seems to create a table with zero rows. The SELECT statement on Line 2 returns the proper number of rows if run alone.  I'm trying to use this statment to create another table then display return results.

 

Upon completion, I get the following Message:

Statement 1: CREATE TABLE completed. 0 rows processed. Elapsed Time = 00:00:00
Statement 2: SELECT completed. 0 rows returned. Elapsed Time = 00:00:00
Statement 3: DROP TABLE completed. 0 rows processed. Elapsed Time = 00:00:00

 

 

 

CREATE VOLATILE TABLE kW_Reading AS (
  SELECT m.meter_id, mr.meas AS kW
  FROM DB.METER m
  JOIN DB.METER_READING mr ON m.meter_id = mr.meter_id AND reading_start_dt = '2016-07-24'
  WHERE Reading_Dttm = '2016/07/24 18:00:00' AND mr.channel = 1 AND m.meter_id IS IN (
    SELECT m.meter_id
    FROM DB.METER m
    JOIN DB.METER_READING mr ON m.meter_id = mr.meter_id AND reading_start_dt = '2016-07-24'
    JOIN DB.SP_Meter spm ON m.meter_id = spm.meter_id
    JOIN DB.SP sp ON sp.spid = spm.spid
    JOIN DB.Rates rates ON rates.premise_number = sp.prid
    WHERE Reading_Dttm = '2016/07/24 18:00:00' AND rates.STATUS_CODE = 'A' AND mr.channel = 2
    )
  )
WITH DATA;

 

SELECT * FROM kW_Reading;

 

DROP TABLE kW_Reading;

2 REPLIES
rjg
Supporter

Re: Create Table with Data Returns 0 rows

You need to add 'on commit preserve rows' to you create statement.

Re: Create Table with Data Returns 0 rows

Thank you.