M.App Enterprise Discussions

Discuss topics with other M.App Enterprise Product pioneers and experts to get the most out of it.
Showing results for 
Search instead for 
Do you mean 
Reply
Contributor
Posts: 31
Registered: ‎03-16-2020

Define table-valued function in mobile application

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?