Thread: Concatenated VARCHAR becomes TEXT in view
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.
I am using Postgresql 7.3 running on Redhat Linux 9.
CREATE OR REPLACE VIEW vw_event_summary AS
SELECT b.ltname, ((btrim((c.refullname )::text) || btrim((d.enname )::text)) || btrim((f.evname )::text)) AS evlinkname1, ((btrim((g.refullname )::text) || btrim((h.enname )::text)) || btrim((i.evname )::text)) AS evlinkname2, a.evid, a.evlinktype AS evltid, a.eventity1, a.evevent1, a.evresource1, a.eventity2, a.evevent2, a.evresource2
FROM event a, linktype b, resource c, entity d, event f, resource g, entity h, event i
WHERE (((((((a.evlinktype = b.ltid ) AND (a.evevent1 = f.evid )) AND (a.evevent2 = i.evid )) AND (a.evresource1 = c.reid )) AND (a.evresource2 = g.reid )) AND (a.eventity1 = d.enid )) AND (a.eventity2 = h.enid ));
ALTER TABLE vw_event_summary OWNER TO postgres;
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
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
>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