[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

sql query help

I need help writing a sql query.  This query works but there are 6 different divisions, however division 17 employees need to use a another division's address.  the costcenter column represents which division that needs to be used instead of the 17 divison.  How can I bascially make a case statement and then use that value other places in the same query.  The NewDiv says it cant be found.


,case
when hr.costcenter = '17-11F' then '011'
when hr.costcenter = '17-12F' then '012'
when hr.costcenter = '17-13F' then '013'
when hr.costcenter = '17-14F' then '014'
when hr.costcenter = '17-16F' then '016'
else 99 end as NewDiv
 
From humres as hr  
--
left join cicmpy ON NewDiv = cicmpy.administration and cicmpy.cmp_type = 'D'
--
Order by hr.res_id


 

Then change out this line as
left join cicmpy ON hr.comp = cicmpy.administration and cicmpy.cmp_type = 'D'
Select  
hr.res_id  
,hr.fullname   
,hr.usr_id  
,jt.descr50  
,hr.comp  
,hr.job_title  
,hr.loc  
, cicmpy.cmp_fadd1 as address 
, cicmpy.cmp_tel as phone 
, cicmpy.cmp_fax as fax 
, cicmpy.cmp_fcity COLLATE DATABASE_DEFAULT+ ', ' + cicmpy.statecode COLLATE DATABASE_DEFAULT+ ' ' + cicmpy.cmp_fpc COLLATE DATABASE_DEFAULT as citystate 
,hr.ldatindienst  
,hr.emp_stat  
,hr.costcenter  
,a.itemcode  
,i.userYesNo_01  
,a.enddate 

From humres as hr  
left join (absences a inner join items i on i.itemcode = a.itemcode and i.userYesNo_01 = 1) on hr.res_id = a.empid and a.type = 86  
left join hrjbtl as jt  on hr.job_title = jt.job_title  
-- 
left join cicmpy ON hr.comp= cicmpy.administration and cicmpy.cmp_type = 'D' 
where usr_id = 'T0798'
-- 
Order by hr.res_id

Open in new window

0
red_75116
Asked:
red_75116
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT res_id,fullname , usr_id, descr50,comp , job_title, loc, address ,phone , fax, citystate,
ldatindienst , emp_stat,costcenter , itemcode,userYesNo_01 ,enddate , NewDiv
(
Select  
hr.res_id  
,hr.fullname  
,hr.usr_id  
,jt.descr50  
,hr.comp  
,hr.job_title  
,hr.loc  
, cicmpy.cmp_fadd1 as address
, cicmpy.cmp_tel as phone
, cicmpy.cmp_fax as fax
, cicmpy.cmp_fcity COLLATE DATABASE_DEFAULT+ ', ' + cicmpy.statecode COLLATE DATABASE_DEFAULT+ ' ' + cicmpy.cmp_fpc COLLATE DATABASE_DEFAULT as citystate
,hr.ldatindienst  
,hr.emp_stat  
,hr.costcenter  
,a.itemcode  
,i.userYesNo_01  
,a.enddate
,case when hr.costcenter = '17-11F' then '011'
when hr.costcenter = '17-12F' then '012'
when hr.costcenter = '17-13F' then '013'
when hr.costcenter = '17-14F' then '014'
when hr.costcenter = '17-16F' then '016'
else 99 end as NewDiv
From humres as hr  
left join (absences a inner join items i on i.itemcode = a.itemcode and i.userYesNo_01 = 1) on hr.res_id = a.empid and a.type = 86  
left join hrjbtl as jt  on hr.job_title = jt.job_title  
--
left join cicmpy ON hr.comp= cicmpy.administration and cicmpy.cmp_type = 'D'
where usr_id = 'T0798'
--
)A
WHERE NewDiv = -----------
Order by res_id
0
 
lofCommented:
you may use subquery in place of a table like in the following example
So in your query you use virtual table EnhencedHumres which contains additional, precomputed fields
select 
	*
from (
	select 
		* 
		,case 
		when hr.costcenter = '17-11F' then '011'
		when hr.costcenter = '17-12F' then '012'
		when hr.costcenter = '17-13F' then '013'
		when hr.costcenter = '17-14F' then '014'
		when hr.costcenter = '17-16F' then '016'
		else 99 end as NewDiv 
	From humres
) as EnhencedHumres
left join cicmpy ON EnhencedHumres.NewDiv = cicmpy.administration and cicmpy.cmp_type = 'D'

Open in new window

0
 
red_75116Author Commented:
Neither of these suggests work.
0
 
lofCommented:
Mine was only example of how to achieve your results.

Now attached is your original query modified to accommodate my earlier suggestion

in the first SELECT list you can now use hr.NewDiv
Select  
	hr.res_id  
	,hr.fullname   
	,hr.usr_id  
	,jt.descr50  
	,hr.comp  
	,hr.job_title  
	,hr.loc  
	, cicmpy.cmp_fadd1 as address 
	, cicmpy.cmp_tel as phone 
	, cicmpy.cmp_fax as fax 
	, cicmpy.cmp_fcity COLLATE DATABASE_DEFAULT+ ', ' + cicmpy.statecode COLLATE DATABASE_DEFAULT+ ' ' + cicmpy.cmp_fpc COLLATE DATABASE_DEFAULT as citystate 
	,hr.ldatindienst  
	,hr.emp_stat  
	,hr.NewDiv --hr.costcenter  
	,a.itemcode  
	,i.userYesNo_01  
	,a.enddate 
From 
(
	select *
		,case when costcenter = '17-11F' then '011'
                when costcenter = '17-12F' then '012'
                when costcenter = '17-13F' then '013'
                when costcenter = '17-14F' then '014'
                when costcenter = '17-16F' then '016'
                else 99 end as NewDiv 
	from humres
) as hr  
left join (absences a inner join items i on i.itemcode = a.itemcode and i.userYesNo_01 = 1) on hr.res_id = a.empid and a.type = 86  
left join hrjbtl as jt  on hr.job_title = jt.job_title  
-- 
left join cicmpy ON hr.comp= cicmpy.administration and cicmpy.cmp_type = 'D' 
where usr_id = 'T0798'
-- 
Order by hr.res_id

Open in new window

0
 
red_75116Author Commented:
I swapped this line out and it gave me the proper results.

left join cicmpy ON hr.NewDiv= cicmpy.administration and cicmpy.cmp_type = 'D'  

I see now how you combined the newdiv and the humres into on.  Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now