You’re offline. This is a read only version of the page.
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.
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;
Please use Insert > Insert Code (in the rich-formatting view) to paste source code. Like this:
Share
Report
All responses (
Answers (
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.
#1
Martin Dráb
445
Most Valuable Professional
#2
Abhilash Warrier
310
#3
Saalim Ansari
261
