Skip to main content

Hi TX Support,


I wanna know how to implementing scalar function with COALESCE in TX ? 
Here i attach the function, could you please tell me how to do that ?


 


USE EQAD_DATA]
GO
/****** Object: UserDefinedFunction ndbo].]GetAliasesByWo1] Script Date: 03/20/2018 16:15:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION Ndbo].]GetAliasesByWo1]
(
@item nvarchar(max),
@length float,
@catego nvarchar(max),
@opt nvarchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max)
select @output = COALESCE(@output + ', ', '') + wo_lot + '=' + convert(nvarchar, wo_qty_ord)
FROM wo_mstr_data
where wo_part = @item and
wo__dec01 = @length and
wo__chr01 = @catego and
RIGHT(wo_rmks,1) = @opt and
wo_status = 'R' and
SUBSTRING(wo_routing,1,6) = 'REWORK'


return @output
END

Hi Ole, 


I've deploy the function and it success.


Then when i wanna set it in the custom transformation of custom field,


it shows


-ubW00dB20GE2MlYJLDnBA.png


Whats the meaning ? and how to solve it ?


if you wanna know my custom transformation script.


here i attach mine 


kxy4iHvrFfuOmVBmQv0gjQ.png

Hi Muhammad, 


To summarize the response I gave on your ticket, it looks like your function needs four input parameters, and you've only passed in three here!


Best regards,


Doug Wynkoop

Hi Doug,
I already add one more parameter, it success when deploying. But unfortunately , it returns Null values.... Instead still get rid of this UDF, can this function work in another way ? for example, before this ... I do direct custom transformation on custom field... I put the core of the function (starts from "COALESCE ......") and do the 'WHERE' condition earlier on the source table ... it works but only returning the first value... which it should be return more result (kind like looping)... is there any idea ? sorry to make you confused with my questions... but I really try it to explain it in detail

Reply


Terms & ConditionsCookie settings