insert ":expression" to complete the expression error

[Anonymous]

Hi,

Below is my case statement.

CASE
WHEN row1.EFFECTIVE_START_DATE= row1.DATE_START
AND row1.DATE_START = row1.POW_ORIGINAL_HIRE_DATE
THEN 'New Hire'
WHEN row1.effective_start_date = row1.DATE_START
AND row1.DATE_START > row1.POW_ORIGINAL_HIRE_DATE
THEN 'Rehire'
WHEN row1.effective_start_date > row1.DATE_START
AND row1.effective_start_date = row1.ASG_START_DATE
THEN 'Assignment Start'
WHEN row1.effective_start_date > row1.ACTUAL_TERMINATION_DATE
THEN 'Voluntary Termination'
WHEN row1.effective_start_date <= COALESCE(row1.ACTUAL_TERMINATION_DATE,row1.effective_end_date)
AND row1.effective_start_date = row1.ASG_END_DATE
THEN 'Assignment End'
ELSE 'Assignment Change'
END EVENT_SUBG_DESC

I need to implement this in tmap for a column. So, I changed it using ternary operators as below but I am facing insert ":expression" to complete the expression error.

(( row1.effective_start_date == row1.DATE_START) && (row1.DATE_START==row1.POW_ORIGINAL_HIRE_DATE)) ? "New Hire"
: (((row1.effective_start_date == row1.DATE_START) && (row1.DATE_START > row1.POW_ORIGINAL_HIRE_DATE))
? "Rehire"
: (((row1.effective_start_date > row1.DATE_START) && (row1.effective_start_date == row1.ASG_START_DATE))
? "Assignment Start"
: ((row1.effective_start_date > row1.ACTUAL_TERMINATION_DATE)
? "Voluntary Termination"
: ((row1.effective_start_date <=
(ISNULL(row1.ACTUAL_TERMINATION_DATE)?row1.effective_end_date : row1.ACTUAL_TERMINATION_DATE)&& (row1.effective_start_date == row1.ASG_END_DATE))
? "Assignment End" :"Assignment Change"))))

Can anyone help me out.

Thanks in advance.

[Anonymous]

I'd recommend writing your case statement into a routine and call it from the tMap. It will be much easier for you and anyone else who ends up working on your job

[Anonymous]

Thanks for the response.
I have columns from database tables that will be compared. How can I refer those in routines.
Could you please help me with an example as I am new to Talend.

[Anonymous]

OK, to write a routine you need to read this....

https://help.talend.com/reader/C8mznD9TYsuB~SWG77PPDQ/KFMROs1xk5w9fefl7uf1AA

I have hacked together an example of how you would do this. This might not work perfectly, I have literally written it off of the top of my head. But it is the right way of thinking about this.

public static String getEmploymentStatus(Date effective_start_date, Date effective_end_date, Date date_start, Date asg_start_date, Date asg_end_date, Date actual_termination_date, Date pow_original_hire_date){
	String returnVal = "";
	
	if(effective_start_date.compareTo(date_start)==0 && date_start.compareTo(pow_original_hire_date)==0){
		"New Hire"
	}else if(effective_start_date.compareTo(start_date)==0 && date_start.compareTo(pow_original_hire_date)>0){
		"Rehire"
	}else if(effective_start_date.compareTo(date_start)>0 && effective_start_date.compareTo(asg_start_date)==0){
		"Assignment Start"
	}else if(((actual_termination_date!=null && effective_start_date.c.compareTo(actual_termination_date)<=0)||(effective_end_date!=null && effective_start_date.c.compareTo(effective_end_date)<=0)) && effective_start_date.compareTo(asg_end_date)==0){
		"Assignment End"
	}else{
		"Assignment Change"
	}

	return returnVal;
	
}

You would then call the routine in your tMap and pass your row values to the respective parameters in the method signature.