Solved

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

Posted on 2006-11-30
6
2,280 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

Configure Web Service (server application) I. Configure security for Web Services methods First, we need to protect Session bean which implements the service: 1. Open EJB deployment descriptor (ejb-jar.xml) in the EJB project that contains you…
This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

636 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