Hi,

I'm having some trouble with this: I have a table that can save a text 
column, and can be null. Also, have another table containing some of the 
columns from the first, and this should take the Text column if not 
empty, otherwise must take a char column from a third table, all of this 
within the SP.

If I try to do it:

Create ...
Begin
..
..
..
Insert into B(id, description)
Select  A.id,
	Case when DataLength(A.large_text) < 1  then
         	C.describe_it
	Else
         	A.large_text
         End
Where id = @id
..
..
..
End


I get this error:
Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. 
Use the CONVERT function to run this query.

( only the C table has a char column. A and B are text columns )

Any suggestion?

Thanks in advance!

***PS: I'm on ASE 12.5, and perhaps I'll be moving it to ASE 15 in short 
time.

"Jose Manuel Espinoza" <jmespinoza@seicom.com.mx> wrote in message 
news:4d02804a$1@forums-1-dub...
> Hi,
>
> I'm having some trouble with this: I have a table that can save a text 
> column, and can be null. Also, have another table containing some of the 
> columns from the first, and this should take the Text column if not empty, 
> otherwise must take a char column from a third table, all of this within 
> the SP.
>
> If I try to do it:
>
> Create ...
> Begin
> .
> .
> .
> Insert into B(id, description)
> Select  A.id,
> Case when DataLength(A.large_text) < 1  then
>         C.describe_it
> Else
>         A.large_text
>         End
> Where id = @id
> .
> .
> .
> End
>
>
> I get this error:
> Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. Use 
> the CONVERT function to run this query.
>
> ( only the C table has a char column. A and B are text columns )
>
> Any suggestion?
>
> Thanks in advance!
>
> ***PS: I'm on ASE 12.5, and perhaps I'll be moving it to ASE 15 in short 
> time.


Have you tried an explicit conversion (and some form of a join):

 Insert into B (id, description)
     select  A.id,
                case
                      when DataLength (A.large_text) < 1   then  convert 
(text, C.describe_it)
                      else 
A.large_text
                end
      from A, C
      where A.id = @id
          and C.id = @id
 .
 .

The error message does not seem to conform with the type descriptions 
provided.  You're trying to (sometimes) place a char column into a text 
column?  But the error message says the opposite.

> Hi,
>
> I'm having some trouble with this: I have a table that can
> save a text  column, and can be null. Also, have another
> table containing some of the  columns from the first, and
> this should take the Text column if not  empty, otherwise
> must take a char column from a third table, all of this
> within the SP.
>
> If I try to do it:
>
> Create ...
> Begin
> .
> .
> .
> Insert into B(id, description)
> Select  A.id,
>     Case when DataLength(A.large_text) < 1  then
>              C.describe_it
>     Else
>              A.large_text
>          End
> Where id = @id
> .
> .
> .
> End
>
>
> I get this error:
> Implicit conversion from datatype 'TEXT' to 'VARCHAR' is
> not allowed.  Use the CONVERT function to run this query.
>
> ( only the C table has a char column. A and B are text
> columns )
>
> Any suggestion?
>
> Thanks in advance!
>
> ***PS: I'm on ASE 12.5, and perhaps I'll be moving it to
> ASE 15 in short  time.

Alternative would be 1)insert null in B.description then 2)
update B conditionally using plain update(varchar col from
C) OR (text column from A) using readtext and writetext;

Actually, the Text column from A will be empty the most of the times so 
B should be filled in with the char column from C.

I double checked datatypes: and A and B are text, while C is a char.

I tried by doing the insert without any conditional, first inserting 
directly from C and worked; then changed to take A directly and worked 
too. Seems that the condition gives some trouble to the compiler, IMO.

Oh, and yes, A,B, and C are used in the query's join.

Is there any workaround?

The only one I can think of is creating a process in the front 
end(PowerBuilder) to simulate this SP :s, but I'd rather to keep this in 
the database.


thanks!


