Solved

sql+ formatting

Posted on 2002-07-17
13
551 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
[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
  • 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
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!

 
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

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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

734 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