RE: Constructing an SQL WHERE clause for a composite key

James can describe the scenario that causes you to need this selection
criteria? Or how the key construction is bad? What do those 2 fields
represent? If we knew what the data elements were , perhaps we could help
you decide if the key construction is faulty.

Vern is correct, you need to define your "cut off" key and treat each field
as separate and set your selection by rules by each field.

Paul Therrien
Andeco Software, LLC
225-229-2491
paultherrien@xxxxxxxxxxxxxxxxxx
www.andecosoftware.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Saturday, August 03, 2013 2:12 AM
To: Midrange Systems Technical Discussion
Subject: 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.