Dynamics Community Forum Thread Details

Computed Column error in View (Convertion of string to int)

Hello,this is a method I use for creating a computed field in View and I need to filter for priceDiscTable.AccountRelation = "01". AccountRelation is String And I have a problem with the value I want to filter "01", because in SQL it is always converted to Int and I get this error: 'Conversion failed when converting the nvarchar value to data type int'.

public static server str getprice()

{ 
str accRelation = "01";
 str price; 
ItemID itemId; 
itemId = SysComputedColumn::returnField(tableStr(PriceDisc_SAM),tableStr(InventTable),fieldStr(InventTable, itemId)); 

price = 'select top 1 priceUnit from PriceDiscTable where PriceDiscTable.ItemCode = 0 and PriceDiscTable.ItemRelation =' + itemId + ' and PriceDiscTable.AccountRelation = 'accRelation'+ 
and PriceDiscTable.Relation = 4 order by PriceDiscTable.FromDate Desc'; 

return price; 
}

The T-SQL definition I have is:

SELECT ITEMID, DATAAREAID, PARTITION, RECID, CAST
((SELECT TOP (1) PRICEUNIT
FROM dbo.PRICEDISCTABLE
WHERE (ITEMCODE = 0) AND (ITEMRELATION = T1.ITEMID) AND (ACCOUNTRELATION = 01) AND (RELATION = 4)
ORDER BY FROMDATE DESC) AS NUMERIC(32, 16)) AS PRICEUNIT
FROM dbo.INVENTTABLE AS T1

and I want it to be like :

SELECT ITEMID, DATAAREAID, PARTITION, RECID, CAST
((SELECT TOP (1) PRICEUNIT
FROM dbo.PRICEDISCTABLE
WHERE (ITEMCODE = 0) AND (ITEMRELATION = T1.ITEMID) AND (ACCOUNTRELATION = '01') AND (RELATION = 4)
ORDER BY FROMDATE DESC) AS NUMERIC(32, 16)) AS PRICEUNIT
FROM dbo.INVENTTABLE AS T1.
How should I write my  method so 01 is taken as a string?

price = "select top 1 priceUnit from PriceDiscTable where PriceDiscTable.ItemCode = 0 and PriceDiscTable.ItemRelation = '" + itemId + "' and PriceDiscTable.AccountRelation = '" +accRelation + "' and PriceDiscTable.Relation = 4 order by PriceDiscTable.FromDate Desc";

Quick Links