Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] NULL being returned as -9998998
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] NULL being returned as -9998998
 Login/Join
 
Member
posted
This one is a bit strange...

I'm trying to implement a DEFINED field in my MFD with logic like:

  FIELD=MRF_ACTL_P, ALIAS=MRF_ACTL_P, USAGE=P7.2, ACTUAL=P8BC, MISSING=ON, TITLE='MRF Diversion %', DESCRIPTION='MRF Diversion %', $
  FIELD=SRVC_MTRL_C, ALIAS=SRVC_MTRL_C, USAGE=A20, ACTUAL=A20, $
  FIELD=HAUL_INVC_SRVC_Q, ALIAS=HAUL_INVC_SRVC_Q, USAGE=P12.2BC, ACTUAL=P6, TITLE='Trash Hauler Invoice Service Quantity', DESCRIPTION='Trash Hauler Invoice Service Quantity', $
  DEFINE TON_LANDFILL_Q/F7.2BC MISSING ON NEEDS SOME = IF MRF_ACTL_P IS MISSING THEN MISSING ELSE IF SRVC_MTRL_C EQ 'T' AND MRF_ACTL_P EQ 0 THEN HAUL_INVC_SRVC_Q ELSE MISSING; TITLE='Tons to Landfill',  DESCRIPTION='Tons to Landfill', $


I'd expect the SQL translator to do something like
SUM(CASE 
WHEN MFR_ACTL_P IS NULL THEN NULL
WHEN SRVC_MTRL_C = 'T' AND MFR_ACTL_P = 0 THEN HAUL_INVC_SRVC_Q
ELSE NULL
END)


Instead the SQL translator is giving this SQL:
SUM(CASE
WHEN T1."MRF_ACTL_P" IS NULL THEN -9998998 
WHEN ((T1."SRVC_MTRL_C" = 'T') AND (T1."MRF_ACTL_P" = 0)) THEN T1."HAUL_INVC_SRVC_Q" 
ELSE -9998998 
END))


The MFD is running against a SQL Server 2005 database.

From what I've read, -9998998 is the internal FOCUS representation of a NULL; but for some reason, it's treating this number as an integer and SUM'ing it. Is there a SET COMMAND someplace that I may have missed? Has anyone else run across this?

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


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
 
Posts: 20 | Location: Minneapolis | Registered: July 20, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
IF MRF_ACTL_P IS MISSING THEN MISSING



Try changing that to THEN 0


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Yep, that works.

  
SUM((CASE 
WHEN T1."MRF_ACTL_P" IS NULL THEN 0 
WHEN ((T1."SRVC_MTRL_C" = 'T') AND (T1."MRF_ACTL_P" = 0)) THEN T1."HAUL_INVC_SRVC_Q" 
ELSE 0


I really need it to return NULL though, since I want this metric to be able to be used in aggregation functions like MIN/MAX/AVE. There is something going wrong with the SQL translator between the FOCUS MISSING keyword and SQL Server NULL. Any other ideas?


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
 
Posts: 20 | Location: Minneapolis | Registered: July 20, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
You may wish to revise the NEEDS SOME DATA clause, as per the documentation:

quote:

NEEDS - Is optional. It helps to clarify the meaning of the command.

SOME - Indicates that if at least one field in the expression has a value, the temporary field has a value (the missing values of the field are evaluated as 0 or blank in the calculation). If all of the fields in the expression are missing values, the temporary field is missing its value. SOME is the default value.

ALL - Indicates that if all the fields in the expression have values, the temporary field has a value. If at least one field in the expression has a missing value, the temporary field also has a missing value.

DATA - Is optional. It helps to clarify the meaning of the command.


Maybe NEEDS ALL DATA will solve the problem - this is just a suggestion.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Francis makes an excellent suggestion; "if" that does not give you what you are looking for, try using Packed instead of Float and remove the other MISSING statements:

quote:
DEFINE TON_LANDFILL_Q/F7.2BC MISSING ON NEEDS SOME = IF MRF_ACTL_P IS MISSING THEN MISSING ELSE IF SRVC_MTRL_C EQ 'T' AND MRF_ACTL_P EQ 0 THEN HAUL_INVC_SRVC_Q ELSE MISSING; TITLE='Tons to Landfill', DESCRIPTION='Tons to Landfill', $


try:

  
 DEFINE TON_LANDFILL_Q/P7.2BC MISSING ON = IF SRVC_MTRL_C EQ 'T' AND MRF_ACTL_P EQ 0 THEN HAUL_INVC_SRVC_Q
 ELSE MISSING; 
TITLE='Tons to Landfill',  DESCRIPTION='Tons to Landfill', $


hth


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Regardless of SOME or ALL, it seems to me that the generated SQL sould not contain any reference to the magic value -9998998.

The generated SQL should direct the RDBMS to return NULL (in whatever form is conventional for that RDBMS) whenever the Focus definition calls for Missing -- just as the RDBMS would do with a Null instance of a real nullable column.

EDA should deal with transforming that to EDA's conventional representation of Null/Missing values later in the process.

As it stands, you need to do the SUM on the Focus side (i.e., use PRINT, HOLD, then SUM against the HOLD file)

My recollection is that NEEDS SOME / ALL DATA controls evaluation of the Define on an individual incoming data row, but not how a data-reduction operation should treat a set of column-values some of which are null. Focus ignores the null instances; IIRC, SQL's arithmetic rules, as SUM() adds together the successive values, will yield Null as soon as one Null instance in encountered.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Also you are putting a P12.2 field into a F7.2 field, you could blow the size.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6275 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
You may wish to revise the NEEDS SOME DATA clause, as per the documentation:

Maybe NEEDS ALL DATA will solve the problem - this is just a suggestion.


Thanks for the clarification. I had never found a good explanation of the NEEDS clause, this helps me alot. Unfortunately, it didn't solve the issue, the generated SQL still contains the -9998998 instead of NULL, whether I use NEEDS SOME DATA or NEEDS ALL DATA.

quote:

Francis makes an excellent suggestion; "if" that does not give you what you are looking for, try using Packed instead of Float and remove the other MISSING statements:


This is a great suggestion too... and again, it proved unsuccessful. I copied/pasted your exact DEFINE syntax to the MFD, but again the generated SQL shows -9998998 where I would have expected NULL.

quote:

Also you are putting a P12.2 field into a F7.2 field, you could blow the size.


Nice catch. I've fixed it now.


Thanks everybody for the great suggestions, I appreciate all of the good ideas. I'm still pretty baffled by this; am I trying to do something too radical here? It seems like a fairly simple/common concept to want to implement at the metadata layer- I'd be shocked to find that this is some yet-to-be-discovered product bug in this late of a product release. That's what has me convinced that I must have something syntatically incorrect, or a system configuration issue.


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
 
Posts: 20 | Location: Minneapolis | Registered: July 20, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Time to open a case with Tech Support - please keep up informed, I'm sure more than one of us are intrigued...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
I'm sure more than one of us are intrigued

I certainly am Confused! It's a really strange behaviour.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
I've opened a case. I'll let you all know what I find.

Thanks for all of the great suggestions!


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
 
Posts: 20 | Location: Minneapolis | Registered: July 20, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] NULL being returned as -9998998

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.