File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Some quick SQL help on hard query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Some quick SQL help on hard query" Watch "Some quick SQL help on hard query" New topic

Some quick SQL help on hard query

Paul Duer
Ranch Hand

Joined: Oct 10, 2002
Posts: 98
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!

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
I agree. Here's the link:
subject: Some quick SQL help on hard query
It's not a secret anymore!