Hi Jason

With IBO 5.9.5 2652 I have a problem with a "SELECT DISTINCT" and CountSQL.

My example query is:

 SELECT DISTINCT ActorID
 FROM ResPensAbrech rpa
    join ResultAct ra on rpa.ResultActID = ra.ResultActID
    join Event on ra.EventID = Event.EventID
 WHERE AbrechYear = :pAbrechYear and EventYear = :pAbrechYear and ContraEventID is Null
 PLAN SORT (JOIN (RPA NATURAL,RA INDEX (RESULTACT_PK),EVENT INDEX (EVENT_PK)))

I have the following two problems:

The CountSQL doesn't contain the PLAN. In case of not using "EXECUTE BLOCK" the CountSQL is:

 SELECT count(DISTINCT ActorID)
      FROM ResPensAbrech rpa
        join ResultAct ra on rpa.ResultActID = ra.ResultActID
        join Event on ra.EventID = Event.EventID
      WHERE AbrechYear = :pAbrechYear and EventYear = :pAbrechYear and ContraEventID is Null

Also with EXECUTE BLOCK the PLAN is not incorporated. Is there any reason for not using the plan?

The second problem is with "EXECUTE BLOCK" used for the count: This query uses the same parameter twice. In this case the "EXECUTE BLOCK" statement looks like

EXECUTE BLOCK ( pAbrechYear BIGINT = ?,pAbrechYear BIGINT = ? )
RETURNS ( CountRows BIGINT )
AS
DECLARE VARIABLE fld0 NUMERIC( 18, 0 );
BEGIN
  CountRows = 0;
  FOR
/* Select to Count Begin */
SELECT DISTINCT ActorID FROM ResPensAbrech rpa
   join ResultAct ra on rpa.ResultActID = ra.ResultActID  
   join Event on ra.EventID = Event.EventID
   WHERE AbrechYear = :pAbrechYear and EventYear = :pAbrechYear and ContraEventID is Null
/* Select to Count End */
  INTO   :fld0
  DO CountRows = CountRows + 1;
  SUSPEND;
END

This statement causes an error as pAbrechYear is used twice in the EXECUTE BLOCK parameter list. Do you plan to fix this problem?

Kind regards

Daniel