Solved

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

Posted on 2006-11-30
6
2,252 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now