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/
Show quoted text
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.
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