Erik, in this case it works quite well.

I am using the where clause to fire the not in subquery. So I am postponing the "validation" normaly placed in the "where" till after the sets are put together. So a having is correct, in this case.

The more I play with this stuff the more confused I become. Do you ever read those SQL for Smarties books? I think that there are 2 out now. I use to have access to them at a former job. Great for tough SQL! Joe Celko who has a column in DBMS mag.


Any ideas on getting what I wanted?




>>SELECT lv_icitem_desc.item , lv_icitem_desc.itmclss ;
>> FROM lv_icitem_desc WHERE item NOT IN ;
>> (SELECT DISTINCT item_no as item FROM lv_inv_count) ;
>> order by 2,1 having val(itmclss) > 5
>
>
>You can only use the HAVING clause in conjunction with a GROUP BY. It is like a WHERE for a group. Is there a reason you're not just using WHERE VAL(itmclss) > 5?