Hi, I am trying to create a calculated field using a case statement. It will simply say, if the facility type in subquery 1 is NULL, then use the facility type from subquery 2. Otherwise use from subquery 1. I am including a screenshot to provide some clarity. Not sure why it does not like subquery 2. Is it because of the “-” in the subquery name. The analyze shows 2 errors: invalid column name “BKLG” and invalid column name “Calculated_facilityType”. These columns are listed under available.
@tsantaniello it looks like it is assuming the then is subtracting two fields. You can wrap the BKLG-WMTXQuery in square brackets [] to get around that.
I would not use Case for this I would either use ISNULL( FIELD1,FIELD2) or Coalesce(field1, field2)
Like This:
case
when (WMTXQuery.Calculated_SubQueryName) is NULL
then [BKLG-WMTXQuery.Calculated_FacilityType]
else WMTXQuery.Calculated_SubQueryName
end
I get the following error:
I will try COALESCE() as well.
before the dot to tell sql this is one piece of information
case
when (WMTXQuery.Calculated_SubQueryName) is NULL
then [BKLG-WMTXQuery].Calculated_FacilityType
else WMTXQuery.Calculated_SubQueryName
end
Thanks so much!
1 Like