Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2006-11-30
6
Medium Priority
?
2,297 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
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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 750 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:Sujith
Sujith earned 750 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
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…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

595 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