Call a select statment for each record

ponderena

• September 12, 2012 00:00

• Answered

Follow

I am new to CloverETL. And I do not know Java.

I would like to know how to do a select the max value of a field from an incoming record, according to a key: select nvl(max(days),0) from table where key = <key from incoming record>.
Then I want to insert a record if the select is null therefore selecting 0.

I would be interested in knowing what component I would use in a graph and the code behind it, if needed.

Thanks.
:|

• 

  imriskal

  • September 14, 2012 11:36

  0

  Comment actions Permalink

  Hi ponderena,
  
  if you want to do some query on the database, you can prepare your statement e.g. in a Reformat component with CTL code as following:
  

  
  function integer transform() {
  	$out.0.query = "select nvl(max(days),0) from table where key = " + $in.0.key;
  
  	return OK;
  }
  

  
  Then you can query your database with a DBInputTable with Query URL set to port:$0.query:discrete. You can process the output records and insert it into the database again with a DBOutputTable component.
  
  If your question was meant more generally and you want to know how similar procedure can be done in CloverETL enviroment, you can achieve it for example this way:
  
  max() is an aggregate function so you can use an Aggregate component. Aggregate key will be your abovementioned key.
  nvl() can be replaced by a simple Reformat with CTL code like:
  

  
  if ($in.0.field1 == null) {
  	$out.0.field1 = 0;
  }
  

  
  If something was unclear to you, please, let me know.
  
  Best regards,

Please sign in to leave a comment.