DSXchange

Posted: **Fri Sep 21, 2018 6:07 pm**

Hi All,

I am finding difficulty while converting the below case statement into the IF else logic to use it into a constaraint while calculation the below scenario..especially with huge amount of data. I tried implemented at source query level, it work for minimal amount of data.

Code: Select all

CASE	WHEN a.Id=2   THEN 
				COUNT(DISTINCT	CASE
						WHEN b.code IN ('A','B','C','D') THEN c.item
						ELSE NULL
					END
				) else 0 END as CNT1,
CASE WHEN a.Id=2   THEN 				
				SUM(CASE
						WHEN b.code IN ('A','B','C','D') AND c.item IS NULL THEN 1
						ELSE 0
					END
				) 	ELSE 0 END as CNT2


SUM(CNT1) + SUM(CNT2) AS TOTAL_CNT

Kindly throw some light on the same if you have any thoughts.

Thanks,
S.R

Posted: **Fri Sep 21, 2018 7:07 pm**

Replace CASE WHEN with IF and insert THEN after the first condition.
Replace every remaining WHEN with ELSE IF.
Remove the END.

You will need to have the results of the COUNT and SUM queries available, either as input columns or as job parameters.

Code: Select all

svCnt1  <--  If inLink.ID = 2 And Index("ABCD", inLink.Code, 1) Then #CountResult# Else SetNull()
svCnt2  <--  If inLink.ID = 2 And Index("ABCD", inLink.Code, 1) Then #SumResult# Else 0

DSXchange

Find difficulty to convert Case into IF else logic

Find difficulty to convert Case into IF else logic