Oracle SQL*Plus to Select an Array of Values from a Database
With an Oracle 10g database I am trying to use sqlplus to extract some values. Each value is associated with a discreet element and there are 40 of them. The output file needs to look like the following:
08/21/2015 01 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
08/21/2015 02 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
08/21/2015 03 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
.
.
.
08/22/2015 24 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
It would take me pretty much forever to figure out how to do this because databases and sqlplus are brand new to me. Hopefully, one of you experts can do this in three lines of code or less (just joking).
Thanks
08/21/2015 01 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
08/21/2015 02 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
08/21/2015 03 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
.
.
.
08/22/2015 24 Element1Value Element2Value Element3Value ...Element40Value SumofElements1-40
It would take me pretty much forever to figure out how to do this because databases and sqlplus are brand new to me. Hopefully, one of you experts can do this in three lines of code or less (just joking).
Thanks
set pages 0
set head off
set feed off
alter session set nls_date_format='MM/DD/YYYY HH24';
select Time, Element, Value from DataValues
where Element = '06000000'
and Element = '06000001'
and Element = '06000002'
and Element = '06000003'
and Element = '06000004'
and Element = '06000005'
and Element = '06000006'
and Element = '06000007'
and Element = '06000008'
.
.
.
and Element = '06000040'
and to_number(to_char(TIME,'HH24')) <= TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))
and to_number(to_char(TIME,'DD' )) = TO_NUMBER(TO_CHAR(SYSDATE,'DD' ))
order by TIME;
exit; 
It won't be so easy using 10g as it doesn't support recursive SQL and this is definitely a recursive application. Probably the most effective will be a stored procedure (or PLSQL script) to open the source table with a cursor and fetch the data 1 row at a time to build the result string and sum.
If you can connect to the 10g database from a newer version of Oracle (11g or higher) recursive SQL is supported. The query will look something like this:
WITH x
--(ST, C, SM)
AS
(
SELECT element || '=' || value ST,
cast (element as integer) C,
Value SM FROM D
WHERE element = (select min(element) from D)
UNION ALL
SELECT ST || ',' || D.element || '=' || value ST,
cast (element as integer) C,
x.SM +D.VALUE SM
FROM X, D
WHERE X.c + 1 = cast (D.element as integer)
)
SELECT ST || ',SUM=' || cast (SM as integer)
FROM x
WHERE C = (SELECT max(C) FROM x);
If you can connect to the 10g database from a newer version of Oracle (11g or higher) recursive SQL is supported. The query will look something like this:
WITH x
--(ST, C, SM)
AS
(
SELECT element || '=' || value ST,
cast (element as integer) C,
Value SM FROM D
WHERE element = (select min(element) from D)
UNION ALL
SELECT ST || ',' || D.element || '=' || value ST,
cast (element as integer) C,
x.SM +D.VALUE SM
FROM X, D
WHERE X.c + 1 = cast (D.element as integer)
)
SELECT ST || ',SUM=' || cast (SM as integer)
FROM x
WHERE C = (SELECT max(C) FROM x);
Agreed further description would be helpful, although right off hand, you should replace your "and"s with "or"s,
where Element = '06000000'
or Element = '06000001'
Even better would be to use 'in'
where element in ('06000000','06000001','06
of course we wont know more til we can see the small test case with table, sample data and expected results.
where Element = '06000000'
or Element = '06000001'
Even better would be to use 'in'
where element in ('06000000','06000001','06
of course we wont know more til we can see the small test case with table, sample data and expected results.
Please provide sample data from the table DataValues
The following
The following
where Element = '06000000'
and Element = '06000001'
and Element = '06000002'
and Element = '06000003'
and Element = '06000004'
and Element = '06000005'
and Element = '06000006'
and Element = '06000007'
and Element = '06000008'
.
.
.
and Element = '06000040'
can be simplified toand Element = '06000001'
and Element = '06000002'
and Element = '06000003'
and Element = '06000004'
and Element = '06000005'
and Element = '06000006'
and Element = '06000007'
and Element = '06000008'
.
.
.
and Element = '06000040'
where Element between '06000000' and '06000040'
assuming you want all of that range.
Since you haven't posted back, I have to guess at what you want.
See if the code below is what you want. If so, just add the additional columns.
If produces:
See if the code below is what you want. If so, just add the additional columns.
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',0);
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 to_char(time,'MM/DD/YYYY') time, to_char(time,'HH24') hour,
max(nvl(case when Element = '06000000' then value end,0)) element1value,
max(nvl(case when Element = '06000001' then value end,0)) element2value,
max(nvl(case when Element = '06000002' then value end,0)) element3value,
max(nvl(case when Element = '06000003' then value end,0)) element4value,
max(nvl(case when Element = '06000004' then value end,0)) element5value
from tab1
group by to_char(time,'MM/DD/YYYY'), to_char(time,'HH24')
order by time
/If produces:
TIME HO ELEMENT1VALUE ELEMENT2VALUE ELEMENT3VALUE ELEMENT4VALUE ELEMENT5VALUE
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 01 0 1 2 3 0
08/21/2015 02 4 5 6 6 0ASKER
Sorry I have not gotten back to you guys, had a family emergency. I will look over what has been suggested to me and will get back to you. I am trying to get is the date, hour and values associated with the what I am calling Elements. The Elements are not contiguous as I have suggested but are more like this:
'05128002', '05022001', '05040002', '05066033'. The code will run once a hour and bring back hourly readings that need to be summed by row.
08/21/2015 01 250 158 257 311...976
08/21/2015 02 252 160 266 323...1001
08/21/2015 03 253 142 251 315...961
I cannot insert data into this database, I am only allowed to read data from it. Please let me know if you need more information.
Thanks
'05128002', '05022001', '05040002', '05066033'. The code will run once a hour and bring back hourly readings that need to be summed by row.
08/21/2015 01 250 158 257 311...976
08/21/2015 02 252 160 266 323...1001
08/21/2015 03 253 142 251 315...961
I cannot insert data into this database, I am only allowed to read data from it. Please let me know if you need more information.
Thanks
Hi dwortman,
About how many rows do you expect to report every hour?
About how many rows do you expect to report every hour?
>>I cannot insert data into this database, I am only allowed to read data from it. Please let me know if you need more information.
Who asked you to insert data into the database? The only inserts were to populate the test case tables.
I still don't understand what you are asking us to assist with.
Please post sample raw data and expected results. Similar to what I provided in my example.
Who asked you to insert data into the database? The only inserts were to populate the test case tables.
I still don't understand what you are asking us to assist with.
Please post sample raw data and expected results. Similar to what I provided in my example.
ASKER
I need to keep reading the database each hour in case an Elements value has been changed. If it is 1400 hrs I would need to reread the database from 0100 hrs up to the current time. The time on our Linux system goes from 0000 to 2300 hrs, if I wrote to a text file the first entry would be hour ending 0100 hrs for the current day until hour ending 0000 (or 2400) hrs the next day. I need the hours to go from 01 until 24 as shown below:
08/21/2015 01 250 158 257 311...976
08/21/2015 02 252 160 266 323...1001
08/21/2015 03 253 142 251 315...961
.
.
.
08/22/2015 24 254 165 300 310...1029
08/22/2015 01 225 160 250 300...935
I am re-sending this comment because I do not know if it got through or not...
08/21/2015 01 250 158 257 311...976
08/21/2015 02 252 160 266 323...1001
08/21/2015 03 253 142 251 315...961
.
.
.
08/22/2015 24 254 165 300 310...1029
08/22/2015 01 225 160 250 300...935
I am re-sending this comment because I do not know if it got through or not...
Please post some sample raw data to go with the expected results. I do not understand the problem by your explanations. I realize you want something grouped by the hour of the day.
The query I posted returns the value based on the element and groups by the hour.
If you need something different, please post raw sample data to go with the expected results.
Also for the results, please show the column headers so we get an idea of what the numbers actually represent.
The query I posted returns the value based on the element and groups by the hour.
If you need something different, please post raw sample data to go with the expected results.
Also for the results, please show the column headers so we get an idea of what the numbers actually represent.
ASKER
slightwv,
I was able to take your suggestion and modify the select clause to get most of what I needed. I have two more requests though. Is there a way to sum each individual row by hour and a way to change the hour (the HO) from 00 to 24?
Thanks
I was able to take your suggestion and modify the select clause to get most of what I needed. I have two more requests though. Is there a way to sum each individual row by hour and a way to change the hour (the HO) from 00 to 24?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Paul,
Your replace clause worked great. I am trying to sum each row as shown below:
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 01 0 1 2 3 6
08/21/2015 02 4 5 6 6 22
Thanks
Your replace clause worked great. I am trying to sum each row as shown below:
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 01 0 1 2 3 6
08/21/2015 02 4 5 6 6 22
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW
'24' may now be sorted incorrectly
(i.e. that '24' data really does occur before '01' data on a time continuum)
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 01 0 1 2 3 6
...
08/21/2015 23
08/21/2015 24 4 5 6 6 22
really would happen like this:
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 24 4 5 6 6 22
08/21/2015 01 0 1 2 3 6
...
08/21/2015 23
'24' may now be sorted incorrectly
(i.e. that '24' data really does occur before '01' data on a time continuum)
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 01 0 1 2 3 6
...
08/21/2015 23
08/21/2015 24 4 5 6 6 22
really would happen like this:
TIME HE Value1 Value2 Value3 Value4 SumOfRowOfValues
---------- -- ------------- ------------- ------------- ------------- -------------
08/21/2015 24 4 5 6 6 22
08/21/2015 01 0 1 2 3 6
...
08/21/2015 23
ASKER
Everything you guys have suggested has worked for me with very little modification on my part. My last question is how do I get just the data from hour ending 01 up to the current hour. Right now, as you know, I get from the 1st day of the month as shown below for a monthly file. I need to keep this capability but I also need to be able to produce a daily file.
DATE HE
08/01/2015 01 0 87 64 111 3
08/01/2015 02 0 95 64 143 11
08/01/2015 03 0 87 62 146 12
08/01/2015 04 0 88 61 148 12
08/01/2015 05 0 85 60 141 10
08/01/2015 06 0 84 59 140 11
08/01/2015 07 0 81 58 136 11
08/01/2015 08 0 78 59 117 8
08/01/2015 09 0 81 62 96 7
08/01/2015 10 0 81 65 81 1
08/01/2015 11 0 90 69 61 0
08/01/2015 12 0 89 74 41 0
08/01/2015 13 0 97 80 41 0
08/01/2015 14 0 113 84 53 0
08/01/2015 15 0 117 86 45 0
08/01/2015 16 0 126 88 48 0
08/01/2015 17 0 135 90 46 0
08/01/2015 18 0 132 89 41 0
08/01/2015 19 0 133 86 50 0
08/01/2015 20 0 120 83 52 0
08/01/2015 21 0 111 76 44 0
08/01/2015 22 0 100 75 52 0
08/01/2015 23 0 103 74 74 2
08/02/2015 24 0 104 72 86 7
I tried this:
from $DATA_VALUES
where to_char(TIME,'DD') = to_char(Sysdate,'DD')
group by to_char(TIME,'MM/DD/YYYY')
order by TIME...
But as you guessed I got this:
08/25/2015 24 0 124 69 153 22
08/25/2015 01 0 116 65 149 23
08/25/2015 02 0 121 64 156 28
If this were a full daily file I would need this:
08/25/2015 01 0 116 65 149 23
08/25/2015 02 0 121 64 156 28
08/25/2015 03 0 112 88 122 33
.
.
.
08/26/2015 24 0 142 68 129 63
The monthly file handles this correctly.
Thanks
DATE HE
08/01/2015 01 0 87 64 111 3
08/01/2015 02 0 95 64 143 11
08/01/2015 03 0 87 62 146 12
08/01/2015 04 0 88 61 148 12
08/01/2015 05 0 85 60 141 10
08/01/2015 06 0 84 59 140 11
08/01/2015 07 0 81 58 136 11
08/01/2015 08 0 78 59 117 8
08/01/2015 09 0 81 62 96 7
08/01/2015 10 0 81 65 81 1
08/01/2015 11 0 90 69 61 0
08/01/2015 12 0 89 74 41 0
08/01/2015 13 0 97 80 41 0
08/01/2015 14 0 113 84 53 0
08/01/2015 15 0 117 86 45 0
08/01/2015 16 0 126 88 48 0
08/01/2015 17 0 135 90 46 0
08/01/2015 18 0 132 89 41 0
08/01/2015 19 0 133 86 50 0
08/01/2015 20 0 120 83 52 0
08/01/2015 21 0 111 76 44 0
08/01/2015 22 0 100 75 52 0
08/01/2015 23 0 103 74 74 2
08/02/2015 24 0 104 72 86 7
I tried this:
from $DATA_VALUES
where to_char(TIME,'DD') = to_char(Sysdate,'DD')
group by to_char(TIME,'MM/DD/YYYY')
order by TIME...
But as you guessed I got this:
08/25/2015 24 0 124 69 153 22
08/25/2015 01 0 116 65 149 23
08/25/2015 02 0 121 64 156 28
If this were a full daily file I would need this:
08/25/2015 01 0 116 65 149 23
08/25/2015 02 0 121 64 156 28
08/25/2015 03 0 112 88 122 33
.
.
.
08/26/2015 24 0 142 68 129 63
The monthly file handles this correctly.
Thanks
>>hour ending 01 up to the current hour
The query I posted will only produce an hour where there is data for that hour. Even if you wanted a full day and for some reason hour 8 had no data, hour 8 wouldn't be in the result set.
If you want 'X' hours and zero's for missing hours, we need to tweak the query.
Then to restrict it down to the current hour, a simple WHERE clause should work.
The query I posted will only produce an hour where there is data for that hour. Even if you wanted a full day and for some reason hour 8 had no data, hour 8 wouldn't be in the result set.
If you want 'X' hours and zero's for missing hours, we need to tweak the query.
Then to restrict it down to the current hour, a simple WHERE clause should work.
ASKER
slightwv,
You bring up a point I have not thought of which is fillers for missing hours. Could you show me how to write out the data in a matrix with zero's for the missing hours. I talked to a couple of people who will be using this query and that would be preferred if possible.
Thanks
You bring up a point I have not thought of which is fillers for missing hours. Could you show me how to write out the data in a matrix with zero's for the missing hours. I talked to a couple of people who will be using this query and that would be preferred if possible.
Thanks
I had to hard-code the date to pull in a couple of places. If the date to pull is a variable, then the select gets a lot cleaner and you won't have to hard-code anything.
Here is what I came up with.
Note: I didn't add the 00 to 24 trick Paul posted.
Here is what I came up with.
Note: I didn't add the 00 to 24 trick Paul posted.
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',0);
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,
max(nvl(case when Element = '06000000' then value end,0)) element1value,
max(nvl(case when Element = '06000001' then value end,0)) element2value,
max(nvl(case when Element = '06000002' then value end,0)) element3value,
max(nvl(case when Element = '06000003' then value end,0)) element4value,
max(nvl(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
/
I'm not sure I follow you, are you now asking to for this?
when to_char(time,'HH24') hour produces '00' for that to be '01'
...
and when to_char(time,'HH24') hour produces '23' this should be '24'
Then, as well, that '01' to '24' will be displayed for each date that exists in the file?
when to_char(time,'HH24') hour produces '00' for that to be '01'
...
and when to_char(time,'HH24') hour produces '23' this should be '24'
Then, as well, that '01' to '24' will be displayed for each date that exists in the file?
Ah >>"... I need the hours to go from 01 until 24 ..."
then what I proposed earlier was quite wrong - sorry.
get the hour as char
convert to number
add 1
give it leading zero
take only last 2 chars
right('0' || to_number(to_char(time,'HH
{+edit] .... sorry mixing my databases and I have to run ... RIGHT() does not exist in Oracle
substr('0' || to_number(to_char(sysdate,
apologies
then what I proposed earlier was quite wrong - sorry.
get the hour as char
convert to number
add 1
give it leading zero
take only last 2 chars
right('0' || to_number(to_char(time,'HH
{+edit] .... sorry mixing my databases and I have to run ... RIGHT() does not exist in Oracle
substr('0' || to_number(to_char(sysdate,
apologies
ASKER
slightwv/Paul,
Below is a snippet of what I have put together so far with your help and it works great for a monthly file. The code will be called in a BASH script and will run every 15 minutes. What we need now, if possible, is a daily file that contains hour ending (HE) 01 (current day) through 24 (next day) in a “zero filled matrix” layout with data filled in up to the current hour. The monthly file does not need the zero filled matrix code. Slightwv, I am having trouble getting what you have suggested to compile (but I am still working on it) and Paul when I replace the “replace” clause with the substr clause the HE column does not print. Also, thanks for the line by line description. That helps a rookie like me a lot.
FYI - The last day of the current month in the monthly file should contain HE 01 through 24 (even though HE 24 a date stamp for the next month).
Again, thanks for the help.
snippet.txt
Below is a snippet of what I have put together so far with your help and it works great for a monthly file. The code will be called in a BASH script and will run every 15 minutes. What we need now, if possible, is a daily file that contains hour ending (HE) 01 (current day) through 24 (next day) in a “zero filled matrix” layout with data filled in up to the current hour. The monthly file does not need the zero filled matrix code. Slightwv, I am having trouble getting what you have suggested to compile (but I am still working on it) and Paul when I replace the “replace” clause with the substr clause the HE column does not print. Also, thanks for the line by line description. That helps a rookie like me a lot.
FYI - The last day of the current month in the monthly file should contain HE 01 through 24 (even though HE 24 a date stamp for the next month).
Again, thanks for the help.
snippet.txt
>> What we need now, if possible, is a daily file that contains hour ending (HE) 01 (current day) through 24 (next day)
Wouldn't this be the last run of the day? If you can't use that for some reason, I would probably just set up a 'daily' script that runs after midnight and queries the previous days data.
>>but I am still working on it
Ask if you have questions or get to a point where you are stuck.
Wouldn't this be the last run of the day? If you can't use that for some reason, I would probably just set up a 'daily' script that runs after midnight and queries the previous days data.
>>but I am still working on it
Ask if you have questions or get to a point where you are stuck.
ASKER
slightwv,
>> Wouldn't this be the last run of the day?
Yes. What I was going to do next week was write a BASH script to cut out of the monthly file what I need. But since I am trying to learn as much as I can about sqlplus (especially when dealing with time functions) I was hoping to get an experts input. I am assuming that "TIME" in the "group by" clause could be modified to extract what would be needed for a "daily" report. Making a copy of the current code and changing the group by clause would make things a lot cleaner I think.
>>but I am still working on it
Ask if you have questions or get to a point where you are stuck.
I do and I am
But it is time for the weekend, hope you (and Paul) have a most excellent weekend as well...
Thanks for your time
>> Wouldn't this be the last run of the day?
Yes. What I was going to do next week was write a BASH script to cut out of the monthly file what I need. But since I am trying to learn as much as I can about sqlplus (especially when dealing with time functions) I was hoping to get an experts input. I am assuming that "TIME" in the "group by" clause could be modified to extract what would be needed for a "daily" report. Making a copy of the current code and changing the group by clause would make things a lot cleaner I think.
>>but I am still working on it
Ask if you have questions or get to a point where you are stuck.
I do and I am
But it is time for the weekend, hope you (and Paul) have a most excellent weekend as well...
Thanks for your time
>> I am assuming that "TIME" in the "group by" clause could be modified to extract what would be needed for a "daily" report.
Not really the group by. You need to add the date you are wanting in the where clause. Nothing else in the select statement should need to change.
That is where I mentioned variables before. If the date you want to search is in a variable, then you don't need to hard code the date anywhere in the SQL.
Not really the group by. You need to add the date you are wanting in the where clause. Nothing else in the select statement should need to change.
That is where I mentioned variables before. If the date you want to search is in a variable, then you don't need to hard code the date anywhere in the SQL.
ASKER
I think I have everything I need thanks to all of the help. My last question is WRT the snippet below is how do I get the difference between RecRowSum and DelRowSum...i.e TotalRecDel = RecRowSum-DelRowSum?
select TIME, HE,
element1value+element2valu
RecRowSum,
delelement1value+delelemen
DelRowSum
from
(
select to_char(TIME,'MM/DD/YYYY')
max(nvl(case when Element = '06000001' then VALUE end,0))element1value,
max(nvl(case when Element = '06000002' then VALUE end,0))element2value,
max(nvl(case when Element = '06000003' then VALUE end,0))delelement1value,
max(nvl(case when Element = '06000004' then VALUE end,0))delelement2value,
from DATA_BASE
group by to_char(TIME,'MM/DD/YYYY')
order by TIME
);
exit;
%
Thanks
2nd submit of this comment
select TIME, HE,
element1value+element2valu
RecRowSum,
delelement1value+delelemen
DelRowSum
from
(
select to_char(TIME,'MM/DD/YYYY')
max(nvl(case when Element = '06000001' then VALUE end,0))element1value,
max(nvl(case when Element = '06000002' then VALUE end,0))element2value,
max(nvl(case when Element = '06000003' then VALUE end,0))delelement1value,
max(nvl(case when Element = '06000004' then VALUE end,0))delelement2value,
from DATA_BASE
group by to_char(TIME,'MM/DD/YYYY')
order by TIME
);
exit;
%
Thanks
2nd submit of this comment
I'm not sure I understand.
Would it not just be subtracting the values?
select TIME, HE,
element1value+element2valu
RecRowSum,
delelement1value+delelemen
DelRowSum,
(element1value+element2val
from
...
Would it not just be subtracting the values?
select TIME, HE,
element1value+element2valu
RecRowSum,
delelement1value+delelemen
DelRowSum,
(element1value+element2val
from
...
ASKER
That is what I needed. But just for my edification is there a way to use just (RecRowSum-DelRowSum)=Tota
Thanks
Thanks
You cannot use column aliases in the SAME select clause
e.g. this won't work:
select 1 as col1, 2 as col2, col1+col2 as col3 from dual
but you can use column aliases from a prior select clause
e.g. this will work:
select col1, col2, col1+col2 as col3 from (
select 1 as col1, 2 as col2 from dual
)
e.g. this won't work:
select 1 as col1, 2 as col2, col1+col2 as col3 from dual
but you can use column aliases from a prior select clause
e.g. this will work:
select col1, col2, col1+col2 as col3 from (
select 1 as col1, 2 as col2 from dual
)
ASKER
Thanks for the information.
ASKER
I've requested that this question be deleted for the following reason:
I thought I closed this question a while back because I awarded points for it. Also, I submitted another question yesterday with three attachments but I do not see it in my list.
I thought I closed this question a while back because I awarded points for it. Also, I submitted another question yesterday with three attachments but I do not see it in my list.
>>I thought I closed this question a while back because I awarded points for it.
Can you not accept it again instead of deleting it?
I also cannot see the new question. If you are sure you submitted it, I would contact EE support using the 'Contact Us' link below and have them look into it.
Can you not accept it again instead of deleting it?
I also cannot see the new question. If you are sure you submitted it, I would contact EE support using the 'Contact Us' link below and have them look into it.
The follow up question was answered too, and I was thanked for providing that information.
ASKER
This question has been answered and points assigned. Please delete this.
...
where Element = '06000000'
and Element = '06000001'
One column cannot have two different values.
Can you provide a small test case with table, sample data and expected results?
Maybe just 3 columns worth. Once we post code for 3 columns, you can do the other 37 of them...