Hi All - When I run the SQL from Oracle SQL developer I see the field value as 0


When I use the same SQL passthrough and print from WebFOCUS I get the values as .00.

Can you please help to dispay as 0 from webfocus ?

Note : datatype of field is NUMBER(12,2)

WebFOCUS 7703,Orcale 11.04

This message has been edited. Last edited by: FP Mod Chuck,

You have several ways to fix that.

1- Change master file USAGE field definition. Actually you probably have something such as USAGE=D10.2 defined for your field. Change it for USAGE=D10
But this change will affect all places where this field is used : the decimals will now be hidden and the field will be rounded.

If you only want a specific report display to be affected
2- From a DEFINE
DEFINE FILE xyz
nFld /D10 = myFieldToChangeFmt;
END
TABLE FILE xyz
PRINT (or SUM) nFld
...
END

3- From a dynamic reformat
TABLE FILE xyz
PRINT (or SUM) COMPUTE nFld /D10 = myFieldToChangeFmt;
...
END

Hello Martin,

This was my first post in this forum and thanks for the quick response.

Actually I forgot to mention, I should have other values not modified for example other values are like .10 , .30 and 2.40 these should be displayed as it is.

Simplest method is to use dynamic reformatting.

Something like this -

TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END

The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T

quote:

Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -

TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END

The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T

That's FANTASTIC!! I had no idea that you can do that.

To take that a little further, I found that you can use the MOD function to evaluate the number and do this on any value that is a whole number:

  

TABLE FILE WF_RETAIL_LITE
PRINT 
    REVENUE_US
    COMPUTE MOD/D12.2 = MOD(REVENUE_US, 1);
    COMPUTE FMT/A8 = IF MOD EQ 0 THEN 'D12M' ELSE 'D12.2M';
    REVENUE_US/FMT
BY  PRODUCT_CATEGORY
BY  TIME_DATE
WHERE PRODUCT_CATEGORY EQ 'Accessories'
WHERE TIME_DATE EQ '20130101'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET CENT-ZERO ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
ENDSTYLE
END

A little off topic, but if you prefer a leading zero on decimals that are < 1 and > -1 you can use the CENT-ZERO set command like I have in the code above.

quote:

Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -

TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END

The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T

Thank you so much Tony!
It worked without any issue. Thanks again!

quote:

Originally posted by Hallway:

quote:

Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -

TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END

The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T

That's FANTASTIC!! I had no idea that you can do that.

To take that a little further, I found that you can use the MOD function to evaluate the number and do this on any value that is a whole number:

  

TABLE FILE WF_RETAIL_LITE
PRINT 
    REVENUE_US
    COMPUTE MOD/D12.2 = MOD(REVENUE_US, 1);
    COMPUTE FMT/A8 = IF MOD EQ 0 THEN 'D12M' ELSE 'D12.2M';
    REVENUE_US/FMT
BY  PRODUCT_CATEGORY
BY  TIME_DATE
WHERE PRODUCT_CATEGORY EQ 'Accessories'
WHERE TIME_DATE EQ '20130101'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET CENT-ZERO ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
ENDSTYLE
END

A little off topic, but if you prefer a leading zero on decimals that are < 1 and > -1 you can use the CENT-ZERO set command like I have in the code above.

Thanks for the response,When i use MOD function i receive below error

ADHOCRQ FOCEXEC *
(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: MOD
(FOC009) INCOMPLETE REQUEST STATEMENT

quote:

LOAD MODULE NOT FOUND: MOD

That's because you are on WF 7.7. Check out DMOD, FMOD & IMOD functions.

Glad the dynamic formatting worked for you.

T