Hi guys, I am trying to write a query that in one of the where columns there is a default value and it's giving me trouble. Basically, I narrow a table of records down by a few different fields. One of the fields is a "unittype" field. There's a know set of values in the table, so I set a value to check like unittype = 'S3', but there are also records in the same column like '**' which stands for default. The table has an "effectivedate" colum, so I set up a where effectivedate > "somedate" and unittype = 'S3'. My problem is this, what I really want is where unittype = 'S3' or if there is no 'S3' use the '**' record. Also if there were both an 'S3' and '**' that met the other criteria, I can only have one of the records returned, because this is a subquery. I would want the 'S3' always first and the '**' only if no 'S3' existed that met the effective date requirement. So I need some help! how to I write the where statement so that it favors the 'S3', but still inserts the '**' record if it can't find 'S3'? Thanks a million!
most databases support the 'exists' functionality, so you could do something like this: select a.unittype from unit a where ( a.unittype = 'S3' OR ( a.unittype = '**' and not exists ( select b.unittype from unit b where b.unittype = 'S3' and effectivedate > somedate ) ) ) AND effectivedate > somedate something like that anyways Jamie