asked on

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

Jim Horn

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.

Anoo S Pillai

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

PortletPaul

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

guinnie

ASKER

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.