Den 2011-06-09 14:47 skrev Alan J Davies såhär:

> I've just created a table with the data you show and a PK of dept_id,
> build_id and this is the copied and pasted result.
> Seems perfect to me, or am I missing the point?
>
> CREATE TABLE NEW_TABLE (
> DEPT_ID INTEGER NOT NULL,
> BUILD_D INTEGER NOT NULL
> );
>
> ALTER TABLE NEW_TABLE ADD CONSTRAINT PK_NEW_TABLE PRIMARY KEY (DEPT_ID,
> BUILD_D);
>
> select n.dept_id, n.build_d, n.dept_id||'-'||n.build_d acct_nmbr
> from new_table n
>
> DEPT_ID BUILD_D ACCT_NMBR
> 1 1 1-1
> 1 21 1-21
> 12 1 12-1
>
> Alan J Davies
> Aldis
>
> On 09/06/2011 13:23, Gordon Niessen wrote:
>> On 6/9/2011 3:29 AM, Alan J Davies wrote:
>>> If every record has this character "-", it becomes meaningless in an
>>> index. All you need is this:
>> This might not be true in all cases. Take for example the following
>> data set:
>> DEPT_ID BUILD_ID ACCOUNT_NBR
>> 1 1 1-1
>> 1 21 1-21
>> 12 1 12-1
>>
>> Separators can be important.

You're misunderstanding each other. Gordon just pointed out that post
concatenation the separator has (may have) significance.

Alan's suggested solution to the OP's problem is still valid, though,
since it is not dependent on the value post concatenation.

So, you're both right.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64