hi all,

I can not recreate my issue on CAR. Sorry for that.
I have the following situation. And I DO know workarounds. I was just wondering what might cause this.

I have a field "YEAR" which is I6
I have a field "TURNOVER" which is P18.4

When I use a DEFINE
DEFINE FILE mymaster
TURNOVER_2013/P18.4 = IF YEAR EQ 2013 THEN TURNOVER ELSE 0;
TURNOVER_2014/P18.4 = IF YEAR EQ 2014 THEN TURNOVER ELSE 0;
END

This gives an error.

quote:

(FOC1400) SQLCODE IS 2616 (HEX: 00000A38)
: Numeric overflow occurred during computation.

After a LOT of trying I found out that the following code works fine:
DEFINE FILE mymaster
TURNOVER_2013/P18.4 = IF ( TURNOVER NE 0 ) AND ( YEAR EQ 2013 ) THEN TURNOVER ELSE 0;
TURNOVER_2014/P18.4 = IF ( TURNOVER NE 0 ) AND ( YEAR EQ 2014 ) THEN TURNOVER ELSE 0;
END

It seam like some internal variable always uses the format of whatever the format of the first field in the DEFINE is.
In the first DEFINE it's the field YEAR. An I6 which by far can not hold the TURNOVER.
In the second DEFINE it's the field TURNOVER. An P18.4 which is okay.


ANY CLUES?

This message has been edited. Last edited by: <Kathryn Henning>,

Try this, convert the year to an I4 first:

DEFINE FILE mymaster
I4YEAR/I4=YEAR;
TURNOVER_2013/P18.4 = IF I4YEAR EQ 2013 THEN TURNOVER ELSE 0;
TURNOVER_2014/P18.4 = IF I4YEAR EQ 2014 THEN TURNOVER ELSE 0;
END

Just an initail thought...

Doug,

I don't think that'll work. It's a I6. I4 wouldn't make a big difference.

But I've tried it anyway.
Still same error.


BUT!
I made a typo.

I have an other businessview with also a TURNOVER in it.
( Not same as the first, it's a difference part of the company ;-) )

In that businessview turnover is define as P13.2
...and I mixed them up.

I did this:
DEFINE FILE BV_blahblah
TURNOVER/P13.4 = .... .... ( as above )
END

In the businessview where TURNOVER is define as P18.4
AND IT WORKS!

so...
the field is defined as P18.4
DEFINE FILE BV_blahblah
THIS_CAUSES_AN_OVERFLOW/P18.4 = IF YEAR EQ 2014 THEN TURNOVER ELSE 0;
THIS_WORKS_FINE/P13.4 = IF YEAR EQ 2014 THEN TURNOVER ELSE 0;
END


...so

In a way, THANKS Doug! ;-)



Still wonder why this is?



_____UPDATE_

Aarrgh...
I got rid of the overflow error...
but SHOCK AND HORROR. It got worse!

check this:

DEFINE FILE BV_blahblah
   TURNOVER_AS_P13_4/P13.4 = IF YEAR EQ 2014 THEN TURNOVER ELSE 0;
   TURNOVER_AS_P13_2/P13.2 = IF YEAR EQ 2014 THEN TURNOVER ELSE 0;
END

TABLE FILE BV_blahblah
   SUM TURNOVER_AS_P13_4
       TURNOVER_AS_P13_2
       TURNOVER
   BY  YEAR
ON TABLE PCHOLD FORMAT HTML
END

This is the output:

YEAR   TURNOVER_AS_P13_4   TURNOVER_AS_P13_2    TURNOVER
2014   3017531,0845        301753108,45         3017531,0845    

Instead of converting it shamelessly moveS the decimal separator two positions to the right?!?!?
( I'm in the Netherlands we use , as decimal separator, don't be confused... )

This only happens with the "IF THEN ELSE". Just using

   TURNOVER_AS_P13_2/P13.2 = TURNOVER;

Works.

This message has been edited. Last edited by: Dave,

We've had issues using the packed format that we couldn't explain too; are you sure you need packed?

Does it work correctly if you use D13.2 instead of P13.2?

Of course there are differences between those formats. I suspect that using decimal instead of packed will be a general improvement though.

Just wondering, if you table and hold the data to a hold file, then did your DEFINE, does it work.

I assume you are reporting of a relations DB, which one is it ?

Waz,

your question is valid.

If I "table" ( is that a real verb ? ) the data to a HOLD and DEFINE that, it works.

But I don't want to do that because it's to much data.

We use ( and proud of it ) teradata.

I guess that begs the question what SQL is being generated ?

Can you move the DEFINES to a COMPUTE ?

Also would it be easier to do this step in SQL passthrough ?

I would suggest logging this with Techsupport.

Stock code not working is not good.

Try this

DEFINE FILE ...
   TURNOVER_AS_P13_4/P13.4 = IF YEAR EQ 2014 THEN (0+TURNOVER) ELSE 0;
   TURNOVER_AS_P13_2/P13.2 = IF YEAR EQ 2014 THEN (0+TURNOVER) ELSE 0;
END

or maybe this

DEFINE FILE ...
   TURNOVER_AS_P13_4/P13.4 = IF YEAR EQ 2014 THEN (0.0000+TURNOVER) ELSE 0;
   TURNOVER_AS_P13_2/P13.2 = IF YEAR EQ 2014 THEN (0.00  +TURNOVER) ELSE 0;
END

@waz

this is SQL generated when I add 4 defines with different number of decimal positions:
p16.2 / p16.4 / p16.6 / p16.8

SELECT
    T19."YEAR",
    SUM(T1."TURNOVER")(DECIMAL(31,04)),
    SUM((CASE (T19."YEAR")  WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(DECIMAL(15,02)),
    SUM((CASE (T19."YEAR")  WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(DECIMAL(15,04)),
    SUM((CASE (T19."YEAR")  WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(DECIMAL(15,06)),
    SUM((CASE (T19."YEAR")  WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(DECIMAL(15,08))
 FROM
...

...and then I saw the original TURNOVER begin a DECIMAL(31,04).

So I also added a D31.4

SQL is
SUM((CASE (T19."DAT_JAAR_COM_NR") WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(FLOAT),

and this returns 0 !

______________________________________

And now, from down-under via The Netherlands to NYC... J.Gross' solution work PERFECTLY !

in all situations!

0+ is enough ( no need to do 0.000+ )

THANKS!



( but it's still a work-around, I'm very worried. No way we want to add this to every DEFINE in all fexi. I still feel it's a BUG.... ...or we have some weird combination of database-format ACTUAL and USAGE and DEFINES? )



---ADDED a bit later:----

I've check the difference in SQL code.

both DEFINEd as D13.4 :

SUM(T1."TURNOVER")(DECIMAL(31,04)),
SUM((CASE (T19."YEAR") WHEN 2013 THEN T1."TURNOVER" ELSE 0 END))(DECIMAL(15,02)),
SUM((CASE (T19."YEAR") WHEN 2013 THEN (0 + T1."TURNOVER") ELSE 0 END))(FLOAT)

First is plain TURNOVER is a DECIMAL ( returns correct data )
Second is a DECIMAL ( -> returns 0 )
Third is a FLOAT ( -> returns correct data )



i'm lost

This message has been edited. Last edited by: Dave,