Board index » delphi » Gigantic field size in Union SQL (Access)

Gigantic field size in Union SQL (Access)

I 'm using Access2000 with ADO. That's the situation :
I have two tables wich I'm joining in a sql using UNION.
Like :
select id, name from Table1
union
select id, name from Table2.

The field "name" is a varchar. Even if its size is 20 in
both tables, in the result set from this sql its size is
about 1500! Does anyone know how can i set the
size in the result set to a specific type(varchar(20).

Thanks,

Marlon medeiros Brum

 

Re:Gigantic field size in Union SQL (Access)


As a union is not updatable and can only be used for
display, you could create field definitions for the dataset
and set  the size of the name field to 20.

Sunil

Quote
"Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
>I 'm using Access2000 with ADO. That's the situation :
>I have two tables wich I'm joining in a sql using UNION.
>Like :
>select id, name from Table1
>union
>select id, name from Table2.

>The field "name" is a varchar. Even if its size is 20 in
>both tables, in the result set from this sql its size is
>about 1500! Does anyone know how can i set the
>size in the result set to a specific type(varchar(20).

>Thanks,

>Marlon medeiros Brum

Re:Gigantic field size in Union SQL (Access)


The problem is that this view will be used in another
view, and this field will be the "Foreign Key", but
when I use

Select * from Table1
Left outer join myView on (table1.regnumber = myView.RegNumber)

It gives an error. It can't make a relationship between a
 10 caracter field and a 1500 one.

"Sunil Furtado" <su...@furtado.de> escreveu na mensagem
news:3cf099c2$1_2@dnews...

Quote

> As a union is not updatable and can only be used for
> display, you could create field definitions for the dataset
> and set  the size of the name field to 20.

> Sunil

> "Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
> >I 'm using Access2000 with ADO. That's the situation :
> >I have two tables wich I'm joining in a sql using UNION.
> >Like :
> >select id, name from Table1
> >union
> >select id, name from Table2.

> >The field "name" is a varchar. Even if its size is 20 in
> >both tables, in the result set from this sql its size is
> >about 1500! Does anyone know how can i set the
> >size in the result set to a specific type(varchar(20).

> >Thanks,

> >Marlon medeiros Brum

Re:Gigantic field size in Union SQL (Access)


     Can you post the fields of both the tables used
in the union, the union query statement and the join sql
statement.

     From what you have given so far, it looks like the
join field is the regnumber which is not the field with the
problem(name).

     I created a relationship between the name field of the
original table and the name field of the union query without
any problem. The size if I am not mistaken is an ADO issue
and not an Access issue.

Sunil

Quote
"Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
>The problem is that this view will be used in another
>view, and this field will be the "Foreign Key", but
>when I use

>Select * from Table1
>Left outer join myView on (table1.regnumber = myView.RegNumber)

>It gives an error. It can't make a relationship between a
> 10 caracter field and a 1500 one.

Other Threads