Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Use of "Set ANSINULL on" option in sybase ASE12.5

    Hi All,
    I am using sybase 12.5. I am trying to use the "Set Ansinull off" option for select's having not equal joins, but it is not working.
    Say you have a
    Table A ID Color
    1 Red
    2 Blue
    3 Null

    Table B ID Color
    1 RED
    2 Null
    3 Green

    CASE-1
    Now I set my ansinull option to off.
    1> set ansinull off
    2>go

    Select * from A where color=null returns..
    ID Color
    3 Null

    Select * from B where color=null returns..
    ID Color
    2 Null

    Select A.ID, A.color
    From A, B
    Where A.ID = B.ID
    and A.color != B.Color
    returns 0 rows affected

    CASE-2
    Now I set AnsiNull option on
    1> set ansinull on
    2>go

    Select * from A where color=null returns..
    returns 0 rows affected

    Select * from B where color=null returns..
    returns 0 rows affected

    Select A.ID, A.color
    From A, B
    Where A.ID = B.ID
    and A.color != B.Color
    returns 0 rows affected

    I am very clear with the results of single select in each CASE, but not able to understand the ASE behaviour in using JOINS, In both CASES(1&2) the joins did not result any rows, though "I" was expecting the following result in the Join condition of CASE-1
    Select A.ID, A.color
    From A, B
    Where A.ID = B.ID
    and A.color != B.Color
    ID Color
    2 Blue
    3 NULL

    But it did not happen at all.

    Instead if I use the same select using ISNULL, it works.
    Select A.ID, A.color
    From A, B
    Where A.ID = B.ID
    and ISNULL(A.color, ' ') != ISNULL(B.Color, ' ')
    ID Color
    2 Blue
    3 NULL

    1) Can I get the above result without using the Isnull option. (Other then creating default on colum Color"?
    2) Is there any global option where in we can set that null value will be taken as default depending on the datatype, e.g. for int -0, for varchar - ' ' (spaces) , for datetime - 1900 (some syabe default date)?
    3) How can I get "my" expected result from this select(as above)
    Select A.ID, A.color
    From A, B
    Where A.ID = B.ID
    and A.color != B.Color

    Actually, I have some 40 stored procedures having such kind of non-equality joins and none of the columns have Defaults. So any solution will save lot of modification time (if I choose using isnull)

    Thanks is advance for your suggestions

    Pranav

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Why are you not using the standard "WHERE color IS NULL"?
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •