ASE Database forum - Select DISTINCT Bug?

Author

Select DISTINCT Bug?

Scott R. Rouse

2005-12-08, 8:25 pm

FYI
I get an error if I try to insert the results of a select distinct along
with a static text string into a table that has a text datatype.
I am not doing a select distinct on the text column.

I receive the following error:[color=darkred
]
selected as DISTINCT.

create table #one(
id char(6),
mydate datetime,
mydata integer
)

create table #two(
id char(6),
mydate datetime,
mytext text
)
go

insert into #one values ("AAA",'1/1/05',123)
insert into #one values ("AAA",'1/1/05',124)
insert into #one values ("BBB",'1/1/05',123)
insert into #one values ("BBB",'1/1/05',124)
go

--insert into #two(id,mydate,mytex
t)
select distinct id,
mydate,
"This is a string to be inserted into a text field."
from #one
go

This will run fine as long as you do not try to do the insert.
Remove the "--" and I get above error.

ASE 12.5.3

Mark A. Parsons

2005-12-08, 8:25 pm

My guess is that the 'select' takes the following steps:

1 - obtain the datatypes of the target columns

2 - build an initial result set from the 'select' list; reformatting the
individual result columns into datatypes that match their associated
target columns (eg, the static text string is converted to 'text');
think 'implicit' datatype conversions

3 - perform any post-query operations on the initial result set (which
now holds the reformatted data), eg, 'distinct', 'group by', etc.

4 - insert the final result (data has already been converted to the
appropriate datatypes) set into the target table

The fact that your stand alone select works just fine would follow the
above steps with the difference that there is no step #1, so the
datatypes of the columns in question do not change. (or you could
assume that there is a step #1, but that the target is ... for lack of a
better term ... stdout; again, no need to convert datatypes.)

Soooo, it would appear that the insert/select is running some implicit
convert()'s on your data in preparation for inserting into the target table.

------------------------

One possible work around would include the use of a derived table, like
such:

insert #two
select d.id,
d.mydate,
"This is a string to be inserted into a text field."
from (select distinct
id,
mydate
from #one) d(id,mydate)
go
(2 rows affected)

Scott R. Rouse wrote:

> FYI
> I get an error if I try to insert the results of a select distinct along
> with a static text string into a table that has a text datatype.
> I am not doing a select distinct on the text column.
>
> I receive the following error:
> selected as DISTINCT.
>
> create table #one(
> id char(6),
> mydate datetime,
> mydata integer
> )
>
> create table #two(
> id char(6),
> mydate datetime,
> mytext text
> )
> go
>
>
> insert into #one values ("AAA",'1/1/05',123)
> insert into #one values ("AAA",'1/1/05',124)
> insert into #one values ("BBB",'1/1/05',123)
> insert into #one values ("BBB",'1/1/05',124)
> go
>
> --insert into #two(id,mydate,mytex
t)
> select distinct id,
> mydate,
> "This is a string to be inserted into a text field."
> from #one
> go
>
> This will run fine as long as you do not try to do the insert.
> Remove the "--" and I get above error.
>
> ASE 12.5.3

Sponsored Links