CODEWITHSUNDEEP
vb6sybase
niru dyogi
niru dyogi

Reputation: 639

IsNull in sybase where clause

I am using vb6 front end and sybase as backend. Can I use IsNull in where clause OR I must use = NULL

SQLStr = "select distinct thrd_pty.id_thrd_pty, thrd_pty.name_thrd_pty, thrd_pty.nbr_tax_idtn " _
           & "from thrd_pty, cntct_rltn, cntct " _
           & "where cntct.id_cntct = '" & cntct(ColNum, CurThrdPty) & "' and cntct_rltn.id_super = cntct.id_cntct and cntct_rltn.name_super = 'cntct' and thrd_pty.id_thrd_pty = cntct_rltn.id_thrd_pty and cntct_rltn.dt_eff_end IsNull "

Upvotes: 0

Views: 8290

Answers (2)

Racso
Racso

Reputation: 2449

Use is null:

cntct_rltn.dt_eff_end is null

More info, here

EDIT:

cQ = "update var1 = null ".(can be) .cQ = "update var1 is null" ????? – niru dyogi

If you are updating, you have to use the assignment operator =. You can use is null only when evaluating conditions (such as the one in the example you provided).

Upvotes: 1

DVK
DVK

Reputation: 129403

The usual syntax in Sybase is either AND cntct_rltn.dt_eff_end IS NULL (which is perfectly fine for your example) or ISNULL(cntct_rltn.dt_eff_end, 0) for the more complicated stuff where you want to replace null with a defined default value. But you can do any of the follownig in ASE 15:

Adaptive Server treats null values in different ways, depending on the operators that you use and the type of values you are comparing. In general, the result of comparing null values is UNKNOWN, since it is impossible to determine whether NULL is equal (or not equal) to a given value or to another NULL. The following cases return TRUE when expression is any column, variable or literal, or combination of these, which evaluates as NULL:

  • expression is null
  • expression = null
  • expression = @x where @x is a variable or parameter containing NULL. This exception facilitates writing stored procedures with null default parameters.
  • expression != n where n is a literal not containing NULL and expression evaluates to NULL. (source)

(uppercase is mine as a personal style preference)

As a side note, you can also use COALESCE() function to deal with NULL values, but in your use case it won't add anything useful.

Reference:

Upvotes: 1

Related Questions