To homepage To homepage
Solved

Implementing Scalar Function with COALESCE in TX

  • 22 March 2018
  • 5 replies
  • 19 views
A
Rank

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 [QAD_DATA]
GO
/****** Object: UserDefinedFunction [dbo].[GetAliasesByWo1] Script Date: 03/20/2018 16:15:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[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

 

icon

Best answer by Anonymous 22 March 2018, 09:47

View original

5 replies

A

Hi Muhammad

Please right click the node "User Defined Functions":

For newer versions of TimeXtender this is found under the Scripts node in a Data Area within a Data Warehouse instance

Type in the name you want to use for the function followed by  your TSQL code starting with "Create Function":

Deploy the function and start using it in Script Actions, Stored Procedures or Views.

Kind Regards

Ole Engberg

Solution Architect

A
Rank

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

Whats the meaning ? and how to solve it ?

if you wanna know my custom transformation script.

here i attach mine 

 

W
Rank
Badge

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

A
Rank

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

A

Hi Muhammad

Transformations (and lookups) can only retrieve one value, not a set.

I'm not sure, what you want to achieve, but if you want to create new rows by joining multiple rows in a lookup table (left or cross join), you must create a custom view or even a custom script to insert rows to your table.

Hopefully this information is helpful to you.

Kind Regards
Ole Engberg
Solution Architect

Reply


Terms and ConditionsCookie settings