CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
elszeus
elszeus

Reputation: 131

Multiple SELECTS and a CTE table

I have this statement which returns values for the dates that exist in the table, the cte then just fills in the half hourly intervals.

    with cte (reading_date) as (
    select date '2020-11-17' from dual
    union all
    select reading_date + interval '30' minute 
    from cte 
    where reading_date + interval '30' minute < date '2020-11-19'
)
select c.reading_date, d.reading_value
from cte c
left join dcm_reading d on d.reading_date = c.reading_date
order by c.reading_date

However, later on I needed to use A SELECT within a SELECT like this:

   SELECT serial_number,
register,
reading_date,
reading_value,,
ABS(A_plus)
FROM
(
SELECT
serial_number,
register,
TO_DATE(reading_date, 'DD-MON-YYYY HH24:MI:SS') AS reading_date,
reading_value,
LAG(reading_value,1, 0) OVER(ORDER BY reading_date) AS previous_read,
LAG(reading_value, 1, 0) OVER (ORDER BY reading_date) - reading_value AS A_plus,
reading_id
FROM DCM_READING
WHERE device_id = 'KXTE4501'
AND device_type = 'E'
AND serial_number = 'A171804699'
AND reading_date BETWEEN TO_DATE('17-NOV-2019' || ' 000000', 'DD-MON-YYYY HH24MISS') AND  TO_DATE('19-NOV-2019'   || ' 235959', 'DD-MON-YYYY HH24MISS')
ORDER BY reading_date)
ORDER BY serial_number, reading_date;

For extra information:

I am selecting data from a table that exists, and using lag function to work out difference in reading_value from previous record. However, later on I needed to insert dummy data where there are missing half hour reads. The CTE table brings back a list of all half hour intervals between the two dates I am querying on.

ultimately I want to get a result that has all the reading_dates in half hour, the reading_value (if there is one) and then difference between the reading_values that do exist. For the half hourly reads that don't have data returned from table DCM_READING I want to just return NULL.

Is it possible to use a CTE table with multiple selects?

Upvotes: 0

Views: 966

Answers (1)

Michal
Michal

Reputation: 41

Not sure what you would like to achieve, but you can have multiple CTEs or even nest them:

with 

cte_1 as
(
    select username
    from dba_users
    where oracle_maintained = 'N'
),

cte_2 as
(
    select owner, round(sum(bytes)/1024/1024) as megabytes
    from dba_segments
    group by owner
),

cte_3 as
(
    select username, megabytes
    from cte_1
    join cte_2 on cte_1.username = cte_2.owner
)

select *
from cte_3
order by username;

Upvotes: 1

Related Questions