• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

sql+ formatting

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
dds110
Asked:
dds110
  • 6
  • 5
  • 2
1 Solution
 
rajeXshCommented:
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
 
Daniel StanleyCommented:
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
 
dds110Author Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

thx,
daniels@asix.com
0
 
rajeXshCommented:
dds110, you can provide a few sample rows of the data and how they should appear
0
 
rajeXshCommented:
dds110, you can provide a few sample rows of the data and how they should appear
0
 
dds110Author Commented:
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
 
rajeXshCommented:
Try

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


0
 
dds110Author Commented:
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
 
dds110Author Commented:
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
 
rajeXshCommented:
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
 
dds110Author Commented:
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
 
dds110Author Commented:
Thanks for being so patient.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now