SQLTeam.com | Weblogs | Forums

Stored procedure giving error

sql2008

#1

I have a stored procedure which is giving me error related to View. The error is

Msg 4121, Level 16, State 1, Procedure GetDataForConfirmationModule, Line 16
Cannot find either column "erplive" or the user-defined function or aggregate "erplive.dbo.AbsentCount_view", or the name is ambiguous.
Msg 4413, Level 16, State 1, Procedure GetDataForConfirmationModule, Line 16
Could not use view or function 'XXACL_ERP_EMP_Confirmation_View' because of binding errors.

Here is my SP.

ALTER PROCEDURE [dbo].[GetDataForConfirmationModule]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select * from (
select *,Row_Number() over(order by [Emp_Name]) as SrNo
from
(select [Employee_Name] Emp_Name,Emp_Card_No,Department,Designation,[RA1 Name] As RA1Name,
[RA2 Name] As RA2Name,datename(month,Confirmation_Due_On)  Month, year (Confirmation_Date_New) Year,0 as 'Confirm',ctc_hdr_mkey,department_mkey,subdept_mkey,
new_design_mkey,0 tot_exp,'NA' Qualification, Probation_Period,convert(varchar,Confirmation_Date,103) Confirmation_Due_On,Absent_count as 'Leave_Taken',emp_mkey ,
[Date_of_Joining] As Date_of_Joining,Remark,Func_Role_Mkey from XXACL_ERP_EMP_Confirmation_View where  isnull(Wages_Type,'P')='P' 
and [Employee Status]='Active'
and  (([resigned date]='' or [resigned date] is null) or ([resigned date]!='' and Emp_Type='Consultant'))
and
[Date_of_Joining] between 
(select (FromDt) from GetDateRangeFromToforConfirmationMail_view(7,1) where srno=1)   
and
(select (ToDt) from GetDateRangeFromToforConfirmationMail_view(7,1) where srno=1)   
and Company_Name not like '%HELIK%'
and Remark='N/A' and emp_card_no not in
(select emp_card_no from P_Emp_Confirmation_Hdr where delete_flag='N' and hr_flag='Y')
union 

-- Manual Entry
select [Employee_Name] Emp_Name,Emp_Card_No,Department,Designation,[RA1 Name] As RA1Name,
[RA2 Name] As RA2Name,datename(month,Confirmation_Due_On) Month, year (Confirmation_Date_New) Year,0 as 'Confirm',ctc_hdr_mkey,department_mkey,subdept_mkey,
new_design_mkey,0 tot_exp,'NA' Qualification,
(select datediff(d,[Date_of_Joining],CONVERT(VARCHAR(25),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),101))/30) as Probation_Period,
CONVERT(VARCHAR(25),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),103) as 'confirmation_Due_On' ,
Absent_count as 'Leave_Taken',emp_mkey ,
[Date_of_Joining] As Date_of_Joining,Remark,Func_Role_Mkey from XXACL_ERP_EMP_Confirmation_View where Wages_Type='P'
and [Employee Status]='Active'
and (([resigned date]='' or [resigned date] is null) or ([resigned date]!='' and Emp_Type='Consultant'))
and emp_card_no in(2282,2315)
--Ends here
union
select [Employee_Name] Emp_Name,Emp_Card_No,Department,Designation,[RA1 Name] As RA1Name,
[RA2 Name] As RA2Name,datename(month,Confirmation_Due_On) Month, year (Confirmation_Date_New) Year,0 as 'Confirm',ctc_hdr_mkey,department_mkey,subdept_mkey,
new_design_mkey,0 tot_exp,'NA' Qualification, Probation_Period,convert(varchar,Confirmation_Date,103) Confirmation_Due_On,Absent_count as 'Leave_Taken',emp_mkey ,
[Date_of_Joining] As Date_of_Joining,Remark,Func_Role_Mkey from XXACL_ERP_EMP_Confirmation_View_For_PreviousMonth where isnull(Wages_Type,'P')='P'
and [Employee Status]='Active'
and (([resigned date]='' or [resigned date] is null) or ([resigned date]!='' and Emp_Type='Consultant'))
and Company_Name not like '%HELIK%'
and
[Date_of_Joining] between
(select (FromDt) from GetDateRangeFromToforConfirmationMail_view(7,1) where srno=2)
and
(select (ToDt) from GetDateRangeFromToforConfirmationMail_view(7,1) where srno=2)
and Remark like '%DOC Exteded By 1 month. Reason:- Taken leave%'
and emp_card_no not in
(select emp_card_no from P_Emp_Confirmation_Hdr where delete_flag='N' and hr_flag='Y'))a)b
order by emp_name
END

Please help


#2

I do not see "erplive" or "AbsentCount_view" mentioned in your query. Have you posted all of it?


#3

I haven't posted the View yet, Wait I will post that too

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]
,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 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)+'
'
-- 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 in (select emp_card_no from P_Emp_Confirmation_Hdr)
) 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

Do let me know if you need anything else


#4

That view doesn't output a "erplive" column, either.

I did, however find the reference the error provided.

select dbo.AbsentCount_view(e.emp_card_no)) end as Absent_count

My question would be is the function a Scalar, Multi-Statement TVF, or an Inline TVF? If it's either of the last two, then the function cannot be used in a SELECT list. Instead, it must be used in a FROM clause or an APPLY clause.