Select table count into a parameter

bxy

• November 04, 2014 00:00

• Answered

Follow

Hi,

I'm new on Clover, and I was trying to pass the count of one table into a graph parameter (i.e. $count) then use that parameter to do further logic check.
However I tried many ways (including DBExecute and DBInputTable) and always get error like "Optimizing connection failed: The CLOSE_CURSORS_AT_COMMIT option is not currently supported by the setHoldability method."

Let's assume the count query is: select count(*) from table1 where condition1='A'
and I want the value to be passed to $count.

Would you please advise how I should do it?

In SSIS I use to have: Select "count"=count(*) from table1 where condition1='A', but this doesn't seem to work here.

Thanks a lot

• 

  hparsa

  • November 04, 2014 20:10

  0

  Comment actions Permalink

  Hi,
  
  You cannot dynamically assign values to parameters in a graph unless you pass it from a jobflow that calls your graph. Instead you can define a dictionary. So get the count and in an immediate Reformat component assign the count value to a dictionary. Remember you can use the value of that dictionary only in the same (if connected by an edge after assignment) or later phases.
  
  Data input table:
  
  select $COUNT:=count(*) from table1 where condition1='A'
  
  Reformat:
  
  dictionary.COUNT=$in.0.COUNT;

  • TEST_COUNT_DICTIONARY.grf

Please sign in to leave a comment.