November 26, 2008 at 4:39 am
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
November 26, 2008 at 4:48 am
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
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 26, 2008 at 4:55 am
could you basically explain the logic behind using NOT ISNULL to acheive results.
November 26, 2008 at 5:15 am
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
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 26, 2008 at 5:16 am
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.
November 26, 2008 at 6:44 am
cheers. That makes sense!!
July 15, 2011 at 5:49 am
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.”
July 15, 2011 at 6:59 am
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....).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply