Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 ISNULL QUESTION Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 26, 2008 4:39 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 04, 2013 9:25 AM Points: 285, Visits: 1,027
 I am just reviewing a script at workwanted to know the following line meant.WHERE NOT ISNULL(OP2.Contract_Line_Number,'') = 'NONCHARGE'I have never seen NOT ISNULL. used. Can anyone explain what is happening here
Post #608961
 Posted Wednesday, November 26, 2008 4:48 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, February 24, 2010 4:10 AM Points: 1,553, Visits: 2,232
 basically if OP2.Contract_Line_Number != 'NOCHANGE'Then you will get results ----------------------------------------------Try to learn something about everything and everything about something. - Thomas Henry Huxley Posting Best PracticesNumbers / Tally TablesSQL-4-Life
Post #608962
 Posted Wednesday, November 26, 2008 4:55 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 04, 2013 9:25 AM Points: 285, Visits: 1,027
 could you basically explain the logic behind using NOT ISNULL to acheive results.
Post #608969
 Posted Wednesday, November 26, 2008 5:15 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, February 24, 2010 4:10 AM Points: 1,553, Visits: 2,232
 Ok the first thing to remember is that they both perform different functionsLets start with ISNULL statementWHERE ISNULL(Col1,'') = 'NOCHANGE'The reason you use is null is so that you don't get thieWHERE NULL = 'NOCHANGE'instead you will get:WHERE '' = 'NOCHANGE'So in your example you possible clause are as follows:WHERE '' = 'NOCHANGE' (this is FALSE)WHERE 'anything' = 'NOCHANGE' (this is FALSE)WHERE 'NOCHANGE' = 'NOCHANGE' (this is TRUE)Now using the NOT operator basicallt says do the oppositeso NOT TRUE = FALSEand NOT FALSE = TRUEDoes that make sense to you? ----------------------------------------------Try to learn something about everything and everything about something. - Thomas Henry Huxley Posting Best PracticesNumbers / Tally TablesSQL-4-Life
Post #608981
 Posted Wednesday, November 26, 2008 5:16 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, March 15, 2012 1:12 AM Points: 152, Visits: 94
 It is same logic asSELECT * from #tWHERE NOT ISNULL(id,0) = 1orSELECT * from #tWHERE ISNULL(id,0) != 1Both the queries will produce same result, since in first query = has greater proirity than not it will check "ISNULL(id,0) = 1" and apply not to that .. but later one is clear in understanding.
Post #608983
 Posted Wednesday, November 26, 2008 6:44 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 04, 2013 9:25 AM Points: 285, Visits: 1,027
 cheers. That makes sense!!
Post #609031
 Posted Friday, July 15, 2011 5:49 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 2:29 AM Points: 117, Visits: 270
 HI,I am struck in the following query and want to know how to improve its performance for 2 mn records.SELECT [1].areabuilding,[10].yearvalue,[2].bldgscheme as bldgscheme_dvt,[2].bldgclass as bldgclass_dvt,[2].bldgclasstier1 as bldgclass_dvttier1,[2].bldgdesc as bldgdescdvt,[3].bldgscheme,[3].bldgclass,[3].bldgclasstier1,[3].bldgdesc,[4].cflag,[4].cflagcode,[4].bldgclassflag,[4].numstoriesflag,[4].yearvalueflag,[4].occtypeflag,[4].areabuildingflag,[4].bldgclasstier1flag,[4].occgrpflag,[4].isEligibleAcrossSource,[4].isEligibleInSource,[5].flagname as flagFilter,[6].flagname as insflag,[7].flagname as Singleoccupancyflag,[8].numstories,[9].occscheme2,[9].occtype2,[9].occdesc,[9].occintmd,[9].occgrp,[9].occscheme1,[9].occtype1,[9].occgroup,A.factid,A.basefactid,A.linkid,A.grpaddressid,A.inputid FROM migration_db.DBO.fact_process7_10 a INNER JOIN migration_db.dbo.dimareabuilding [1] ON isnull(a.areabuildingid,1)=[1].areabuildingID INNER JOIN migration_db.dbo.dimbldgclass [2] ON isnull(a.bldgclassdvtid,1)=[2].bldgclassID INNER JOIN migration_db.dbo.dimbldgclass [3] ON isnull(a.bldgclassid,1)=[3].bldgclassID INNER JOIN migration_db.dbo.dimcflag [4] ON isnull(a.cflagid,1)=[4].cflagID INNER JOIN migration_db.dbo.dimflag [5] ON isnull(a.flagid,1)=[5].flagID INNER JOIN migration_db.dbo.dimflag [6] ON isnull(a.Insflagid,1)=[6].flagID INNER JOIN migration_db.dbo.dimflag [7] ON isnull(a.Singleoccupancyid,1)=[7].flagID INNER JOIN migration_db.dbo.dimnumstories [8] ON isnull(a.numstoriesid,1)=[8].numstoriesID INNER JOIN migration_db.dbo.dimoccupancy [9] ON isnull(a.occupancyid,1)=[9].occupancyID INNER JOIN migration_db.dbo.dimyear [10] ON isnull(a.yearid,1)=[10].yearIDThe following are null value counts :Insflagid --- 1532677Singleoccupancyid --732556cflagid --1223643flagid --1933134Please suggest.Regards,Ankit ______________________________________________________________________Ankit MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.You are already naked. There is no reason not to follow your heart.”
Post #1142399
 Posted Friday, July 15, 2011 6:59 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:09 AM Points: 6,870, Visits: 12,508