Combining max(nvl(case produces incorrect result for negative numbers

remove the NVL(  ,0) ?

try this:

select to_char(time,'MM/DD/YYYY') time, to_char(time,'HH24') hour,
      max(case when Element = '06000000' then value end) element1value,
      max(case when Element = '06000001' then value end) element2value,
      max(case when Element = '06000002' then value end) element3value,
      max(case when Element = '06000003' then value end) element4value,
      max(case when Element = '06000004' then value end) element5value
from tab1
group by to_char(time,'MM/DD/YYYY'), to_char(time,'HH24')
order by time

regarding this:

Also, I awarded you 400 points for your previous help but the question still shows that it is open.  If I need to do something to make sure that you get the 400 points please let me know.

Could I suggest you use a message to slightwv, instead of a question?

in the top right corner (beside the bell icon) is an envelope icon, there you can address your message to a particular member (and only seen by those you send it to)

Paul,
What you suggest gets the negative numbers but the part I have to sum up the row does not work now.  Also if an element only has zeros in the database it returns blanks.  Below is what I have so far.

select Time, HE,
element1value+element2value+element3value+element4value)+
element5value
RowSum
from
(
select
      to_char(time,'MM/DD/YYYY') time, to_char(time,'HH24') hour,
      max(case when Element = '06000000' then value end) element1value,
      max(case when Element = '06000001' then value end) element2value,
      max(case when Element = '06000002' then value end) element3value,
      max(case when Element = '06000003' then value end) element4value,
      max(case when Element = '06000004' then value end) element5value
from tab1
group by to_char(time,'MM/DD/YYYY'), to_char(time,'HH24')
order by time

>>.  If I need to do something to make sure that you get the 400 points please let me know.

Just re-accept it.

>>it will not handle negative numbers (they are replaced with a zero).  

If value is negative, it should work just fine.  Please post sample data and expected results.

perhaps you could let us see some results?
but my guess this time is:


select
      to_char(time,'MM/DD/YYYY') time, to_char(time,'HH24') hour,
      NVL( max(case when Element = '06000000' then value end) ,0) element1value,
      NVL( max(case when Element = '06000001' then value end) ,0) element2value,
      NVL( max(case when Element = '06000002' then value end) ,0) element3value,
      NVL( max(case when Element = '06000003' then value end) ,0) element4value,
      NVL( max(case when Element = '06000004' then value end) ,0) element5value
from tab1
group by to_char(time,'MM/DD/YYYY'), to_char(time,'HH24')

>>"If value is negative, it should work just fine. "

If the column has only negative numbers for a given value of element, then NVL(   ,0) would give a maximum of zero, hence it affects the result.

but if the MAX() returns NULL it could be set to 0

>>If the column has only negative numbers for a given value of element,

If you add "has at least one null value", then AHHH, I see it now.  Please ignore my previous post.

yes, that is what I had intended, sorry

If the column is: only (one or more) negative numbers, and (one of more) NULLs

then MAX(NVL( ... ,0))  would be zero, and would be incorrect

I have attached what I have so far.  Message.docx contains log data that I am trying to extract (one page of it anyway) .  Generator.txt contains the data I have been able to extract.  The columns do not line up so I have transposed the names from Message.docx to the columns that contain data in the Generator.txt file.  You can easily tell that columns are missing.  Note that the first three columns do not show up even though they contain data.  Hope this helps.
Generator.txt
message.docx

Forgot to send the code that I have so far...
gen.sh

What you posted didn't help me that much.  What we are looking for is a simplified model that shows us what you are trying to achieve so we can set it up on our systems and post tested code.

Did you try Paul's suggestion in http:#a40976032 ?

Using my test case from your previous question, it appears that it provides what you are asking for.

If it doesn't please add to the test case and tell us where it isn't working.

drop table tab1 purge;
create table tab1(time date, element char(8), value number);

insert into tab1 values(to_date('08/21/2015 01','MM/DD/YYYY HH24'),'06000000',-10);
insert into tab1 values(to_date('08/21/2015 01','MM/DD/YYYY HH24'),'06000001',1);
insert into tab1 values(to_date('08/21/2015 01','MM/DD/YYYY HH24'),'06000002',2);
insert into tab1 values(to_date('08/21/2015 01','MM/DD/YYYY HH24'),'06000003',3);
--
insert into tab1 values(to_date('08/21/2015 02','MM/DD/YYYY HH24'),'06000000',4);
insert into tab1 values(to_date('08/21/2015 02','MM/DD/YYYY HH24'),'06000001',5);
insert into tab1 values(to_date('08/21/2015 02','MM/DD/YYYY HH24'),'06000002',6);
insert into tab1 values(to_date('08/21/2015 02','MM/DD/YYYY HH24'),'06000003',6);
commit;


select
mytime,
myhour,
element1value,
element2value,
element3value,
element4value,
element5value,
element1value+element2value+element3value+element4value+element5value SumOfRowOfValues
from (
select
	mytime,
	myhour, 
	nvl(max(case when Element = '06000000' then value end),0) element1value,
	nvl(max(case when Element = '06000001' then value end),0) element2value,
	nvl(max(case when Element = '06000002' then value end),0) element3value,
	nvl(max(case when Element = '06000003' then value end),0) element4value,
	nvl(max(case when Element = '06000004' then value end),0) element5value
from tab1
right outer join (
select '08/21/2015' mytime, to_char(level,'fm00') myhour from dual connect by level <= 23
) hours on to_char(tab1.time,'HH24')=hours.myhour and to_char(tab1.time,'MM/DD/YYYY')=hours.mytime
group by
	mytime,
	myhour
order by to_number(myhour)
)
where myhour <= case when to_char(sysdate,'MM/DD/YYYY')='08/21/2015' then 23 else to_number(to_char(sysdate,'HH24')) end
/

Select allOpen in new window

Paul,
I like what you have done with the code, it is a lot cleaner, easier to follow and the output file looks like it should.  I do have a couple of additional problems I need help with.  Is there a way to truncate or round "RowSum"?  Also, I know you tried to help me before on this but I did not understand.  I need the first row of the day to be hour ending 1 AM for the current day and the last row to be hour ending 24 (midnight) of the next day.  The script we have been working with will run 10 minutes after midnight to get values placed in the database for the previous hour (hour ending 24).  The hourly time stamp for our system is 0-23 hours.  I have tried to use "DateAdd" but I am not having any joy.   Hope you can help.

Thanks

>>"I need the first row of the day to be hour ending 1 AM for the current day and the last row to be hour ending 24"

The first ten digits of our number system are 0,1,2,3,4,5,6,7,8,9 &
The twenty four hours of a day are 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23

The last row should be 23

24 is a "point in time" that is so infinitesimally small you cannot measure it; AND that very same tiny wee point in time is equal to another point in time: zero. i.e. midnight is BOTH the end of a day and the commencement of a new day

00:00:00+00000 = 24:00:00+00000 (or vice versa)

------------ SO ----------------

In my honest professional opinion, you should report hours as zero ....to ... twenty three

----------- BUT ---------------

You seem determined to use an "ordinal" hour (1st hour ..... 24th hour), so:

Here is some sample data:

CREATE TABLE TABLE1
    (TIME date)
;

INSERT ALL 
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 00:10:23','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 01:25:42','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 02:41:00','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 03:56:19','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 05:11:38','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 06:26:56','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 07:42:15','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 08:57:34','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 10:12:53','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 09:06:38','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 11:37:16','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 12:52:34','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 14:07:53','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 15:23:12','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 16:38:31','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 17:53:49','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 19:09:08','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 20:24:27','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 21:39:45','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 22:55:04','YYYY-MM-DD hh24:mi:ss'))
  INTO TABLE1 ("TIME") VALUES (to_date('2015-09-16 23:21:57','YYYY-MM-DD hh24:mi:ss'))
SELECT * FROM dual
;

Select allOpen in new window

If you use this query:

select
      TRUNC(time,'HH24')
    , to_char(time,'HH24')
    , to_number(to_char(time,'HH24')) 
    , (to_number(to_char(time,'HH24')) + 1)
    , substr('0' || (to_number(to_char(time,'HH24')) + 1),-2)
from table1

Select allOpen in new window

You would get this result:

|          TRUNC(TIME,'HH24') | TO_CHAR(TIME,'HH24') | TO_NUMBER(TO_CHAR(TIME,'HH24')) | (TO_NUMBER(TO_CHAR(TIME,'HH24'))+1) | SUBSTR('0'||(TO_NUMBER(TO_CHAR(TIME,'HH24'))+1),-2) |
|-----------------------------|----------------------|---------------------------------|-------------------------------------|-----------------------------------------------------|
| September, 16 2015 00:00:00 |                   00 |                               0 |                                   1 |                                                  01 |
| September, 16 2015 01:00:00 |                   01 |                               1 |                                   2 |                                                  02 |
| September, 16 2015 02:00:00 |                   02 |                               2 |                                   3 |                                                  03 |
| September, 16 2015 03:00:00 |                   03 |                               3 |                                   4 |                                                  04 |
| September, 16 2015 05:00:00 |                   05 |                               5 |                                   6 |                                                  06 |
| September, 16 2015 06:00:00 |                   06 |                               6 |                                   7 |                                                  07 |
| September, 16 2015 07:00:00 |                   07 |                               7 |                                   8 |                                                  08 |
| September, 16 2015 08:00:00 |                   08 |                               8 |                                   9 |                                                  09 |
| September, 16 2015 10:00:00 |                   10 |                              10 |                                  11 |                                                  11 |
| September, 16 2015 09:00:00 |                   09 |                               9 |                                  10 |                                                  10 |
| September, 16 2015 11:00:00 |                   11 |                              11 |                                  12 |                                                  12 |
| September, 16 2015 12:00:00 |                   12 |                              12 |                                  13 |                                                  13 |
| September, 16 2015 14:00:00 |                   14 |                              14 |                                  15 |                                                  15 |
| September, 16 2015 15:00:00 |                   15 |                              15 |                                  16 |                                                  16 |
| September, 16 2015 16:00:00 |                   16 |                              16 |                                  17 |                                                  17 |
| September, 16 2015 17:00:00 |                   17 |                              17 |                                  18 |                                                  18 |
| September, 16 2015 19:00:00 |                   19 |                              19 |                                  20 |                                                  20 |
| September, 16 2015 20:00:00 |                   20 |                              20 |                                  21 |                                                  21 |
| September, 16 2015 21:00:00 |                   21 |                              21 |                                  22 |                                                  22 |
| September, 16 2015 22:00:00 |                   22 |                              22 |                                  23 |                                                  23 |
| September, 16 2015 23:00:00 |                   23 |                              23 |                                  24 |                                                  24 |

Select allOpen in new window

Note I have shown TRUNC(time,'HH24') because TRUNC is just so useful for date/time aggregations.

I would expect you to be able to take this information and make it work for your query.

If not I suggest you ask a new question. (and if asking a new question please do not address he question to any specific expert)

>>"Is there a way to truncate or round "RowSum"? "

Oracle offers both
TRUNC()
ROUND()
e.g.

SELECT....

     , ROUND(
             JeffHydr + StStHydr + Spillway + JeffOil1 + JeffOil2 + JeffStm3 + JeffStm4 + Winyah_1 + Winyah_2 + Winyah_3 + Winyah_4 
             + Cross_1 + Cross_2 + Cross_3 + Cross_4 + Hilton_1 + Hilton_2 + Hilton_3 + Myrtle_1 + Myrtle_2 + Myrtle_3 + Myrtle_4 
             + Myrtle_5 + Rainey_1 + Rainey_2 + Rainey_3 + Rainey_4 + Rainey_5 + Rainey_6 + HG + Lee + Rich + Geotown 
             + Berk + Domtar + VSG + Cater + BioEng + BioDor + BioAll + IPCO + CEC RowSum
         , 2)
FROM (

Select allOpen in new window

You should find this categorized list of Oracle functions a useful reference.

I tried Round(RowSum,0) before I sent the last comment and to me it should have worked...I get the error message  ORA-00907 (please see attached file) when I try the last suggestion.

Thanks
error.txt

Im sorry but I have answered more than one question already.

The ROUND function does work.

Thanks for the solution.  To me SQL has a quirky syntax and it is going to take me time to get use to.

 And now just for grins and giggles,

All utilities (as far as I know) gas, electric, oil, etc. use hour ending clock time.  It was agreed upon (by PE's and bean counters) and blessed by the Government eons ago to basically make sure that when things like billing between companies take place that everyone uses the same clock time for reading meters, generation, scheduling, etc..

I hope the point spread is acceptable now.  Thanks for the help.