Re: Constructing an SQL WHERE clause for a composite key

James

I think you add more to what you already have. This is similar to filtering on dates when they are stored in 3 fields.

BTW, I would make it FOO = rather than FOO <= . That's a small point, perhaps.

For the date thing, say you want to select all dates before May 6, 2012 - it'd be something like -

year < 2012 or (year = 2012 and month < 5) or (year = 2012 and month = 5 and day < 6)

Now an alternative is to concatenate character representations. Trouble with this? It is likely to perform relatively badly, due to using functions. I think some of the new indexing techniques handle this better.

HTH
Vern

On 8/2/2013 7:28 PM, James H. H. Lampert wrote:

I just realized that I have a problem with key construction in my JDBCR4
project:

It works great when the key is a single field. But it fails when it's
multiple fields.

Suppose I have a file with keyfields FOO and BAR, with FOO being a
9-digit number, and BAR being an 11-digit number.

Now, suppose I have records with the following key values in the file:

FOO BAR
180624 10000105018
180627 10000105020
180639 10000105100
180639 10000105333
180639 10000105334
180648 10000105169
180651 10000105273
180652 10000105275
180654 10000105292
180654 10000105293


Now, suppose I'm looking for two result sets, both "ORDER BY FOO, BAR":
one of all the records from 180648 10000105169 through EOF, and one of
all the records from TOF to 180648 10000105169.

Obviously, if I were to do "WHERE FOO <= 180648 AND BAR <= 10000105169"
for the second result set, it would miss 180639 10000105333 and 180639
10000105334 entirely, skipping back to 180639 10000105100.

I *could* do something like "WHERE (FOO <= 180648 AND BAR <=
10000105169) OR FOO < 180648" -- but what happens if I'm keying on 3
fields? Is there an easier, more general solution?

--
JHHL