El 10/12/2010 01:57 p.m., Carl Kayser escribi�:
> "Jose Manuel Espinoza"<jmespinoza@seicom.com.mx>  wrote in message
> news:4d02804a$1@forums-1-dub...
>> Hi,
>>
>> I'm having some trouble with this: I have a table that can save a text
>> column, and can be null. Also, have another table containing some of the
>> columns from the first, and this should take the Text column if not empty,
>> otherwise must take a char column from a third table, all of this within
>> the SP.
>>
>> If I try to do it:
>>
>> Create ...
>> Begin
>> .
>> .
>> .
>> Insert into B(id, description)
>> Select  A.id,
>> Case when DataLength(A.large_text)<  1  then
>>          C.describe_it
>> Else
>>          A.large_text
>>          End
>> Where id = @id
>> .
>> .
>> .
>> End
>>
>>
>> I get this error:
>> Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. Use
>> the CONVERT function to run this query.
>>
>> ( only the C table has a char column. A and B are text columns )
>>
>> Any suggestion?
>>
>> Thanks in advance!
>>
>> ***PS: I'm on ASE 12.5, and perhaps I'll be moving it to ASE 15 in short
>> time.
>
>
> Have you tried an explicit conversion (and some form of a join):
>
>   Insert into B (id, description)
>       select  A.id,
>                  case
>                        when DataLength (A.large_text)<  1   then  convert
> (text, C.describe_it)
>                        else
> A.large_text
>                  end
>        from A, C
>        where A.id = @id
>            and C.id = @id
>   .
>   .
>
> The error message does not seem to conform with the type descriptions
> provided.  You're trying to (sometimes) place a char column into a text
> column?  But the error message says the opposite.
>
>

This is a multi-part message in MIME format.
--------------050507010506020004090401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Using the convert() function (same thing Carl's already recommended) the query seems to work just fine.  (see attached)

NOTE:  I'm on ASE 15.0.3; I don't have immediate access to a 12.5 dataserver so I'm *assuming* this will work on ASE 
12.5, too.

-----------

If you still can't get this to work then you really need to provide a *complete* test case that reproduces the issue.

So far you haven't provided the actual table definitions, any sample data, or any complete/valid SQL statements.

It's kinda hard to provide debugging suggestions without knowing *exactly* what we're trying to debug.

Jose Manuel Espinoza wrote:
> 
> Actually, the Text column from A will be empty the most of the times so 
> B should be filled in with the char column from C.
> 
> I double checked datatypes: and A and B are text, while C is a char.
> 
> I tried by doing the insert without any conditional, first inserting 
> directly from C and worked; then changed to take A directly and worked 
> too. Seems that the condition gives some trouble to the compiler, IMO.
> 
> Oh, and yes, A,B, and C are used in the query's join.
> 
> Is there any workaround?
> 
> The only one I can think of is creating a process in the front 
> end(PowerBuilder) to simulate this SP :s, but I'd rather to keep this in 
> the database.
> 
> 
> thanks!
> 
> 
> El 10/12/2010 01:57 p.m., Carl Kayser escribi�:
>> "Jose Manuel Espinoza"<jmespinoza@seicom.com.mx>  wrote in message
>> news:4d02804a$1@forums-1-dub...
>>> Hi,
>>>
>>> I'm having some trouble with this: I have a table that can save a text
>>> column, and can be null. Also, have another table containing some of the
>>> columns from the first, and this should take the Text column if not 
>>> empty,
>>> otherwise must take a char column from a third table, all of this within
>>> the SP.
>>>
>>> If I try to do it:
>>>
>>> Create ...
>>> Begin
>>> .
>>> .
>>> .
>>> Insert into B(id, description)
>>> Select  A.id,
>>> Case when DataLength(A.large_text)<  1  then
>>>          C.describe_it
>>> Else
>>>          A.large_text
>>>          End
>>> Where id = @id
>>> .
>>> .
>>> .
>>> End
>>>
>>>
>>> I get this error:
>>> Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. 
>>> Use
>>> the CONVERT function to run this query.
>>>
>>> ( only the C table has a char column. A and B are text columns )
>>>
>>> Any suggestion?
>>>
>>> Thanks in advance!
>>>
>>> ***PS: I'm on ASE 12.5, and perhaps I'll be moving it to ASE 15 in short
>>> time.
>>
>>
>> Have you tried an explicit conversion (and some form of a join):
>>
>>   Insert into B (id, description)
>>       select  A.id,
>>                  case
>>                        when DataLength (A.large_text)<  1   then  convert
>> (text, C.describe_it)
>>                        else
>> A.large_text
>>                  end
>>        from A, C
>>        where A.id = @id
>>            and C.id = @id
>>   .
>>   .
>>
>> The error message does not seem to conform with the type descriptions
>> provided.  You're trying to (sometimes) place a char column into a text
>> column?  But the error message says the opposite.
>>
>>
> 

--------------050507010506020004090401
Content-Type: text/plain;
 name="copy.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="copy.txt"


create table A ( id int, large_text  text null)
create table B ( id int, description text)
create table C ( id int, describe_it varchar(100))
go

insert A values (1,null)
insert A values (2,'this is a piece of text')
insert C values (1,'vc 1')
insert C values (2,'vc 2')
go


select * from A
select * from B
select * from C
go



 id          large_text
 ----------- ------------------------------------
           1 NULL
           2 this is a piece of text


 id          description
 ----------- ------------------------------------


 id          describe_it
 ----------- ------------------------------------
           1 vc 1
           2 vc 2





create proc add_to_B
@id int
as
insert B
select  A.id,
        case    when datalength(A.large_text) < 1
                then convert(text,C.describe_it)
                else A.large_text
        end
from    A, C
where   A.id = @id
and     C.id = @id
and     A.id = C.id
go

exec add_to_B 1
go
exec add_to_B 2
go



select * from B
go

 id          description
 ----------- ------------------------------------
           1 vc 1
           2 this is a piece of text


--------------050507010506020004090401--

Thanks for your idea...

I did it like you suggested, just with normal Update:

after inserting using C column as default:

Insert into B(id, description)
  Select  A.id, C.describe_it
  Where A.other = C.other
	and A.id = @id

Then do a normal update:

Update B
Set description = A.large_text
from A
Where A.id = B.id
    and DataLength( A.large_text ) > 1
    and B.id = @id

This works, but I have a doubt: how many chars can support this 
statement? Frankly, I haven't used readtext/writetext before, and after 
reading a few seems a little bit more complicated than this simple 
update :s

Or maybe is this friday's madness? :p

Thanks again!


El 10/12/2010 02:44 p.m., jobless escribi�:
> Alternative would be 1)insert null in B.description then 2)
> update B conditionally using plain update(varchar col from
> C) OR (text column from A) using readtext and writetext;

Mark, I just tested by creating my tables and SP in 15.0.2 and it worked 
fine.

However, when I do the convert on 12.5 shows this:

Operand type clash: TEXT is incompatible with TEXT


I thought it was because I have the ASE 15 in my lap, and the database 
is 12.5 so I went to the server and wrote the SP in SQL Advantage. Same 
error.

Well, at least now I know that when I move to 15 it will work  :p


Thanks for your help!


