Re: Concatenated VARCHAR becomes TEXT in view

>Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote on 02/01/2006 11:00:50
AM:

> On Wed, Feb 01, 2006 at 08:44:01AM -0800, MargaretGillon(at)chromalloy(dot)com
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(at)svana(dot)org> 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