Re: 617 error

Darren_Jacobs@carmax.com wrote: > I have a user that needs to insert a char into a text datatype. The > following 617 error is being received. I've looked into cast's but not > sure if it will work. The sql statement is below. I know I've seen > chatter on this before but do not recall the solution. > > Any help would be greatly appreciated. > > 9.40 FC2w4 > HPUX 11.11 > -617 A TEXT or BYTE data type must be supplied within this context. > > This statement assigns a value that is not a simple large object to a > BYTE or TEXT column or assigns a BYTE or TEXT column to a column that > is not a simple large object. Neither action is supported. BYTE and > TEXT values must be assigned as whole units to columns of the same > type. Check that the statement specifies the columns that you > intended. > > insert into ps_cx_age_cmnt > (business_unit, cust_id, item, cx_comment_date, cx_oprid, > comment_source, cx_caf_sup_review, cx_perm_message, > cx_cmx_sup_review, cx_descrlong, cx_initials)> SELECT > business_unit, cust_id, item, accounting_dt, "CARMAX", "C", " ", > " ", " ", > cx_comments, -- (This is the character field trying to insert into > cx_descrlong, which is a text field) > "N/A" > from ps_cx_penditem > where cx_comments <> ' '; I suspect your problem is that there is no mechanism built into IDS for converting any type of data to BYTE or TEXT - there are no blob literals, for example. This is a moderately well known and very long standing problem (ever since 1990, when BYTE and TEXT blobs were introduced - and I remember writing a demo for the Which 1990 computer show in London using blobs). There are some feature requests aimed at getting this fixed - explicit casts between *CHAR types and TEXT or CLOB (maybe or maybe not implicit casts), and for literal strings for blobs too - including SQL standard hexadecimal strings x'A09A2B' etc. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Thanks Jonathan for the response, and others! I'm going to have the user call a script from a strpoc to perform a unload/load. This should meet their needs. Jonathan Leffler <jleffler@earthli nk.net> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: 617 error 03/17/2006 12:04 AM Darren_Jacobs@carmax.com wrote: > I have a user that needs to insert a char into a text datatype. The > following 617 error is being received. I've looked into cast's but not > sure if it will work. The sql statement is below. I know I've seen > chatter on this before but do not recall the solution. > > Any help would be greatly appreciated. > > 9.40 FC2w4 > HPUX 11.11 > -617 A TEXT or BYTE data type must be supplied within this context. > > This statement assigns a value that is not a simple large object to a > BYTE or TEXT column or assigns a BYTE or TEXT column to a column that > is not a simple large object. Neither action is supported. BYTE and > TEXT values must be assigned as whole units to columns of the same > type. Check that the statement specifies the columns that you > intended. > > insert into ps_cx_age_cmnt > (business_unit, cust_id, item, cx_comment_date, cx_oprid, > comment_source, cx_caf_sup_review, cx_perm_message, > cx_cmx_sup_review, cx_descrlong, cx_initials)> SELECT > business_unit, cust_id, item, accounting_dt, "CARMAX", "C", " ", > " ", " ", > cx_comments, -- (This is the character field trying to insert into > cx_descrlong, which is a text field) > "N/A" > from ps_cx_penditem > where cx_comments <> ' '; I suspect your problem is that there is no mechanism built into IDS for converting any type of data to BYTE or TEXT - there are no blob literals, for example. This is a moderately well known and very long standing problem (ever since 1990, when BYTE and TEXT blobs were introduced - and I remember writing a demo for the Which 1990 computer show in London using blobs). There are some feature requests aimed at getting this fixed - explicit casts between *CHAR types and TEXT or CLOB (maybe or maybe not implicit casts), and for literal strings for blobs too - including SQL standard hexadecimal strings x'A09A2B' etc. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list