Find difficulty to convert Case into IF else logic

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Find difficulty to convert Case into IF else logic

Post by satheesh_color »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply