Can I use an IF statement while creating a View?

[modglin]

I have 3 tables that I would like to use to create a view. I may not be able to bring the third table in and that would be okay, but it would be nice to have all the information in a view for another department to use. I want all employees that are active from the second table.

The first table (HRPERSNL) has the employee information – name, employee#, location#, jobcode, jobdate, jobtitle, active

The second table (NEMPHIST) has the courses that the employees have taken – employee#, jobcode, course, enrstat, endate

The third table (NEMPCERT) has the certification(s) the employees have completed – employee#, certcode, expdate, lastdate, required

I want to create a field based on the jobcode field that can be found in either the HRPERSNL or NEMPHIST tables and want to call it JobCertCode. In most cases the first 3 letters in the jobcode field is equal to the Certcode field from the 3rd table. With the exception (there’s always an exception) of Jobcode 220, 221, 222, and 223 all need to have the Certcode of 220.

The JobCertCode and the Employee# are the two fields that would join the information from the first two tables to the third. That is the reason I didn’t know if I would be able to bring that information into this view or not.

I used the code below to create the view using the first two tables, however, was not sure how to enter the if statement. IF SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) in (220,221,222,223) then 220 else SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3).

SELECT     dbo.hrpersnl.p_empno, dbo.hrpersnl.p_active, dbo.hrpersnl.p_fname, dbo.hrpersnl.p_lname, dbo.hrpersnl.p_jobcode, dbo.hrpersnl.p_jobdate, 
                      dbo.hrpersnl.p_jobtitle, dbo.hrpersnl.p_level3, dbo.hrpersnl.p_misc1, dbo.nemphist.certcode, dbo.nemphist.course, dbo.nemphist.enddate, 
                      dbo.nemphist.enrstat, SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) AS JobCertCode
FROM         dbo.hrpersnl RIGHT OUTER JOIN
                      dbo.nemphist ON dbo.hrpersnl.p_empno = dbo.nemphist.empno
WHERE     (dbo.hrpersnl.p_active = 'A') OR
                      (dbo.hrpersnl.p_active = 'L')

Ideally I would like to bring in the third table so I can pull the Date they were certified on a particular certification and when it expires. The employee# to employee# and the JobCertCode(the if statement) to CertCode would They need to take several courses to qualify for a certification and when the certification expires; they need to reschedule the courses again. There can be several records for an employee for a course if they have been with the company for awhile, or if they have failed it in the past and taken it again. The Training Department needs to know if they have taken a class or not, if it counts towards the certification and if they are certified or not. I will then be using this view to create a Crystal report that will give them the information in a format that they want and to hide the courses that have been taken in the past.

I have tried the IF statement is a few places within the code, however, I am definately doing something wrong there!
Thanks for any assistance!!

 

[monksnake]

Do a CASE statement on that column instead of an if statement.

SELECT     dbo.hrpersnl.p_empno, dbo.hrpersnl.p_active, dbo.hrpersnl.p_fname, dbo.hrpersnl.p_lname, dbo.hrpersnl.p_jobcode, dbo.hrpersnl.p_jobdate, 
                      dbo.hrpersnl.p_jobtitle, dbo.hrpersnl.p_level3, dbo.hrpersnl.p_misc1, dbo.nemphist.certcode, dbo.nemphist.course, dbo.nemphist.enddate, 
                      dbo.nemphist.enrstat, [!]CASE WHEN SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) IN (220,221,222,223) then 220 else SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) END[/!] AS JobCertCode
FROM         dbo.hrpersnl RIGHT OUTER JOIN
                      dbo.nemphist ON dbo.hrpersnl.p_empno = dbo.nemphist.empno
WHERE     (dbo.hrpersnl.p_active = 'A') OR
                      (dbo.hrpersnl.p_active = 'L') 


[monkey][snake] <.

 

[gmmastros]

Instead of...

IF SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) in (220,221,222,223) then 220 else SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3)

You'll want to use the case statement, like this...

[COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]SUBSTRING[/color](dbo.hrpersnl.p_jobcode, 1, 3) IN (220,221,222,223)
     [COLOR=blue]Then[/color] [COLOR=red]'220'[/color]
     [COLOR=blue]Else[/color] [COLOR=#FF00FF]SUBSTRING[/color](dbo.hrpersnl.p_jobcode, 1, 3)
     [COLOR=blue]End[/color]

Notice that I put apostrophes around the 220 (on the Then). I did this because the data types returned by each execution path of the case statement should match, and since the ELSE statement returns a string, the Then part should also return a string.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Then George to elaborate on the data types, wouldn't you also want to do this:

Case When SUBSTRING(dbo.hrpersnl.p_jobcode, 1, 3) IN ([!]'220','221','222','223'[/!])

<.

 

[gmmastros]

monk

Good catch.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[modglin]

Thanks Everyone!
I forget that SQL is case not if. Been using Access and Crystal and trying to learn SQL. I would have caught the fact that it needed to be ('220','221',etc) based on the Crystal reports, but good catch if someone else refers to this post!!!!
I created that view and then created a second view that links the 1st with the nempcert table using the newly created JobCertCode = dbo.nempcert.CertCode .
Was worried about performance, however, the crystal report doesn't take long to run from the second view.

Again, thanks for the quick responses!