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,

quote:

IF MRF_ACTL_P IS MISSING THEN MISSING

Try changing that to THEN 0

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?

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 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

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.

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

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.

Time to open a case with Tech Support - please keep up informed, I'm sure more than one of us are intrigued...

quote:

I'm sure more than one of us are intrigued

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

I've opened a case. I'll let you all know what I find.

Thanks for all of the great suggestions!