Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

SQL Query bad results

Hi experts, I need to solve the results of follows query:
SELECT utchora
           , pointnumber
           , siteid
           , MAX (DECODE (rn, 1, valor_inst)) valor_inst
           , MAX (DECODE (rn, 1, tlq_inst)) tlq_inst
           , AVG (valor_inst) valor_prom
           , case when max(bitand(tlq_inst, 168)) = 0                 
               then                                                    
             max(tlq_inst) keep (dense_rank first order by utctime)  
             else 8
             end as tlq_prom
           , MAX (DECODE (max0, 1, valor_inst)) valor_max
           , MAX (DECODE (max0, 1, tlq_inst)) tlq_max
           , MAX (DECODE (max0, 1, utctime)) utctime_max
           , MAX (DECODE (min0, 1, valor_inst)) valor_min
           , MAX (DECODE (min0, 1, tlq_inst)) tlq_min
           , MAX (DECODE (min0, 1, utctime)) utctime_min
        FROM (
  
        SELECT TRUNC ( utctime, 'HH24')  utchora 
             , utctime 
             , pointnumber
             , siteid
             , valor_inst
             , tlq_inst
             , valor_prom
             , tlq_prom
             , valor_max
             , tlq_max
             , utctime_max
             , valor_min
             , tlq_min
             , utctime_min
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY utctime /*DESC*/) rn
             , AVG (valor_inst) OVER (PARTITION BY pointnumber ORDER BY utctime DESC) av0
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY valor_inst DESC) max0
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY valor_inst) min0
        FROM a_5min_023
       WHERE TRUNC ( utctime, 'HH24') >= to_date('11/08/2014 03:00:00','dd/mm/yyyy hh24:mi:ss') 
       AND TRUNC ( utctime, 'HH24') < to_date('11/08/2014 04:00:00','dd/mm/yyyy hh24:mi:ss') 
       AND POINTNUMBER = 230001
       
       )        
       GROUP BY pointnumber, utchora, siteid ;

Open in new window

Results:
UTCHORA|POINTNUMBER|SITEID|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX|VALOR_MIN|TLQ_MIN|UTCTIME_MIN
11/08/2014 03:00:00 a.m.|230001||0|32|0|8||||0|32|11/08/2014 03:00:00 a.m.
11/08/2014 03:00:00 a.m.|230001|0|||6|8|11|8|11/08/2014 03:55:00 a.m.|||

Open in new window

The problems are:
1 - Returns 2(two) rows with results. I need to get just one row.
2 - I need obtain the average of the segment with:
 AVG (valor_inst) valor_prom 

Open in new window

But never is calculated.
Could you help me to found the solutions?
All the scripts, attached.
Thankyou in advanced
Regards
table-023.sql
inserts.sql
0
carlino70
Asked:
carlino70
  • 3
  • 3
1 Solution
 
sdstuberCommented:
looks like your data above is just the current results, I can get those by running the query you posted.

What are the results you WANT?
0
 
carlino70Author Commented:
ok. I want obtain:
UTCHORA                 |POINTNUMBER|SITEID|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX             |VALOR_MIN|TLQ_MIN|UTCTIME_MIN             
11/08/2014 03:00:00 a.m.|230001     |0     |0         |32      |6         |8       |11       |8      |11/08/2014 03:55:00 a.m.|0        |32     |11/08/2014 03:00:00 a.m.

Open in new window

The valor_prom must be AVG(valor_inst), but not is calculated.
Thankyou
0
 
sdstuberCommented:
I don't understand -  your expected data and your description don't seem to match.

How can VALOR_PROM be 6 if it's the AVG(valor_inst) which is 0 and null.  Nulls are skipped in averages, so it's just AVG(0) which is 0,  not 6
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
sdstuberCommented:
also, one of your sideids is null, so, grouping by siteid with null in one value and 0 in all the rest will always result in 2 rows.

Are all of your insert statements correct?  Maybe that null shouldn't be there
0
 
carlino70Author Commented:
Yes Sr., the problem are the null values for siteid.

For the AVG(valor_inst) now is correct, because is calculated with 12 registers, given by the utctime segment chosen.

I appreciate your help, it was very useful to me

Thankyou
0
 
carlino70Author Commented:
Excellent
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now