Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguo

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguo

• chandrala

  Is FN_NIVELCOMPETENCIACARGO a table valued function The select you are using is for scalar functions. If it is a TVF, change

  (select

  dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g

  to

  dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia) as g

  
  
  

• Brilly

  TVF
  
  

• Chris Hilemon

  If your intent is to execute the TVF for each row returned by your query, then this will work.

  Change to

  select tvf1.*, t1.* from (

  select a.nmempresa,a.nmtipocompetencia, e.nmcompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,

  b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia

  from

  tgsc_tiposcompetencias a,

  TGSC_TIPOSCOMPETENCIASxcargo b,

  tgsc_cargos c,

  tgsc_ciclos d,

  tgsc_competenciasxcargo e, tgsc_competencias f

  where a.nmempresa=72 and a.nmciclo=9

  and b.nmtipocompetencia=a.nmtipocompetencia

  and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9

  and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9

  and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72

  and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia

  and f.nmtipocompetencia=b.nmtipocompetencia) as t1 CROSS APPLY dbo.FN_NIVELCOMPETENCIACARGO(t1.nmempresa,t1.nmciclo, t1.nmtipocompetencia, t1.nmcargo,t1.nmcompetencia) as tvf1

  
  
  

• Mohsen Kokabi

  Is dbo.FN_NIVELCOMPETENCIACARGO a TVF or scalar function
  
  

• boyo358

  Thank you very much ; I will try and If i am having problems I will come back here.
  
  

• Srinivasa Rao

  I changed to this

  select a.nmempresa,a.nmtipocompetencia, e.nmcompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,

  b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia

  from

  tgsc_tiposcompetencias a,

  TGSC_TIPOSCOMPETENCIASxcargo b,

  tgsc_cargos c,

  tgsc_ciclos d,

  tgsc_competenciasxcargo e, tgsc_competencias f,

  dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia) as g

  where a.nmempresa=72 and a.nmciclo=9

  and b.nmtipocompetencia=a.nmtipocompetencia

  and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9

  and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9

  and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72

  and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia

  and f.nmtipocompetencia=b.nmtipocompetencia

  Now the error is this

  Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "a.nmempresa" could not be bound.

  Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "a.nmciclo" could not be bound.

  Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "a.nmtipocompetencia" could not be bound.

  Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "c.nmcargo" could not be bound.

  Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "f.nmcompetencia" could not be bound.

  
  
  
• Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguo