Computed column showing NULL values

(0) Share

Report

Posted on by Parag Chapre

12,254 Most Valuable Professional

Hello,

I have created a View with two tables. One is the Date table and another with EmployeeSickTable. I want to fetch data from another table in my view but that table has one to N relation.

My Date table has the below data.

From Date

01/10/2020

02/10/2020

03/10/2020

04/10/2020

05/10/2020

06/10/2020

07/10/2020

08/10/2020

09/10/2020

10/10/2020

EmployeeSickTable

Employee Id	SickId	Sick%
1	1001	100
2	1002	60

SicknessDetails

SickId	Sick%	Valid from	Valid to
1001	100	01/10/2020	05/10/2020
1001	60	06/10/2020	10/10/2020

My View Structure

DateTable >> EmployeeSickTable ( There is no join here because I want to get Employee sickness data per day)

If I add join with EmployeeSickTable >>SicknessDetails then It shows multiple lines which I want to avoid.

I want to see the below data with two table structure like DateTable >> EmployeeSickTable

My view should be like this

From Date	Employee	SickId	Valid From	Valid to	Sick%
01/10/2020	1	1001	01/10/2020	10/10/2020	100
02/10/2020	1	1001	01/10/2020	10/10/2020	100
03/10/2020	1	1001	01/10/2020	10/10/2020	100
04/10/2020	1	1001	01/10/2020	10/10/2020	100
05/10/2020	1	1001	01/10/2020	10/10/2020	100
06/10/2020	1	1001	01/10/2020	10/10/2020	60
07/10/2020	1	1001	01/10/2020	10/10/2020	60
08/10/2020	1	1001	01/10/2020	10/10/2020	60
09/10/2020	1	1001	01/10/2020	10/10/2020	60
10/10/2020	1	1001	01/10/2020	10/10/2020	60

I have created a computed column like this

private static str computeSickPercent()
{

SicknessDetails sicknessDetails;

str trendDateStr = SysComputedColumn::returnField(
tableStr(MyView),
tableStr(DateTable),
fieldStr(DateTable, TrendDate)) ;

str SickIdHeader = SysComputedColumn::returnField(
tableStr(MyView),
tableStr(EmployeeSickTable),
fieldStr(EmployeeSickTable, SickId)) ;

TransDate trendDate= str2date(trendDateStr,213);

EmployeeSickTable employeeSickTable = EmployeeSickTable::find(SickIdHeader);

select validtimestate(trendDate) sicknessDetails
where sicknessDetails.SickId == employeeSickTable.sickId;

return num2Str(sicknessDetails.Sick%,-1,-1,2,2);

}

I want to calculate Sickness % per day based on a date table but it is showing null values.

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Martin Dráb 239,381 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Please use Insert > Insert Code (in the rich-formatting view) to paste source code. Like this:

private static str computeSickPercent() { SicknessDetails sicknessDetails; str trendDateStr = SysComputedColumn::returnField( tableStr(MyView), tableStr(DateTable), fieldStr(DateTable, TrendDate)); str sickIdHeader = SysComputedColumn::returnField( tableStr(MyView), tableStr(EmployeeSickTable), fieldStr(EmployeeSickTable, SickId)); TransDate trendDate = str2date(trendDateStr,213); EmployeeSickTable employeeSickTable = EmployeeSickTable::find(sickIdHeader); select validtimestate(trendDate) sicknessDetails where sicknessDetails.SickId == employeeSickTable.sickId; return num2Str(sicknessDetails.Sick%, -1, -1, 2, 2); }

Your code is indeed wrong. It shows that you don't fully understand the purpose of computed columns.

Computed columns are used to generate T-SQL code, which is inserted to the view definition. This happens when you synchronize the database. When you execute the view, it doesn't call the method; it just executes the view.

According to your code, you believe that it'll call your method when fetching the value, but it's not the case.

Another wrong expectation is that returnField() returns a value of the field. It doesn't. What we're doing ism generating T-SQL code and returnField() will give you a field identifier (such as T1.SICKID) that you can use in T-SQL code.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,381 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Do you need a computed column at all? Wouldn't it be sufficient to outer-join SicknessDetails table?

For example:

select dateTable join employeeSickTable // no condition -> Cartesian product outer join sicknessDetails where sicknessDetails.SickId == employeeSickTable.SickId && sicknessDetails.ValidFrom >= dateTable.DateValue && sicknessDetails.ValidTo <= dateTable.DateValue;

Was this reply helpful? Yes No