SAP Knowledge Base Article - Preview

3548890 - Unexpected behavior when enabling "streamlined dynamic SQL"- SAP ASE

Symptom

  • if @a is null will be raised Error 403 if @a is a text tyep.

    1> declare @a text
    2> set @a=null
    3> if @a is null
    4>   select 1
    5> else
    6>   select -1
    7> go
    Msg 403, Level 16, State 2:
    Server 'BK9', Line 3:
    Invalid operator for datatype op: is null type: TEXT.

  • But when enabling "streamlined dynamic SQL", it's not raised Error 403 and return unexpected behavior when assigning a return value of case when.

    1.if is null

    1> declare @a text
    2> set @a=null
    3> if @a is null
    4>   select 1
    5> else
    6>   select -1
    7> go
    (1 row affected)

     -----------
               1

    (1 row affected)
    1>


    2.case when : refer to "Reproducing the Issue" for detail repro steps

    1> declare @t1 text, @t2 text, @t text
    2> select @t = case when t2.id is null then t1.t1 else t2.t2 end, @t1=t1.t1,@t2=t2.t2
    3> from #table1 t1
    4> left outer join #table2 t2 on t2.id = t1.id
    5>
    6> if @t1 is null
    7> select '@t1 is null'
    8> else
    9> select '@t1 is not null'
    10>
    11>
    12> if @t2 is null
    13> select '@t2 is null'
    14> else
    15> select '@t2 is not null'
    16>
    17> if @t is null
    18> select '@t is null'
    19> else
    20> select '@t is not null'
    21>
    22> go
    (1 row affected)

     -----------
     @t1 is null

    (1 row affected)

     -----------
     @t2 is null

    (1 row affected)

     --------------
     @t is not null            <<<wrong return value

    (1 row affected)
    1>




Read more...

Environment

SAP Adaptive Server Enterprise (ASE)

Product

Sybase SDK all versions

Keywords

text datatype, case when, unexpected behavior, Msg 403, CR830181, CR#830181, 830181 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.