Count on concatenated fields

Is there an easier method, that is without sending the interim results to a temp table, to perform a count distinct of two concatenated fields. Current method: select id_num||sdate as fld1 from a_ms_test into temp test1; select count(distinct fld1) from test1

select distinct( id_num||sdate ) as fld1 from a_ms_test; Works for me. I tried: select distinct( tabname||tabid ) from systables; and got: GL_COLLATE 90 GL_CTYPE 91 VERSION 99 access_stats 475 avg_costs 439 backdate_cash 440 eqty_cash 441 export_tracking 422 firm_data 100 interest_cash 442 mtge_cash 443 or this: select distinct( trim(tabname) || '_'||tabid) from systables;GL_COLLATE_90 GL_CTYPE_91 VERSION_99 access_stats_475 Art S. Kagel ----- Original Message ----- From: Tony Demeis <ids@iiug.org> At: 6/06 12:04:40 Is there an easier method, that is without sending the interim results to a temp table, to perform a count distinct of two concatenated fields. Current method: select id_num||sdate as fld1 from a_ms_test into temp test1; select count(distinct fld1) from test1 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi you can use count on distinct concatenated fields like select count(distinct id_num||sdate) as fld1 from a_ms_test But this will cause more overhead ( cost of query if you check using set explain on will be more) Its recommended to use temp table for better performance of such qureries. Regards Nilesh P Bhavsar