>ON TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence > >become > >ON BINTOC(TI.cTrans_fk)+BINTOC(TI.iSequence_Number) = BINTOC(X.cTrans_pk)+BINTOC(X.iSequence_Number) >>
>* try 1 : same ? >SELECT TI.cTrans_fk, TI.cInsurances_fk; > FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number; > from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ; > GROUP BY 1) X ; > JOIN Trans_Insurances TI ; > ON TI.cTrans_fk = X.cTrans_pk ; > WHERE TI.iSequence_Number = X.iSequence_Number; >into cursor curTemp1 Time: 3.554 Rushmore optimization level for intermediate result: none Rushmore optimization level for table t: none Joining intermediate result and table t using index tag Ctrans_fk Rushmore optimization level for intermediate result: none Rushmore optimization level for table ti: none Joining intermediate result and table ti using temp index >* try 2 : error ? >SELECT TI.cTrans_fk, TI.cInsurances_fk; > FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number; > from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ; > GROUP BY 1) X ; > JOIN Trans_Insurances TI ; > ON TI.cTrans_fk = X.cTrans_pk ; > HAVING TI.iSequence_Number = X.iSequence_Number; >into cursor curTemp1 > Time: 0.046 Rushmore optimization level for intermediate result: none Rushmore optimization level for table t: none Joining intermediate result and table t using index tag Ctrans_fk Rushmore optimization level for intermediate result: none Rushmore optimization level for table ti: none Joining intermediate result and table ti using index tag Ctrans_fk Looks like a winner!!!!!!!!!!!! >* try 3 : can to go in error if (cTrans_fk ,iSequence_Number) is not unique >SELECT TI.cTrans_fk; > , (SELECT TI.cInsurances_fk FROM Trans_Insurances TI ; > WHERE TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence_Number); > cInsurances_fk; > FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number; > from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ; > GROUP BY 1) X ; >into cursor curTemp1 > Gives an error alias TI is not found. >* try 4 >SELECT TI.cTrans_fk, TI.cInsurances_fk; > FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number; > from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ; > GROUP BY 1) X ; > LEFT JOIN Trans_Insurances TI ; > ON TI.cTrans_fk = X.cTrans_pk ; > WHERE TI.iSequence_Number = X.iSequence_Number; >into cursor curTemp1 > > Time: 0.053 Rushmore optimization level for intermediate result: none Rushmore optimization level for table t: none Joining intermediate result and table t using index tag Ctrans_fk Rushmore optimization level for intermediate result: none Rushmore optimization level for table ti: none Joining intermediate result and table ti using index tag Ctrans_fk Looks like a winner too. >* try 5 >SELECT TI.cTrans_fk, TI.cInsurances_fk; > FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number; > from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ; > GROUP BY 1) X ; > JOIN Trans_Insurances TI ; > ON TI.cTrans_fk = X.cTrans_pk and INLIST(X.iSequence_Number,TI.iSequence_Number) ; >into cursor curTemp1 >Time: 0.061