insert ":expression" to complete the expression error

Five Stars

insert ":expression" to complete the expression error

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.

Community Manager

Re: insert ":expression" to complete the expression error

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

Five Stars

Re: insert ":expression" to complete the expression error

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.
Community Manager

Re: insert ":expression" to complete the expression error

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog