I have a scalar function which was getting used inside several queries. Scalar function was called inside WHERE and INSIDE CASE statement. This created huge IO and CPU performance issue due to the fact that function was getting executed for each row (Function calls and joins few views). I have now changed Scalar function to Inline table function. This works perfectly but system has a limitation that it cannot have 2 select statement inside same query.
CASE WHEN (QUALIF_HIS_INFO.QUALHIS_STATUS = 'Completed' AND QUALIF_HIS_INFO.DATEOFEXPIRATION IS NOT NULL) THEN 'Completed' WHEN STATUS_VIEW.REQUIREMENT_STATUS IS NULL THEN 'Unassigned' WHEN STATUS_VIEW.REQUIREMENT_STATUS = 'Pending' AND (TRAINING_REQUIREMENT.OBSOLETE = 1 OR (SELECT 1 from TRAINING.ISREQUIREMENTASSIGNED_2(USER_SETTINGS_1.USER_ID, QUALIF_HIS_INFO.QUALHIS_REQUIREMENT)) IS NULL) THEN 'Unassigned' ELSE STATUS_VIEW.REQUIREMENT_STATUS END QUALIFICAT_HIST_I_2_QUALH_STAT,
Is there anyway I can use CROSS APPLY or anything else which will eliminate use of SELECT statement here?
Any help will be much appreciated as I seem to be stuck here from quite a while now.
Function accepts 2 parameters.