AlwaysInTheDark AlwaysInTheDark - 8 days ago 4
SQL Question

SQL Creating a cloumn in a view with the results from cross-referencing two tables

I'm new to SQL and was attempting to create a view that combines data from a database of readings and from a database of failures. I wanted to create a view ordered by target name, then metric name, then by timestamp, with an additional column that returns a 1 to say there was a failure on that day and a zero otherwise. The query I've written is currently reading that I'm missing a right parenthesis, but when I eliminate the parenthesis it finds the table names invalid. I'm unsure whether my use of case is causing it, although it has worked on some practice samples. Any help checking this and suggestions on how to improve it would be much appreciated.

SELECT * FROM
(
with new_failure_table as (
SELECT target_name, END_TIMESTAMP,START_TIMESTAMP,
((END_TIMESTAMP - (START_TIMESTAMP))*24*60)
FROM failure_table
WHERE (END_TIMESTAMP - (START_TIMESTAMP))*24*60 >5
AND (END_TIMESTAMP - START_TIMESTAMP) < 1
and availability_status = 'Target Down'
)
-- Simplifies failure table to include actual failures according to two parameters

SELECT
t1.target_name,
t1.metric_name,
t1.rollup_timestamp,
t1.average,
t1.minimum,
t1.maximum,
t1.standard_deviation,
t2.END_TIMESTAMP,
t2.START_TIMESTAMP,
(CASE
when t1.target_name = t2.target_name
and t1.rollup_timestamp = trunc(END_TIMESTAMP+1)
and t1.rollup_timestamp = trunc(START_TIMESTAMP+1)
THEN '1' ELSE '0' END) AS failure_status
--Used to create column that reads 1 when there was a failure between the two readings and 0 otherwise

FROM
data_readings AS t1, new_failure_table AS t2
WHERE t1.target_name = t2.target_name
)

GROUP BY t1.target_name, metric_name
ORDER BY rollup_timestamp desc;

Answer

You do not need to wrap case in parenthesis instead it can be

CASE  
when t1.target_name = t2.target_name 
and t1.rollup_timestamp = trunc(END_TIMESTAMP+1) 
and t1.rollup_timestamp = trunc(START_TIMESTAMP+1) 
THEN '1' ELSE '0' END AS failure_status

Also in FROM do not use AS instead of:

...
FROM data_readings AS t1, new_failure_table AS t2 
...

use

...
FROM data_readings t1, new_failure_table t2 
...

UPD: The whole query should look like this

SELECT * FROM
(
with new_failure_table as  (
    SELECT target_name, END_TIMESTAMP,START_TIMESTAMP, 
    ((END_TIMESTAMP - (START_TIMESTAMP))*24*60) 
    FROM failure_table 
    WHERE (END_TIMESTAMP - (START_TIMESTAMP))*24*60 >5 
    AND  (END_TIMESTAMP - START_TIMESTAMP) < 1
    and availability_status = 'Target Down'
    ) 
    -- Simplifies failure table to include actual failures according to two parameters

SELECT
    t1.target_name as target_name,
    t1.metric_name as metric_name,
    t1.rollup_timestamp as rollup_timestamp,
    t1.average,
    t1.minimum,
    t1.maximum,
    t1.standard_deviation,
    t2.END_TIMESTAMP,
    t2.START_TIMESTAMP,
    CASE  
    when t1.target_name = t2.target_name 
    and t1.rollup_timestamp = trunc(END_TIMESTAMP+1) 
    and t1.rollup_timestamp = trunc(START_TIMESTAMP+1) 
    THEN '1' ELSE '0' END AS failure_status
    --Used to create column that reads 1 when there was a failure between the two readings and 0 otherwise    

FROM
    data_readings t1, new_failure_table t2 
WHERE t1.target_name = t2.target_name       
)
GROUP BY target_name, metric_name
ORDER BY rollup_timestamp desc;