Good Day,
I’m getting null value when I tried to multiply 4 variables.
Please see sample troubleshooting and highlighted below.
TEST_1 * TEST_2 = TEST_12 (has value)
TEST_1TEST_2TEST_3 = TEST_123 (has value)
TEST_1TEST_2TEST_3*TEST_4 = TEST_1234 (NULL value)
I even multiplied in reverse but still I got NULL value when multiplying 4 variables, TEST_4321.
Is this a known issue? Is there a workaround?
Thank you!!!
BR/// Anthony
You seem to have a NULL. You may be able to set the NULLs to a 1 and then it won’t evaluate to a NULL. Don’t use NVL (or similar - don’t know which DB you are using) as you’ll get zero instead of NULL.
Might be helpful if you let us know the database and the formatting of each test column.
Also, does it work if you use less complex numbers? IE. Use something like 2.2 in each test column.
Try wrapping each column in an NVL() function that will return a 1 if it is null:
NVL(TEST_1, 1) * NVL(TEST_2, 1) * NVL(TEST_3, 1) * NVL(TEST_4, 1)
What database are you connecting to, Oracle, Sql Server, Sybase, etc.? Test_3 * IsNull(Test_4, 1) (if you are using Sql Server) should fix it. Or use InfoMan’s suggestion for Oracle.
I am using SQL Anywhere (Toad Data Point 4.1).
Here is the format.
( SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAtt) - SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAttReatt) ) / SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabSucc) AS TEST_2
AVG(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.DCVECTOR_INDEX=5 THEN(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedQci)/(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccAddedQci)END) AS TEST_4
Below is the one that is having NULL value.
( ( ( ( AVG(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.DCVECTOR_INDEX=5 THEN(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedQci)/(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccAddedQci)END) * SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmS1SigConnEstabAtt) ) / SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmS1SigConnEstabSucc) ) * ( SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAtt) - SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAttReatt) ) ) / SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabSucc) ) * AVG(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.DCVECTOR_INDEX=1 THEN(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedQci-DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedHoOngoingQci)END) AS TEST_4321
It is still giving me NULL value.
In the highlighted rows, TEST 1 up to 4 have values but it is still giving me NULL (TEST_1234 and TEST_4321).
But it is giving me values for TEST_12 and TEST_123.
I think you may have found a bug. Try this to see what happens (Test1 * Test2) * (Test3 * Test4). Also, because your data has such a high decimal precision you could be running into some kind of numeric overflow problem, but Toads error message is misleading you (I have had a Toad error message lead me astray before). Try rounding the values to a shorter precision (round(Test1, 5) * round(test2, 5), etc.)
That isn’t the format of the columns. Also, the query that you show that gives NULL is far more complex than what was initially sent.Please do a test with simple columns with simple data. My guess is that you may be creating values that are too large for the DBs formating. IE. The numbers after the decimal point might be causing an issue.
This doesn’t appear to be a TDP issue. People are very helpful, but please try the suggested items. Can we get the level of SQL knowledge you have?
I tried the ff. but it gives an error. "… The result database function ‘*’ exceeds the maximum numeric precision of 255…"
(TEST_1TEST_2)(TEST_3*TEST_4)
ROUND(TEST_1)… * ROUND(TEST_2)… * ROUND(TEST_3)… * ROUND(TEST_4)…
(ROUND(TEST_1)… * ROUND(TEST_2)… )* (ROUND(TEST_3)… * ROUND(TEST_4)…)
But I got it working using CAST. I am not sure why but I have other formulas with this kind of structure that does not use CAST.
(CAST((AVG(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.DCVECTOR_INDEX=1 THEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedQci END)) AS FLOAT)(CAST(((SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAtt)-SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabAttReatt))/SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmRrcConnEstabSucc)) AS FLOAT)))((CAST((SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmS1SigConnEstabAtt)/SUM(DC_E_ERBS_EUTRANCELLFDD_DAY.pmS1SigConnEstabSucc)) AS FLOAT))*(CAST((AVG(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_DAY.DCVECTOR_INDEX=5 THEN(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabAttAddedQci)/(DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccInitQci+DC_E_ERBS_EUTRANCELLFDD_V_DAY.pmErabEstabSuccAddedQci)END)) AS FLOAT))) AS TEST_1234
