--Hi, all I ran into a little problem that I do not understand any help is appreciated.

--if i run the below query I seem to loose characters, even though I have a varchar(20)  'p_ReticPct' is being cut to 'p_Ret'

--I am getting this result set

--p_HGB
--p_HCT
--p_WBC
--p_RBC
--p_Ret -- here I should have p_ReticPct but for some reason I am loosing data if I add more chacters to 'P_HGB' eg.('P_HGBsssssss)' I receive all of 'p_ReticPct'.

--please note: this is a small section of a much larger script


drop table if exists memory\ParamVals;
create table memory\ParamVals ("dbfieldname" varchar(20));

INSERT INTO memory\ParamVals
SELECT 'p_HGB' FROM memory\TheSpec

union

SELECT 'p_HCT' FROM memory\TheSpec

union

SELECT 'p_WBC' FROM memory\TheSpec

union

SELECT 'p_RBC' FROM memory\TheSpec

union

SELECT 'p_ReticPct' FROM memory\TheSpec;

SELECT * FROM memory\ParamVals;

Silven

In a UNION, the column types are always the types of the columns in the first
SELECT, so to get a varchar(20) you must change your query to:

INSERT INTO memory\ParamVals
  SELECT CAST('p_HGB' AS VARCHAR(20)) FROM memory\TheSpec
  UNION
  SELECT 'p_HCT' FROM memory\TheSpec
  UNION
  ...

--
Fernando Dias
[Team Elevate]

Thanks, for the quick reply... what about the create table memory\ParamValss ("dbfieldname" varchar(20)); it just ignores my varchar(20)

Is there a way to enter the below query into a table without declaring a table like I tried to do?

Thanks,
for the help

Fernando Dias wrote:

Silven

In a UNION, the column types are always the types of the columns in the first
SELECT, so to get a varchar(20) you must change your query to:

INSERT INTO memory\ParamVals
  SELECT CAST('p_HGB' AS VARCHAR(20)) FROM memory\TheSpec
  UNION
  SELECT 'p_HCT' FROM memory\TheSpec
  UNION
  ...

--
Fernando Dias
[Team Elevate]

"silven" <silven@canada.com> wrote in message
news:E67C269B-541D-4D7E-B6FC-6BF25105096A@news.elevatesoft.com...
> Thanks, for the quick reply... what about the create table
> memory\ParamValss ("dbfieldname" varchar(20)); it just ignores my
> varchar(20)
>
> Is there a way to enter the below query into a table without declaring a
> table like I tried to do?
>

The field length of the dataset being INSERTED INTO is already truncated
before you insert it. Your table field is the right length, the killer is
the dataset created by the SELECT. That's where you chop the data, and then
when it gets inserted you already lost the trailing characters. You will
have to include the field definition in the first SELECT as suggested by
Fernando.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:18F68F6E-885C-49CC-BA67-C3D4EF56EEB0@news.elevatesoft.com...
>
You will
> have to include the field definition in the first SELECT as suggested by
> Fernando.
>

Or change the order of the SELECT .. UNION so that the first select is for
the largest field.

Robert

Thanks for the help

"Robert" wrote:


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:18F68F6E-885C-49CC-BA67-C3D4EF56EEB0@news.elevatesoft.com...
>
You will
> have to include the field definition in the first SELECT as suggested by
> Fernando.
>

Or change the order of the SELECT .. UNION so that the first select is for
the largest field.

Robert