El 10/12/2010 04:52 p.m., Mark A. Parsons escribi�:
> Using the convert() function (same thing Carl's already recommended) the
> query seems to work just fine. (see attached)
>
> NOTE: I'm on ASE 15.0.3; I don't have immediate access to a 12.5
> dataserver so I'm *assuming* this will work on ASE 12.5, too.
>
> -----------
>
> If you still can't get this to work then you really need to provide a
> *complete* test case that reproduces the issue.
>
> So far you haven't provided the actual table definitions, any sample
> data, or any complete/valid SQL statements.
>
> It's kinda hard to provide debugging suggestions without knowing
> *exactly* what we're trying to debug.
>
> Jose Manuel Espinoza wrote:
>>
>> Actually, the Text column from A will be empty the most of the times
>> so B should be filled in with the char column from C.
>>
>> I double checked datatypes: and A and B are text, while C is a char.
>>
>> I tried by doing the insert without any conditional, first inserting
>> directly from C and worked; then changed to take A directly and worked
>> too. Seems that the condition gives some trouble to the compiler, IMO.
>>
>> Oh, and yes, A,B, and C are used in the query's join.
>>
>> Is there any workaround?
>>
>> The only one I can think of is creating a process in the front
>> end(PowerBuilder) to simulate this SP :s, but I'd rather to keep this
>> in the database.
>>
>>
>> thanks!
>>
>>
>> El 10/12/2010 01:57 p.m., Carl Kayser escribi�:
>>> "Jose Manuel Espinoza"<jmespinoza@seicom.com.mx> wrote in message
>>> news:4d02804a$1@forums-1-dub...
>>>> Hi,
>>>>
>>>> I'm having some trouble with this: I have a table that can save a text
>>>> column, and can be null. Also, have another table containing some of
>>>> the
>>>> columns from the first, and this should take the Text column if not
>>>> empty,
>>>> otherwise must take a char column from a third table, all of this
>>>> within
>>>> the SP.
>>>>
>>>> If I try to do it:
>>>>
>>>> Create ...
>>>> Begin
>>>> .
>>>> .
>>>> .
>>>> Insert into B(id, description)
>>>> Select A.id,
>>>> Case when DataLength(A.large_text)< 1 then
>>>> C.describe_it
>>>> Else
>>>> A.large_text
>>>> End
>>>> Where id = @id
>>>> .
>>>> .
>>>> .
>>>> End
>>>>
>>>>
>>>> I get this error:
>>>> Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not
>>>> allowed. Use
>>>> the CONVERT function to run this query.
>>>>
>>>> ( only the C table has a char column. A and B are text columns )
>>>>
>>>> Any suggestion?
>>>>
>>>> Thanks in advance!
>>>>
>>>> ***PS: I'm on ASE 12.5, and perhaps I'll be moving it to ASE 15 in
>>>> short
>>>> time.
>>>
>>>
>>> Have you tried an explicit conversion (and some form of a join):
>>>
>>> Insert into B (id, description)
>>> select A.id,
>>> case
>>> when DataLength (A.large_text)< 1 then convert
>>> (text, C.describe_it)
>>> else
>>> A.large_text
>>> end
>>> from A, C
>>> where A.id = @id
>>> and C.id = @id
>>> .
>>> .
>>>
>>> The error message does not seem to conform with the type descriptions
>>> provided. You're trying to (sometimes) place a char column into a text
>>> column? But the error message says the opposite.
>>>
>>>
>>

You might not need readtext/writetext given the update you
listed did not error; Your update is good, there wont be any
char limitation as the datatypes are same;

readtext/writetext are fun depending on the requirement(not
straight forward though, its been over 7 years since i
*actively coded* and moved on to dba stuff)

> Thanks for your idea...
>
> I did it like you suggested, just with normal Update:
>
> after inserting using C column as default:
>
> Insert into B(id, description)
>   Select  A.id, C.describe_it
>   Where A.other =3d C.other
>     and A.id =3d @id
>
> Then do a normal update:
>
> Update B
> Set description =3d A.large_text
> from A
> Where A.id =3d B.id
>     and DataLength( A.large_text ) > 1
>     and B.id =3d @id
>
> This works, but I have a doubt: how many chars can support
> this  statement? Frankly, I haven't used
> readtext/writetext before, and after  reading a few seems
> a little bit more complicated than this simple  update :s
>
> Or maybe is this friday's madness? :p
>
> Thanks again!
>
>
> El 10/12/2010 02:44 p.m., jobless escribi=f3:
> > Alternative would be 1)insert null in B.description then
> > 2) update B conditionally using plain update(varchar col
> > from C) OR (text column from A) using readtext and
> writetext;
>