Error when placing a WHEN in SQL query - ORACLE

Navigation
0

I have a query in oracle: 

WITH hist_snaps
            AS (SELECT instance_number,
            snap_id,
            round(begin_interval_time,'MI') datetime,
            (  begin_interval_time + 0 - LAG (begin_interval_time + 0)
            OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
            FROM dba_hist_snapshot), hist_stats
            AS (SELECT dbid,
            instance_number,
            snap_id,
            stat_name,
            VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
            delta_value
            FROM dba_hist_sysstat
            WHERE stat_name IN ('user commits', 'user rollbacks'))
            SELECT datetime,
            ROUND (SUM (delta_value) / 3600, 2) as transactions
            FROM hist_snaps sn, hist_stats st, DUAL
            WHERE     st.instance_number = sn.instance_number
            AND st.snap_id = sn.snap_id
            AND datetime >= CURRENT_DATE - 1
            AND diff_time IS NOT NULL
            GROUP BY datetime
            ORDER BY 1

This returns me 2 columns like this:

I am trying to make a WHEN so that when the data that is in transactions is < 0 I put 0 in that cell.

Try this code but I get an error: 

WITH hist_snaps
            AS (SELECT instance_number,
            snap_id,
            round(begin_interval_time,'MI') datetime,
            (  begin_interval_time + 0 - LAG (begin_interval_time + 0)
            OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
            FROM dba_hist_snapshot), hist_stats
            AS (SELECT dbid,
            instance_number,
            snap_id,
            stat_name,
            VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
            delta_value
            FROM dba_hist_sysstat
            WHERE stat_name IN ('user commits', 'user rollbacks'))
            SELECT datetime,
            CASE ROUND (SUM (delta_value) / 3600, 2) as transactions1
                WHEN transactions1 < 0 THEN 0
            END AS transactions
            FROM hist_snaps sn, hist_stats st, DUAL
            WHERE     st.instance_number = sn.instance_number
            AND st.snap_id = sn.snap_id
            AND datetime >= CURRENT_DATE - 1
            AND diff_time IS NOT NULL
            GROUP BY datetime
            ORDER BY 1
    
asked by Jesús Henríquez 12.09.2018 в 18:47
source

1 answer

0

I think you can not use the result of the alias in the same query, try this: 

WITH hist_snaps
            AS (SELECT instance_number,
            snap_id,
            round(begin_interval_time,'MI') datetime,
            (  begin_interval_time + 0 - LAG (begin_interval_time + 0)
            OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
            FROM dba_hist_snapshot), hist_stats
            AS (SELECT dbid,
            instance_number,
            snap_id,
            stat_name,
            VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
            delta_value
            FROM dba_hist_sysstat
            WHERE stat_name IN ('user commits', 'user rollbacks'))
            SELECT datetime,
            CASE 
            WHEN ROUND (SUM (delta_value) / 3600, 2) < 0 THEN 0
            END AS transactions
            FROM hist_snaps sn, hist_stats st, DUAL
            WHERE     st.instance_number = sn.instance_number
            AND st.snap_id = sn.snap_id
            AND datetime >= CURRENT_DATE - 1
            AND diff_time IS NOT NULL
            GROUP BY datetime
            ORDER BY 1
    
answered by 12.09.2018 в 18:58
How to make a post from Express to return a Json with the variables entered in a form? How can I make a search engine in native reply using asyncstorage in a listview?