Board index » delphi » Unique index - huh?

Unique index - huh?

Hi,
   I'm really scratching my head over this one.  Is there any way to make
Local SQL to return a unique index field for each record when joining two
tables?

--
Regards,

Matt Palmer
Author of mpDockManager
http://freespace.{*word*269}.net/matt.palmer/

 

Re:Unique index - huh?


On Wed, 9 Feb 2000 00:32:25 -0000, "Matt P" <matt.pal...@{*word*269}.net>
wrote:

Quote
>   I'm really scratching my head over this one.  Is there any way to make
>Local SQL to return a unique index field for each record when joining two
>tables?

If you have normalized tables, the unique key for the joined result
would be the combination of keys of the original tables.

Jan

Re:Unique index - huh?


True, but how could I make Local SQL return this as a *single-field*,
composite unique key?  Basically, the grid I'm using requires that there is
a single-field unique field - ExpressGrid.  If I don't get this, I can't let
the user re-group the grid interactively...

--
Regards,

Matt Palmer
Author of mpDockManager
http://freespace.{*word*269}.net/matt.palmer/

Quote
Jan Sprengers <j...@nospamplease.adm2000.be> wrote in message

news:38a137db.68635562@forums.inprise.com...
Quote

Re:Unique index - huh?


On Wed, 9 Feb 2000 12:31:40 -0000, "Matt P" <matt.pal...@{*word*269}.net>
wrote:

Quote
>True, but how could I make Local SQL return this as a *single-field*,
>composite unique key?  Basically, the grid I'm using requires that there is
>a single-field unique field - ExpressGrid.  If I don't get this, I can't let
>the user re-group the grid interactively...

You have to concatenate the key fields in order to form a new virtual
field with the + or || operator.  If this aren't string fields, you
need to convert them to strings first.  E.g.:
  select A.StrFld+Cast(B.IntFld as Char(8)) as KeyFld, ...

HTH,

Jan

Re:Unique index - huh?


Quote
On Wed, 9 Feb 2000 12:31:40 -0000, "Matt P" <matt.pal...@{*word*269}.net> wrote:
>True, but how could I make Local SQL return this as a *single-field*,
>composite unique key?  Basically, the grid I'm using requires that there is
>a single-field unique field - ExpressGrid.  If I don't get this, I can't let
>the user re-group the grid interactively...

Jan Sprengers describes how to make a calculated column that is the two
values combined into one. I would add that the local SQL online help
describes joining tables on composite key values. These two actions are
mutually exclusive, in nature and in use. You need not concatenate the two
columns in the SELECT clause to join tables on composite keys and if you do
concatenate them in the SELECT clause it has no effect on using composite
keys in the join.

Local SQL (the SQL implementation the BDE uses for dBASE, Paradox, and
FoxPro tables) is documented in the online help file LOCALSQL.HLP, found in
the main BDE directory. This help file is a language reference of the local
SQL implementation (subset) of SQL-92. The copy of this file that came with
BDE 4.x (and earlier versions) was seriously out of date and contained
little useful information. It has since been rewritten. It has been updated
again concurrent with C++Builder 5. Updated copies will have the topic
"VIEWs" in the index (and lack of this topic indicates a pre-update copy).
I can e-mail you a copy of the updated file if you need and desire it.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Re:Unique index - huh?


Thanks Jan,
   I've actually come up with another solution to my particular problem -
but that does the trick too.

Regards,

Matt Palmer
Author of mpDockManager
http://freespace.{*word*269}.net/matt.palmer/

Quote
Jan Sprengers <j...@nospamplease.adm2000.be> wrote in message

news:38a173d0.83984393@forums.inprise.com...
Quote

Re:Unique index - huh?


In the following example (DBDEMOS) :

select * from customer c
inner join orders o
on o.custno = c.custno

The field orders.orderno is unique in that result set. You could use that as
a key field in your Express Grid.

--

Alain Quesnel

Product Manager
Argos Software
alainnos...@argosoftware.com

===========

Quote
"Matt P" <matt.pal...@{*word*269}.net> wrote in message

news:87qbgh$9ju14@bornews.borland.com...
Quote
> Hi,
>    I'm really scratching my head over this one.  Is there any way to make
> Local SQL to return a unique index field for each record when joining two
> tables?

> --
> Regards,

> Matt Palmer
> Author of mpDockManager
> http://freespace.{*word*269}.net/matt.palmer/

Re:Unique index - huh?


In the following example (DBDEMOS) :

select * from customer c
inner join orders o
on o.custno = c.custno

The field orders.orderno is unique in that result set. You could use that as
a key field in your Express Grid.

--

Alain Quesnel

Product Manager
Argos Software
alainnos...@argosoftware.com

===========

Quote
"Matt P" <matt.pal...@{*word*269}.net> wrote in message

news:87qbgh$9ju14@bornews.borland.com...
Quote
> Hi,
>    I'm really scratching my head over this one.  Is there any way to make
> Local SQL to return a unique index field for each record when joining two
> tables?

> --
> Regards,

> Matt Palmer
> Author of mpDockManager
> http://freespace.{*word*269}.net/matt.palmer/

Re:Unique index - huh?


Thanks Alain,

   in my tables, the tables were joined in such a way that a single field
would not produce a unique key. My solution was to modify ExpressGrid to
allow more than one field as a primary key (as any decent grid should,
IMHO!).

  It wasn't all that difficult to do, and solved the basic problem I had
with ExpressGrid - that of requiring a _single_ unique field in order to
group data.  This was a problem even on tables I wasn't joining, since some
of our tables were keyed on more than one field.

--
Regards,

Matt Palmer
Author of mpDockManager
http://freespace.{*word*269}.net/matt.palmer/

Re:Unique index - huh?


How did you modify it?

--

Alain Quesnel

Product Manager
Argos Software
alainnos...@argosoftware.com

===========

Quote
"Matt P" <matt.pal...@{*word*269}.net> wrote in message

news:87uqlj$cim15@bornews.borland.com...
Quote
> Thanks Alain,

>    in my tables, the tables were joined in such a way that a single field
> would not produce a unique key. My solution was to modify ExpressGrid to
> allow more than one field as a primary key (as any decent grid should,
> IMHO!).

>   It wasn't all that difficult to do, and solved the basic problem I had
> with ExpressGrid - that of requiring a _single_ unique field in order to
> group data.  This was a problem even on tables I wasn't joining, since
some
> of our tables were keyed on more than one field.

> --
> Regards,

> Matt Palmer
> Author of mpDockManager
> http://freespace.{*word*269}.net/matt.palmer/

Other Threads