GeeCON Prague 2014*
The moose likes JDBC 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
Bookmark "Some quick SQL help on hard query" Watch "Some quick SQL help on hard query" New topic
Author

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!

A
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
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Some quick SQL help on hard query