I have a query which is giving me error as
Column 'designation' does not belong to table Table.
Here is my query:-
Select upper(ra1user.first_name + ' ' + ra1user.last_name) RA1_Name, " +
"ra1user.email As RA1_Email_ID, upper(ra2user.first_name + ' ' + ra2user.last_name) [RA2 Name], " +
"ra2user.email As RA2_Email_ID, upper(Emp_name) empName, " +
"um.email As empEmail_ID, upper(empDesignation) empDesignation, " +
"(select CONVERT(VARCHAR(10),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),101)) " +
"as empConfirmation_Date , " +
"convert(varchar(10), em.date_of_Joining, 103) as date_of_Joining " +
"from emp_mst em " +
"join user_mst um on um.employee_mkey = em.mkey " +
"left join user_mst ra1user on ra1user.employee_mkey = em.reporting_to " +
"left join user_mst ra2user on ra2user.employee_mkey = em.reporting_to2 " +
"join type_mst_a desig on em.new_design_mkey=desig.master_mkey " +
"where em.emp_card_no="
VIEW
ALTER view [dbo].[XXACL_ERP_EMP_Confirmation_View] as select
*
from ( select distinct comp.company_name Company_Name, case when e.comp_mkey!=e.on_Deput_comp_mkey then Dcomp.company_name else '' end Deputed_Company_Name , 'NA' [Company Code], e.emp_card_no [Emp_Card_No], 'NA' Title, e.emp_name [Employee_Name], usr.first_name [First_Name], replace(d1.type_desc, '', '') Designation, grade.type_desc Grade , replace(isnull(b1.type_desc, '--NA--'), '', '') as 'Department' , isnull(b2.type_desc, '--NA--') as 'Sub Department' , isnull(hub.type_desc, '--NA--') as Hub_Name , 'NA' Location , 'NA' City , 'NA' [State Name] , case when e.emp_type='E' then 'HO-Employee' when e.emp_type='T' then 'Temporary'
when e.emp_type='C' then 'Consultant'
when e.emp_type='S' then 'Site-Employee' else 'Temporary-OutSource' end [Emp_Type] , convert(datetime, e.date_of_joining, 103) Date_of_Joining , convert(datetime, e.dt_of_birth, 103) DOB , ctc.final_ctc_amt [Fixed CTC P.A] , ctc.variance_amt [Variable CTC P.A] , ctc.Anu_ctc_amt Total_CTC_PA , dbo.fn_spellNumber(cast(cast(ctc.Anu_ctc_amt as numeric(18, 0)) as varchar), 'UK', '0') [CTC_in_Word] , convert(datetime, ctc.pay_slip_eff_date, 103) [Last Increment Date] , case when (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no
and ra1_mkey is not null)>0 then (select top 1 emp_mst.emp_name as 'RA1 Name' from emp_mst, p_emp_confirmation_hdr
where p_emp_confirmation_hdr.ra1_mkey=emp_mst.mkey and p_emp_confirmation_hdr.emp_card_no=e.emp_card_no)
else s1.emp_name end as 'RA1 Name', case when (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no
and ra2_mkey is not null)>0 then (select top 1 emp_mst.emp_name as 'RA2 Name' from emp_mst, p_emp_confirmation_hdr
where p_emp_confirmation_hdr.ra2_mkey=emp_mst.mkey and p_emp_confirmation_hdr.emp_card_no=e.emp_card_no)
else s2.emp_name end as 'RA2 Name', --, s1.emp_name as 'RA1 Name', --isnull(s2.emp_name, '--NA--') as 'RA2 Name' 'NA' 'Functional Head Name' , 'NA' [Probation Period], CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then (select top 1 confirmation_date from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)
else CONVERT(VARCHAR(25), dateadd(mm, 1, DATEADD(dd, -(DAY(getdate())-1), getdate())), 103) end AS Confirmation_Date --case when convert(varchar, e.date_of_confirmation, 103)='01/01/1900' then null else --convert(datetime, e.date_of_confirmation, 103) end Confirmation_Date , 'NA' 'Confirmation Remarks' , case when e.status='A' then 'Active'
when e.status='S' then 'Active'
when e.status='F' then 'Full and Final'
when e.status='R' then 'Resign' else 'NA' end [Employee Status] , 'NA' [Notice Period Term] , case when e.resig_date='1900-01-01 00:00:00.000' then null else convert(datetime, e.resig_date, 103) end [Resigned Date] , 'NA' 'Resignation Received HR' , case when e.dt_of_leave='1900-01-01 00:00:00.000' then null
else convert(datetime, e.dt_of_leave, 103) end [Last Working Date] , e.Reason_of_resignation [Reason for Leaving] , 'NA' [Sourced Through] , 'NA' 'Replacement For' , case when e.marital_status='M' then 'Married' when e.marital_status='U' then 'UnMarried' else '--NA--'
end [Marital Status] , 'NA' [Qualification Category] , dbo.StringConcat(( isnull((select isnull(education, '') +'--'+ isnull(edu.remarks , '')
from p_Emp_Education edu
join p_education_mst edumst on edu.education_mkey=edumst.mkey
where edu.mkey=e.mkey
and Entry_Srno=1
order by entry_srno for xml raw, elements), 'NA'))) 'Highest Qualification' , 'NA' Graduation , 'NA'[GradYear Passed] , 'NA' [Post Graduation] , 'NA' [Post Grad Year Passed] , ad.add_1 Address_Line1 , ad.add_2 Address_Line2 , c.city_name as Address_City , ad.pincode as 'PinCode' , stmst.State_name Address_State , Residence_No 'Telephone No.' , e.mobile_no 'Mobile No.' , e.email_id_official [EmailId Official] , e.email_id_personal 'EmailId Personal' , e.pan_no 'PAN CARD' , isnull(e.pf_no, '--NA--') as 'PF_No' , isnull(e.ESIC_No, '--NA--') as 'ESIC_No' , isnull(e.passport_no, '--NA--') as 'Passport_No' , e.Work_Exp as 'Previous Experience Year' , fam.Member_Name as 'Family Details 1 Name' , r.relation 'Family Details 1 Relation' , isnull(convert(varchar, fam.DOB, 103), 'null') as 'Family Details 1 DOB' , fam1.Member_Name as 'Family Details 2 Name' , r1.relation 'Family Details 2 Relation' , isnull(convert(varchar, fam1.DOB, 103), 'null') as 'Family Details 2 DOB' , fam2.Member_Name as 'Family Details 3 Name' , r2.relation 'Family Details 3 Relation' , isnull(convert(varchar, fam2.DOB, 103), 'null') as 'Family Details 3 DOB' , fam3.Member_Name as 'Family Details 4 Name' , r3.relation 'Family Details 4 Relation' , isnull(convert(varchar, fam3.DOB, 103), 'null') as 'Family Details 4 DOB' , e.bld_grp 'Blood Group' --, bld.type_desc blood_type , 'NA' 'Background Verif Doc' --, case when Medical_Fit='Y' then 'Yes' else 'No' end Medical_Fitness , 'NA' 'Medical Fitness' , 'NA' 'Medical Fitness Remark' , 'NA' 'Graphology Test' , 'NA' 'Bank A/C status' , bank.bank_name [Bank Name] , e.citybank_acc_no 'Bank Account Number' , case when e.on_Deput_comp_mkey>0 then e.on_Deput_comp_mkey else e.comp_mkey
end comp_mkey , e.new_design_mkey , e.New_Dept_mkey , e.ctc_hdr_mkey , e.status emp_status1 , e.mkey emp_mkey , e.subdept_mkey , e.department_mkey , e.mkey, CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then (select top 1 confirmation_date as 'confirmation_Due_On' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else CONVERT(VARCHAR(25), dateadd(mm, 1, DATEADD(dd, -(DAY(getdate())-1), getdate())), 103) end as 'confirmation_Due_On' -- e.confirmation_due_on , CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then (select top 1 PROBATION_PERIOD as 'PROBATION_PERIOD' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else (select datediff(d, e.dt_of_join, CONVERT(VARCHAR(25), dateadd(mm, 1, DATEADD(dd, -(DAY(getdate())-1), getdate())), 101))/30) END as Probation_Period --, e.Probation_Period , e.wages_type , e.reporting_to , e.reporting_to2 , CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then (select top 1 LEAVE_TAKEN_PROB_PER as 'Absent_Count' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else (select erplive.dbo.AbsentCount_view(e.emp_card_no)) end as Absent_count --, (select count() from xxacl_ERP_AB_PL_Count_V where emp_card_no = e.emp_card_no) Absent_count , case
when (select count() from xxacl_ERP_AB_PL_Count_View where emp_card_no= e.emp_card_no)>7 then dateadd(mm, e.probation_period+1, e.Date_of_Joining)
when day(e.Date_of_Joining)>15 then dateadd(mm, e.probation_period+1, e.Date_of_Joining)
else e.date_of_confirmation
end Confirmation_Date_New , case
when (select isnull(sum(total_day), 0) from xxacl_ERP_AB_PL_Count_View where emp_card_no= e.emp_card_no)>7 --then 'DOC Exteded By 1 month. Reason:- Taken leave='+ Convert(varchar, (select SUM(TOTAL_DAY) from xxacl_ERP_AB_PL_Count_View where emp_card_no = e.emp_card_no))+' which is > 7. Actual DOC='+Convert(Varchar, dateadd(mm, e.probation_period, e.Date_of_Joining), 103)+'' then 'N/A' -- when day(e.Date_of_Joining)>15 -- then 'N/A'-- 'DOC Exteded By 1 month. Reason:- DOJ should be before 15th of month. Actual DOC='+Convert(varchar, dateadd(mm, e.probation_period, e.Date_of_Joining), 103)+''
else 'N/A'
end Remark , case when e.sex='F' then 'Female'
when e.sex='M' then 'Male'
else 'NA' end Gender , ctc.mkey CTC_MKEY , case when e.on_Deput_comp_mkey>0 then Dcomp.add1+' '+Dcomp.add2+ '' +Dcomp.city else comp.add1+' '+comp.add2+ '' +comp.city
end Company_Add , convert( varchar, getdate(), 103) curr_date , case when b1.add_tinfo1='C' then 'Core' else 'Support' end Core_Support , case when e.ctc_hdr_mkey in (213, 214, 215) then 30 else 90 end Prob_period_Letter, isnull(e.func_role_mkey, 0) func_role_mkey --, e.new_design_mkey
from emp_mst e
left join emp_shift_details b on e.emp_card_no=b.emp_card_no
and b.mkey = ( select max(mkey) from emp_shift_details c where b.emp_card_no=c.emp_card_no )
left join emp_mst1 e1 on e.emp_card_no=e1.emp_card_no -- and e.emp_card_no=1216 -- left join user_mst u on e.mkey=u.employee_mkey
left join shift_master s on b.shift_mkey=s.mkey
left join company_mst comp on e.comp_mkey=comp.mkey and comp.fa_year=2008
left join company_mst Dcomp on e.on_Deput_comp_mkey=Dcomp.mkey and Dcomp.fa_year=2008
left join type_mst_a grade on e.ctc_hdr_mkey=grade.add_iinfo1
left join type_mst_a d1 on e.new_design_mkey=d1.master_mkey
left join type_mst_a b1 on e.New_Dept_mkey=b1.master_mkey
left join type_mst_a b2 on e.New_subDept_mkey=b2.master_mkey
left join type_mst_a pw on e.paidweekly_off=pw.type_abbr
left join type_mst_a po on e.weekly_off=po.type_abbr
left join type_mst_a hub on e.Hub_Mkey=hub.master_mkey
left join state_mst st on e1.domicile_mkey=st.mkey and e1.country_of_birth=st.country_mkey and st.add_flag='N'
left join emp_mst s1 on s1.mkey=e.reporting_to
left join emp_mst s2 on s2.mkey=e.reporting_to2
left join ctc_recal_group_hdr ctc on ctc.emp_mkey=e.mkey
and ctc.mkey = ( select max(mkey) from ctc_recal_group_hdr c where e.mkey=c.emp_mkey and delete_flag='N' )
left join emp_address_trl ad on e.mkey=ad.mkey and ad.entry_sr_no=( select max(entry_sr_no) from emp_address_trl where mkey=e.mkey )
left join city_mst as c on ad.city_mkey = c.mkey
left join state_mst stmst
on ad.state_mkey = stmst.mkey
left join p_Emp_Family fam on e.mkey=fam.mkey and fam.entry_srno=1
left join p_relation_mst r on r.mkey=fam.relation_mkey
left join p_Emp_Family fam1 on e.mkey=fam1.mkey and fam1.entry_srno=2
left join p_relation_mst r1 on r1.mkey=fam1.relation_mkey
left join p_Emp_Family fam2 on e.mkey=fam2.mkey and fam2.entry_srno=3
left join p_relation_mst r2 on r2.mkey=fam2.relation_mkey
left join p_Emp_Family fam3 on e.mkey=fam3.mkey and fam3.entry_srno=4
left join p_relation_mst r3 on r3.mkey=fam3.relation_mkey
left join type_mst_a bld on e1.blood_type=bld.master_mkey and bld.type_code='BG'
and bld.delete_flag='N'
left join user_mst usr on e.mkey=usr.employee_mkey
left join p_bank_mst bank on e.bank_code=bank.mkey -- where type_code='PWP'
where 1=1
and e.emp_card_no!=9999 -- and e.emp_card_no not in ( select emp_card_no from P_Emp_Confirmation_Hdr )
and e.emp_card_no in ( 2385 ,2380 ,2377 ,2337 ,2361,2371
)
) aa -- and e.emp_card_no=9999 -- and e.mkey<=1932 -- b.mkey = ( select max(mkey) from emp_shift_details c where b.emp_card_no=c.emp_card_no ) -- and e.emp_card_no=2192 -- and ctc.mkey = ( select max(mkey) from ctc_recal_group_hdr c where e.mkey=c.emp_mkey and delete_flag='N' ) GO
Tables:
