>Martijn van Oosterhout <kleptog@svana.org> wrote on 02/01/2006 11:00:50 AM:
> On Wed, Feb 01, 2006 at 08:44:01AM -0800, MargaretGillon@chromalloy.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@svana.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