Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ISNULL QUESTION Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 4:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:10 AM
Points: 285, Visits: 1,059
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 Practices
Numbers / Tally Tables

SQL-4-Life
Post #608962
Posted Wednesday, November 26, 2008 4:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:10 AM
Points: 285, Visits: 1,059
could you basically explain the logic behind using NOT ISNULL to acheive results.
Post #608969
Posted Wednesday, November 26, 2008 5:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 functions

Lets start with ISNULL statement

WHERE ISNULL(Col1,'') = 'NOCHANGE'

The reason you use is null is so that you don't get thie
WHERE 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 opposite
so NOT TRUE = FALSE
and NOT FALSE = TRUE

Does that make sense to you?


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #608981
Posted Wednesday, November 26, 2008 5:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 15, 2012 1:12 AM
Points: 152, Visits: 94
It is same logic as

SELECT * from #t
WHERE NOT ISNULL(id,0) = 1

or

SELECT * from #t
WHERE ISNULL(id,0) != 1

Both 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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:10 AM
Points: 285, Visits: 1,059
cheers. That makes sense!!
Post #609031
Posted Friday, July 15, 2011 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:05 AM
Points: 122, Visits: 292
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].yearID



The following are null value counts :
Insflagid --- 1532677
Singleoccupancyid --732556
cflagid --1223643
flagid --1933134

Please 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:19 AM
Points: 7,147, Visits: 13,211
Please don't hijack other threads (even more if those threads are almost three years old).

Open a new thread and post your question with more a more detailed description than just throwing the query at us. For a detailed explanation on how to post performance related questions please read and follow the advice given in the second link in my signature (don't just copy and paste the stuff you just posted. It won't help us help you....).




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1142469
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse