On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, October 20, 2008

Nullif


The NullIf statement compares one value with another. If they match, it returns null, otherwise it returns the first. In this example, the NullIf, and the following Case statement get the same results.
Column Employee_Id  Heading Employee|Id
Column NullIf_JobId Heading NullIf|(E_Job_Id)
Column Case_JobID Heading Case|(E_Job_Id)
Column Equals Heading '='
Column E_Job_Id Heading E.|Job_id
Column J_Job_Id Heading J.|Job_id

Select E.Employee_Id,
E.Job_Id As E_Job_Id,
--------------------------------------------
Case When E.Job_Id = J.Job_Id
Then '='
Else ' '
End
As
Equals,
--------------------------------------------
J.Job_Id As J_Job_Id,
--------------------------------------------
Nullif(E.Job_Id, J.Job_Id) /* this is the same as the following case */
As
NullIf_JobId,

--------------------------------------------
Case When E.Job_Id = J.Job_Id /* this is the same as the nullif */
Then Null
Else E.Job_ID
End
As
Case_JobID

--------------------------------------------
From Employees E,
Job_History J
Where E.Employee_Id = J.Employee_Id
Order By E.Employee_Id;

No comments:

Post a Comment