Complex sql query as Table in schema

Data Abstract
1

Hi,

I am trying to add a complex query as Sql statement in a Table definition.
Something like

select		[Id], [EntityId], [EntityRelationId], [EntityAuthorizationId], [ContactId], [IsPrimary], [IsDeleted], [RowVersion],
			[ContactDisplayName], [ContactSsn], [ContactTin], [ContactPhone1TypeName], [ContractPhone1Label], [ContactPhone1],
			[ContactPhone2TypeName], [ContractPhone2Label], [ContactPhone2], [ContactPhone3TypeName], [ContractPhone3Label],
			[ContactPhone3], [ContactEmail], [ContactAddressLine]
	FROM   	[Entities].[EntityContact] ec
			cross apply
			(select top 1 ed.DisplayName as ContactDisplayName, ed.Ssn as ContactSsn, ed.Tin as ContactTin,
	                     ed.Phone1TypeName as ContactPhone1TypeName, ed.Phone1Label as ContractPhone1Label,
	                     ed.Phone1 as ContactPhone1,
	                     ed.Phone2TypeName as ContactPhone2TypeName, ed.Phone2Label as ContractPhone2Label,
	                     ed.Phone2 as ContactPhone2,
	                     ed.Phone3TypeName as ContactPhone3TypeName, ed.Phone3Label as ContractPhone3Label,
	                     ed.Phone3 as ContactPhone3, ed.Email as ContactEmail, 
	                     ed.AddressLine as ContactAddressLine
			from 	Entities.vEntityData ed 
			where	ed.Id = ec.Id 
			) ved	
where		{WHERE}

in this example i set [Entities].[EntityContact] as refresh table.
i also use a business processor and created commands for insert, update and delete.

in all cases i get either
“The multi-part identifier Entities.EntityContact.EntityId could not be bound” or
“one or more updates failed to apply on the server. -1: cant refresh delta, record isn’t found”

how can i have this kind of functionality, to have a complex sql as view model and have separate statements - commands for insert, update and delete. In all my attempts i get errors either on open or on internal refresh after apply updates.

Thanks
Yiannis

3

Hi,

Can you create a simple testcase that reproduces this issue, pls?
Are you using MS SQL Server?
You can drop email to support@ for keeping privacy

pls attach DDL script too

1 Like
4

Hi EvgenyK,

I have a small test project ready. Project uses sql server and SQLNCLI11.1 as driver.
But going back and forth between SQLNCLI11.1, OleDB, and firedac, seems to somehow corrected the issue i had (95% i think is the ado driver).
I will keep this project and if i have the same issue again i will reproduce it and send it to support right away without tweaking it.

Thanks
Yiannis

1 Like