?
Solved

sql+ formatting

Posted on 2002-07-17
13
Medium Priority
?
556 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 200 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

800 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