I think Martin is correct,
I believe what Martin is trying to say is to do it like this:
public static server str TransferFlag()
{
return strFmt('ISNULL((select top 1 1 from InventTransferTable as inventTransferTable WHERE inventTransferTable.PARTITION = %1 AND inventTransferTable.DATAAREAID = %2 AND inventTransferTable.SalesId = %3), %4)'
, SysComputedColumn::comparisonField(tableStr(TransferFlagView), identifierStr(SalesTable), fieldStr(SalesTable, Partition))
, SysComputedColumn::comparisonField(tableStr(TransferFlagView), identifierStr(SalesTable), fieldStr(SalesTable, DataAreaId))
, SysComputedColumn::comparisonField(tableStr(TransferFlagView), identifierStr(SalesTable), fieldStr(SalesTable, SalesId))
, 1
);
}
This code will give you sth similar to this query:
SELECT T1.Field1, T1.Field2, T1.SALESID, T1.DATAAREAID, T1.PARTITION, T1.RECID, T2.SALESID AS SalesTableSALESID, T2.DATAAREAID AS DATAAREAID#2, T2.PARTITION AS PARTITION#2, CAST(ISNULL
((SELECT TOP (1) 1 AS Expr1
FROM dbo.InventTransferTable AS inventTransferTable
WHERE (PARTITION = T2.PARTITION) AND (DATAAREAID = T2.DATAAREAID) AND (SALESID = T2.SALESID)), 1) AS NVARCHAR(10)) AS TransferFlag
FROM dbo.InventTransferTable AS T1 JOIN
dbo.SALESTABLE AS T2 ON T1.SALESID = T2.SALESID AND T1.DATAAREAID = T2.DATAAREAID AND T1.PARTITION = T2.PARTITION
while the first approach will give you sth similar to this query
SELECT T1.Field1, T1.Field2, T1.SALESID, T1.DATAAREAID, T1.PARTITION, T1.RECID, T2.SALESID AS SalesTableSALESID, T2.DATAAREAID AS DATAAREAID#2, T2.PARTITION AS PARTITION#2,
CAST((CASE WHEN T1.SALESID = T2.SALESID THEN 1 ELSE 0 END) AS NVARCHAR(10)) AS TransferFlag
FROM dbo.InventTransferTable AS T1 JOIN
dbo.SALESTABLE AS T2 ON T1.SALESID = T2.SALESID AND T1.DATAAREAID = T2.DATAAREAID AND T1.PARTITION = T2.PARTITION
Always write your SQL when trying to do computed columns to make sure your computed column is returning what you want. If this is not what you want then please share more details
Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
