What kind of function? (1 Viewer)

[neoklis]

Hi all,

I have a view and i want to create a function that will return some calculated expressions as columns for each row of the view. Can someone quide me of what kind of T-Sql function am i going to use;

Thank you

 

[neoklis]

This what i’ve done so far... I create a function and it runs correctly

ALTER FUNCTION dbo.Perf_test
(
@WcId char (10)
,@MatGroup varchar(10)
,@Mrp varchar (3)
,@Descr varchar(50)
)
RETURNS @table TABLE (Type varchar(4))
AS

BEGIN
declare @Type as varchar(4)
set @Type=dbo.Perf_categorize(@WcId,@MatGroup,@Mrp,@Descr)

Insert @table
Select @Type
return
END

And I create the sql statement which I know is wrong.. I can’t parse it correctly. Any help will be appreciated.

SELECT ID,WCID, Material_Group, MRP_Controller, Description
FROM PERF_Performance where id=id_
(select * from dbo.perf_test(WCID,Material_Group,MRP_Controller,Description))

 

[neoklis]

I have tried this too but it returns no columns…

SELECT ID,WCID, Material_Group, MRP_Controller, Description
FROM PERF_Performance as t
cross apply dbo.perf_test(t.WCID,t.Material_Group,t.MRP_Controller,t.Description) as f

 

[neoklis]

I have also try this

SELECT ID,WCID, Material_Group, MRP_Controller, Description,(select * from dbo.perf_test(WCID,Material_Group,MRP_Controller,Description))
FROM PERF_Performance

The problem I deal is that is working nicely for only one returned column from the function but the returned column has no column name. If I want more returned columns I get error.

 

[neoklis]

Can someone pls help me..? When i exec the statement

SELECT Perf.ID,Perf.WCID,Perf.Material_Group,Perf.MRP_Controller,Perf.Description,Perf.Component_Flag,
Perf.D,Perf.Length,calc.type
FROM PERF_Performance Perf
Left Outer Join dbo.perf_calc(108146,'10000679','ΕΤ3022','740',
'Cut m 012,700X0,80X20000 4%HBO XE09-12 R',0,12.7,20000) Calc
on Perf.id=Calc.Id

everything runs great. but when i execute

SELECT Perf.ID,Perf.WCID,Perf.Material_Group,Perf.MRP_Controller,Perf.Description,Perf.Component_Flag,
Perf.D,Perf.Length,calc.type
FROM PERF_Performance Perf
Left Outer Join dbo.perf_calc(Perf.ID,Perf.WCID,Perf.Material_Group,Perf.MRP_Controller,
Perf.Description,Perf.Component_Flag,Perf.D,Perf.Length) Calc
on Perf.id=Calc.Id

I get these erros.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.WCID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.Material_Group" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.MRP_Controller" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.Description" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.Component_Flag" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.D" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Perf.Length" could not be bound.

Can someone help me..?

 

[neoklis]

It finally worked...

SELECT Perf.ID,Perf.WCID,Perf.Material_Group,Perf.MRP_Controller,Perf.Description,Perf.Component_Flag,
Perf.D,Perf.Length,calc.Type,calc.Vtakinmin
FROM PERF_Performance as Perf
Outer Apply dbo.perf_calc(Perf.ID,Perf.WCID,Perf.Material_Group,Perf.MRP_Controller,
Perf.Description,Perf.Component_Flag,Perf.D,Perf.Length) as Calc