[SOLVED] Convert/Cast LONG VARCHAR to VARCHAR in Gupta SQLBase 12

Convert/Cast LONG VARCHAR to VARCHAR in Gupta SQLBase 12

I'm trying to convert/cast a SQLBase LONG VARCHAR column into a VARCHAR Column using a temporary table.

The source table being used has a comment field column of type "long varchar". I am unable to find the correct syntax to use a convert/cast function.

If I try to implicitly cast/convert as below, the error I receive is

Error: 01627 TYP LON Invalid data type (long expected)

create table SYSADM.TMP_STUDENT_ (
    STUDENT_NO integer,
    STUDENT_COMMENTS nvarchar(255)
) pctfree 10;

insert into SYSADM.TMP_STUDENT_ (
    STUDENT_NO ,
    STUDENT_COMMENTS ) select
    STUDENT_NO,
    STUDENT_COMMENTS 
from SYSADM.STUDENT;

grant all on SYSADM.TMP_STUDENT_ to PUBLIC;

Solution

You cannot use a LONG VARCHAR column in a subselect.

But to achieve what you want you can run an UNLOAD ( table ) and then a LOAD ( into new Table ).

To see all the syntax options for these commands go to : SQLBase Books . download the version you want, and read the 'Language Reference' manual, specifically UNLOAD and LOAD. This will work if you get the syntax right - I've done it before, but there's too much, to cover all the options here..

Alternatively, you can write a stored procedure (SQLBase Stored Procs are great - you can use SAL as you would in TD. Easy to write if you use SQLCommandCentre) to execute from SQLTalk. In a loop, read the LONG col into a Long String, and then Insert into the varchar col from the Long String.