09 Jul 2012 @ 10:53 PM 

Improper handling of NULL values is a common cause of application failure. This is true for applications built on both Oracle and Sybase.

We’ll talk about how to avoid such problems when building where clauses in your SQLs.

Firstly, a NULL value is a value that is not known. A NULL value cannot be compared correctly with other values- even with another NULL value.

When building your where clauses, use the IS NULL and the IS NOT NULL statements. The following example can be used to compare columns between 2 tables –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(T1.COL1<>T2.COL1) or (T1.COL1 is null and T2.COL1 is not null) or
(T1.COL1 is not null and T2.COL1 is null)

Another solution is to use the DECODE statement. To perform the above operation, consider –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(decode(T1.COL1,T2.COL1,1,0)=0)

Depending on the needs of your application, you may need to handle NULL values in different ways. NVL can be used in select statements to replace returned NULLs with a user defined expression.

Posted By: Kevin
Last Edit: 09 Jul 2012 @ 10:57 PM

EmailPermalink
Tags
Tags: ,
Categories: basics, Snippets


 

Responses to this post » (None)

 
Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

 


 Last 50 Posts
Change Theme...
  • Users » 66
  • Posts/Pages » 25
  • Comments » 4
Change Theme...
  • VoidVoid « Default
  • LifeLife
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LightLight