Solved

ORACLE SQL in OMINIPORTLET -  COMPUTE SUM of COLUMN -500 points!

Posted on 2006-11-30
6
2,271 Views
Last Modified: 2013-12-01
I am trying to create a Omniportlet report using an SQL statement.  I need to have a row at the bottom of the report to compute the sum of a few columns.  I can't seem to get the SQL COMPUTE function to work.  To make it clear, visually, this is the way I want the report to do...

Column1     Column2     Column3
    MF              4                52
    HJ               6                25
    OL               1                60
----------------------------------------
    Totals:       11               137

Here is my SQL statement I am trying to run....

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF Age Height ON REPORT
SELECT Initials, Age, Height
FROM mytable

I get the error: Invalid SQL Statement

I don't know why this isn't working.  Please help! Thanks in advance.
0
Comment
Question by:MFredin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18055379
compute sum of age , height on report

This will work.

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18055393
OOPS..even that works..

break on report
compute sum of age height on report
select a, age, height from hh;


See the below it works for me

         A        AGE     HEIGHT
---------- ---------- ----------
         1         10        100
         2         15        300
         3         18        200
        -1         10        100
        -2         20        100
           ---------- ----------
sum                73        800

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18055409
if you want total instead of sum then

compute sum label total of age height on report

SQL> compute sum label total of age height on report
SQL> l
  1* select * from hh
SQL> /

         A        AGE     HEIGHT
---------- ---------- ----------
         1         10        100
         2         15        300
         3         18        200
        -1         10        100
        -2         20        100
           ---------- ----------
total              73        800

5 rows selected.

Thanks

0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 

Author Comment

by:MFredin
ID: 18055480
I've tried that and I keep getting "Error in executing Query : [ORA-00900: invalid SQL statement ]".

My query runs fine until I put the "compute sum label total of age height on report" at the beginning.

Does the "compute" work with SQL or is it just for SQL*Plus? I can get it to work with SQL*Plus but not with the Oracle Omniportlet with uses straight SQL.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 250 total points
ID: 18055729
it is a sql*plus and not a sql command.

That is the reason we dont end the command in sqlplus with a ; at the end

I have not worked on oracle omniportlet ..so i cant help you.

Thanks
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 18100138
Since you cannot use sqlplus commands, and you want to get it done with a single sql - I can suggest you a workaround:

Edit your query like this one:

select rpad(col1,20)||rpad(col2,20)||rpad(col3,20) data from c
union
select rpad('Total:',20)||rpad(sum(col2),20)||rpad(sum(col3),20) from c;
0

Featured Post

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question