M.App Enterprise Discussions

[poula123]

Hello,

I need to call a table-valued function from the SQL server in filter list.

and this is the creation script of the function:

USE [EAMS01]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[poula3] (@username varchar(50) )
RETURNS @t TABLE(Id bigint,Name nvarchar(255),ParentId bigint)
    AS
	begin
	DECLARE @counter1 INT = 0;
	DECLARE @counter2 INT = 0;
	declare @table1 table(Site_ID BIGINT);
	declare @table2 table(Site_ID BIGINT);
	declare @SiteParent bigint;
	set @SiteParent = (select [SITE_ID] from [EAMS01].[ADMIN].[SYS_USERS] where [USERNAME]=@username);
	insert into @table1
	SELECT Site_ID  
	FROM site.SITES
	where PARENT_SITE_ID = @SiteParent or Site_ID = @SiteParent;

	set @counter1 = (select count(*)  from @table1);

	while (@counter1 != @counter2)
	BEGIN
		set @counter2 = @counter1
		insert into @table1
		SELECT Site_ID 
		FROM site.SITES
		where PARENT_SITE_ID in (select SITE_ID from @table1);

		insert into @table2 select distinct(Site_ID) from @table1;
		delete from @table1;
		insert into @table1 select Site_ID from @table2;
		delete from @table2;
		SET @counter1 = (select count(Site_ID) from @table1);
	END	
	
	insert into @t select tbl1.Site_ID ,  governSite.SITE_NAME ,sites.PARENT_SITE_ID
	FROM @table1 tbl1
	inner JOIN SITE.SITES sites ON SITES.SITE_ID = tbl1.Site_ID
	LEFT JOIN SITE.GOVERN_SITE governSite ON governSite.CONTACT_SITE_ID = sites.SITE_ID
	return
	end
GO

when I call the function in the filter I found this log "no such table: poula3".

I tried to define the function as a table so I write it as an entity.

and this is the code to define the function.

<Entity Id="poula3" Table="poula3" Key="Id" SyncType="Automatic" >
<Field Name="Id" Type="Integer" IsRequired="True" /> 
<Field Name="Name" Type="String" /> 
<Field Name="ParentId" Type="Integer"  /> 
</Entity>

and have got this log error: Parameters were not supplied for the function 'poula3'.

and this is the filter I need to use in the list:

<Filter Id="list_view" Sql="FLAG_GEOMETRY =1 and  SITE_ID in( select Id from [poula3](@{System.UserName}))" IsDefault="False" />

so how can I define a table-valued function in the shell file of a mobile application?