Solved

sql+ formatting

Posted on 2002-07-17
13
550 Views
Last Modified: 2007-12-19
Hi.  Here are my column formats:

[code]
COLUMN AVG(SCORE) HEADING 'AVERAGE SCORE' FORMAT 999.99
COLUMN MAX(SCORE) HEADING 'HIGHEST SCORE' FORMAT 999
COLUMN MIN(SCORE) HEADING 'LOWEST SCORE' FORMAT 999
[/code]

What I'd like to do is use 999.99 on all three columns but supress the decimals if they happen to be 0.  Is there any way to do this?

Thanks
0
Comment
Question by:dds110
  • 6
  • 5
  • 2
13 Comments
 
LVL 2

Expert Comment

by:rajeXsh
ID: 7162961
You can use

[code]
COLUMN AVG(SCORE) HEADING 'AVERAGE SCORE' FORMAT B999.99
COLUMN MAX(SCORE) HEADING 'HIGHEST SCORE' FORMAT B999
COLUMN MIN(SCORE) HEADING 'LOWEST SCORE' FORMAT B999
[/code]

The 'B' will display the 0 value as blank


0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7162971
the ABS() function will do that.

$anoka@scatcat~>column c1 format 99.99
$anoka@scatcat~>column c2 format 99.99
$anoka@scatcat~>select c1, c2 from test;
    C1     C2
------ ------
 13.20  31.00
   .00  12.11
2 rows selected.

$anoka@scatcat~>select
 2 abs(c1),
 3 abs(c2)
 4 from test;
             ABS(C1)              ABS(C2)
-------------------- --------------------
                13.2                   31
                   0                12.11
2 rows selected.


good luck,
daniels@asix.com
0
 
LVL 8

Author Comment

by:dds110
ID: 7163326
Sorry guys, neither one worked.  What information can I provide you with that will be of more help?

I'm using Oracle 8i for windows and SQL+.

Any other ideas?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7163404
if that did not work then i'm not clear on what you're trying to do.

thx,
daniels@asix.com
0
 
LVL 2

Expert Comment

by:rajeXsh
ID: 7164606
dds110, you can provide a few sample rows of the data and how they should appear
0
 
LVL 2

Expert Comment

by:rajeXsh
ID: 7164607
dds110, you can provide a few sample rows of the data and how they should appear
0
 
LVL 8

Author Comment

by:dds110
ID: 7174121
Ok.  I have three queries to return the results.  The first result set is:

Average Score
-------------
        88.99

Second Result Set:

Highest Score
-------------
          100

Third Result Set:

Lowest Score
------------
          75

I think it's ridiculous that I cannot use one number format and hide the trailing zeroes and decimal points.

By the way, my apologies for getting back to the q so late.

Thanks

0
 
LVL 2

Expert Comment

by:rajeXsh
ID: 7174383
Try

SELECT TRUNC(AVG(SCORE) , 2) FROM ......
SELECT TRUNC(MAX(SCORE) , 2) FROM ......
SELECT TRUNC(MIN(SCORE) , 2) FROM ......


0
 
LVL 8

Author Comment

by:dds110
ID: 7188092
I am not ignoring this question.  I will try the suggestion from rajeXsh tonight since my employer is too cheap to buy oracle.

Thanks

DDS
0
 
LVL 8

Author Comment

by:dds110
ID: 7192324
Sorry rajeXsh, it didn't work.  I'm posting my entire script to see if anyone else has an idea.

-----------Begin Script-----------
CLEAR SCR;
SET VERIFY OFF;

--TITLE SECTION-----------------------------
TTITLE 'SOFTWARE EXPERTS' SKIP 1 -
'CONSULTANT EVALUATION REPORT' SKIP 1 -
'============================' SKIP 2

--COLUMN FORMATING SECTION------------------
COLUMN P_ID HEADING 'PROJECT ID' FORMAT 999
COLUMN TO_CHAR(E_DATE,'DD/MM/YYYY') HEADING 'EVALUATION DATE' FORMAT A15
COLUMN SCORE HEADING 'SCORE' FORMAT 999
COLUMN TRUNC(AVG(SCORE),2) HEADING 'AVERAGE SCORE' FORMAT 999.99
COLUMN TRUNC(MAX(SCORE),2) HEADING 'HIGHEST SCORE' FORMAT 999.99
COLUMN TRUNC(MIN(SCORE),2) HEADING 'LOWEST SCORE' FORMAT 999.99

--START QUERIES----------------------------
ACCEPT EID PROMPT "Please enter the ID of the consultant being evaluated: "

SELECT P_ID, TO_CHAR(E_DATE,'DD/MM/YYYY'), SCORE
    FROM EVALUATION
    WHERE EVALUATEE_ID = &EID;

--Turn titles off after 1st query to supress titles on every query
TTITLE OFF;

SELECT TRUNC(AVG(SCORE),2) FROM EVALUATION WHERE EVALUATEE_ID = &EID;
SELECT TRUNC(MAX(SCORE),2) FROM EVALUATION WHERE EVALUATEE_ID = &EID;
SELECT TRUNC(MIN(SCORE),2) FROM EVALUATION WHERE EVALUATEE_ID = &EID;

--Turn verification back on
SET VERIFY ON;
-----------End Script----------------

Thanks
0
 
LVL 2

Accepted Solution

by:
rajeXsh earned 50 total points
ID: 7193289
dds110,

Remove the formating statements from the script

Remove these

COLUMN TRUNC(AVG(SCORE),2) HEADING 'AVERAGE SCORE' FORMAT 999.99
COLUMN TRUNC(MAX(SCORE),2) HEADING 'HIGHEST SCORE' FORMAT 999.99
COLUMN TRUNC(MIN(SCORE),2) HEADING 'LOWEST SCORE' FORMAT 999.99



Replace with

COLUMN TRUNC(AVG(SCORE),2) HEADING 'AVERAGE SCORE'
COLUMN TRUNC(MAX(SCORE),2) HEADING 'HIGHEST SCORE'
COLUMN TRUNC(MIN(SCORE),2) HEADING 'LOWEST SCORE'


0
 
LVL 8

Author Comment

by:dds110
ID: 7215277
Sorry rajeXsh,

My assignment requires I have the FORMAT keyword.  I also apologize for getting back to this q so late in the game.

I will try your suggestion though, and if it works, you'll get the points.  Look for them later tonight.

DDS
0
 
LVL 8

Author Comment

by:dds110
ID: 7215851
Thanks for being so patient.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

830 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