Case statement not working

Getting 2 rows. One is correct but the other has null value in org_unit that is incorrect.

Do not understand why getting null org_unit.

Should only be 1 row per employee.
test-data.xls
staff.sql
guinnieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Please post your T-SQL code in a code block and not with the File Attach button.  When I try to open the .sql file it returns an error. ask-question-buttons.jpg
Anoo S PillaiCommented:
The NULL in  org_unit is related to data and the conditions in CASE clause, Identify the values of the columns which does not fit into the conditions of CASE WHEN  ( those values are coming as NULL in org_unit ) , add one ELSE part in the CASE and identify the values which are coming under the ELSE .


 CASE 
      WHEN  CHROME_SCHOOLS.SCHOOL_CODE > ('809')
        and IPPS_EC_EMPLOYEE_POSITIONS_VW.LOCATION_CODE not in ('001','399','354')  
        and IPPS_EC_LOCATIONS_VW.MIDENT = CHROME_SCHOOLS.DEFAULT_SCHOOL_BSID 
          THEN 'Elementary/' + IPPS_EC_EMPLOYEE_POSITIONS_VW.EMP_GROUP_CODE +  '/' + CHROME_SCHOOLS.SCHOOL_CODE + '/2014-15'
      WHEN CHROME_SCHOOLS.SCHOOL_CODE < ('810')
        and IPPS_EC_EMPLOYEE_POSITIONS_VW.LOCATION_CODE not in ('001','399','354')  
        and IPPS_EC_LOCATIONS_VW.MIDENT = CHROME_SCHOOLS.DEFAULT_SCHOOL_BSID 
          THEN  'Secondary/' + IPPS_EC_EMPLOYEE_POSITIONS_VW.EMP_GROUP_CODE +  '/' + CHROME_SCHOOLS.SCHOOL_CODE  + '/2014-15'  
      WHEN IPPS_EC_EMPLOYEE_POSITIONS_VW.LOCATION_CODE  in ('001','399','354')
          THEN  'CEC/'  + IPPS_EC_EMPLOYEE_POSITIONS_VW.EMP_GROUP_CODE  + '/2014-15'

	  ELSE	CAST ( CHROME_SCHOOLS.SCHOOL_CODE AS VARCHAR(1000 ) )   + '/'
		CAST ( IPPS_EC_EMPLOYEE_POSITIONS_VW.LOCATION_CODE AS VARCHAR(1000) )   + '/'
		CAST ( IPPS_EC_LOCATIONS_VW.MIDENT AS VARCHAR(1000) )  + '/'
		CAST ( CHROME_SCHOOLS.DEFAULT_SCHOOL_BSID AS VARCHAR(1000)) 

             end  org_unit 

Open in new window



This could be attributed to a  CROSS JOIN "FROM CHROME_SCHOOLS,IPPS_EC_EMPLOYEE_VW" or any missing join condition or even due to unexpected data in table.

Only you could confirm :-)


Anoo
PortletPaulEE Topic AdvisorCommented:
NEVER mix "old fashioned" join syntax with ANSI standard join syntax

In your FROM cause you have this which is causing a Cartesian product (or "CROSS JOIN"):
FROM CHROME_SCHOOLS , IPPS_EC_EMPLOYEE_VW

What is the relationship between tables [CHROME_SCHOOLS] and [IPPS_EC_EMPLOYEE_VW]?

IF you actually intended to have a CROSS JOIN, then use "CROSS JOIN" like this:
FROM CHROME_SCHOOLS
CROSS JOIN IPPS_EC_EMPLOYEE_VW
INNER JOIN IPPS_EC_LOCATIONS_VW
      ON IPPS_EC_LOCATIONS_VW.LOCATION_CODE = IPPS_EC_EMPLOYEE_VW.HOME_LOCATION_CODE
INNER JOIN IPPS_EC_EMPLOYEE_POSITIONS_VW
      ON IPPS_EC_EMPLOYEE_POSITIONS_VW.EMPLOYEE_ID = IPPS_EC_EMPLOYEE_VW.EMPLOYEE_ID
      AND IPPS_EC_EMPLOYEE_POSITIONS_VW.EMP_GROUP_CODE = IPPS_EC_EMPLOYEE_VW.EMP_GROUP_CODE
      AND IPPS_EC_EMPLOYEE_POSITIONS_VW.LOCATION_CODE = IPPS_EC_EMPLOYEE_VW.HOME_LOCATION_CODE
      AND ISNULL(IPPS_EC_EMPLOYEE_POSITIONS_VW.POSITION_END_DATE, '20991231') >= GETDATE()

Open in new window


IF the cross join isn't what you intended join table [IPPS_EC_EMPLOYEE_VW] correctly into the query.
PortletPaulEE Topic AdvisorCommented:
by the way 1: while I really appreciate you providing some sample data there are 4 tables involved in your query, for us to run the query as a test we would need sample data PER TABLE

by the way 2: I suspect your use of "select distinct" is a "Hail Mary Distinct". If you have a couple of minutes this might be worth reading: Select Distinct is returning duplicates ... and look for "hail mary". There are 2 other URLs in that article to look at also

Some Simple SQL Rules to Live By (DISTINCT is *usually* bad)
Why I Hate DISTINCT

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
guinnieAuthor Commented:
There isn't a relationship between chrome_schools and ipps_ec_employee_vw.
Just needed to get school demographics from chrome_schools.
I item of interest, when I comment out all CASE statements I get the 2 rows I expected.
So as Anoo suggested, the case statements aren't doing what I think they should.

Good article Paul. Thank you for that information.
Sorry I didn't get back to everyone sooner, I had major upgrades to to today.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.