Re: Concatenated VARCHAR becomes TEXT in view

[MargaretGillon@xxxxxxxxxxxxxx]

>Martijn van Oosterhout <kleptog@xxxxxxxxx>
wrote on 02/01/2006 11:00:50 AM:

> On Wed, Feb 01, 2006 at 08:44:01AM -0800, MargaretGillon@xxxxxxxxxxxxxx
wrote:
> > I have a view which I use to populate list boxes on several input
screens 
> > in Visual FoxPro for Windows. In the view I concatenate three
varchar 
> > columns to make a new column. The concatenation works fine but
the 
> > resulting column is a text column, which becomes a memo field
in Visual 
> > FoxPro. Memos don't work well for list boxes. Is there any way
to get the 
> > resulting column as a varchar or char field? My view command
is below. I 
> > have tried changing the ::text to ::varchar but the outcome is
the same.
> 
> It's not clear from your query which ::text you converted to ::varchar,
> but what you need to do is cast the result, not the arguments. The
> result of btrim() is also of type text so you're actually concatinating
> three text strings. You need to put (blah)::varchar around everything.
> 
> Alternativly (what I generally do) is change the ODBC settings so
that
> text doesn't map to memo but to a normal string. That fixes it for
> me. IIRC there's a setting "Text as memo field" which you
untick.
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout   <kleptog@xxxxxxxxx>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is a
> > tool for doing 5% of the work and then sitting around waiting
for someone
> > else to do the other 95% so you can sue them.
> [attachment "signature.asc" deleted by Margaret Gillon/CLA/Chromalloy]


Hi Martijn,

The "btrim( ::text)" commands were added
by postgresql when I built the view. I tried adding a cast to the resulting
field but Postgresql 7.3 would not let me do that. I went back to the source
tables and found that one of them had the name column defined as CHAR()
while the other two columns I was concatenating were VARCHAR(). Maybe the
view had to cast to TEXT because of the different text types that I was
concatenating? Is there a way to cast columns created with SELECT AS in
version 7.3?

I have altered one table structure so that all the
columns being concatenated are VARCHAR and I have rebuilt the view. Now
the columns created from the concatenations are VARCHAR. 

Regarding changing the ODBC settings: I would not
like to convert all text fields to char type because I have many text fields
with large descriptions in them which would be truncated.

*** *** *** *** *** *** *** *** ***
*** *** *** *** *** *** *** *** *** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297