Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-30
6
Medium Priority
?
2,287 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 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:sujith80
sujith80 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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

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…
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, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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