0

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.

  • Please explain this "This works perfectly but system has a limitation that it cannot have 2 select statement inside same query", because it's not obvious from this piece of posted code here why this cannot be done. – Luuk Feb 11 at 11:03
  • Thanks for your comment @Luuk. Actually it works fine till we use any filter from front-end. As soon we use any filter, system fails to modified by SQL builder and switches to JAVA search. That's why we avoid using any inline queries or with clause inside system. – AJ_007 Feb 11 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.