Solved

sql+ formatting

Posted on 2002-07-17
13
544 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 7

Expert Comment

by:Daniel Stanley
Comment Utility
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
Comment Utility
dds110, you can provide a few sample rows of the data and how they should appear
0
 
LVL 2

Expert Comment

by:rajeXsh
Comment Utility
dds110, you can provide a few sample rows of the data and how they should appear
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 8

Author Comment

by:dds110
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for being so patient.
0

Featured Post

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

728 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

12 Experts available now in Live!

Get 1:1 Help Now