Computed field in View - Ambiguous column name

(0) Share

Report

Posted on by Willem van Duren

230

I have a custom made View in which I made a computed field which shows the difference between two date fields. This is a ViewComputedColumnString field.

I use the ViewMethod: calcdates, which I made as follows:

public class ELCWDOrderMismatchView extends common
{
    /// 
    ///
    /// 
    public static server str calcDates()
    {
     return strFmt("CASE WHEN ELCWDActualConfirmedToCustomerDate != '1900-01-01 00:00:00.000' AND ReceiptDateConfirmed != '1900-01-01 00:00:00.000' THEN DATEDIFF(DAY, %1, %2) ELSE 0 END",
                fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate),
                fieldStr(SalesLine, ReceiptDateConfirmed));

    }

}

This works fine. Untill I added an extra datasource to my view.

My view is based on salesline table. I added an innerjoin to the SalesTable (link on SalesId and DataareaId), of which I need to add the WorkerSalesTaker to my View.

When I sync I get a Sync error on the 'ReceiptDateConfirmed' field (Ambiguous column).
- I tried to remove my computed Date field: then it syncs fine

It seems that the computed field method does not know what datasource to use for the 'ReceiptDateConfirmed' field. Is there something that I need to add to the code to make it look to Salesline only ?

I have the same question (0)

All responses (13)

Answers (1)

Suggested answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (1)

Report

What you should have in your T-SQL code is something like T2.ReceiptDateConfirmed. Hardcoding it wouldn't be a good idea, but there are methods for this purpose, such as SysComputedColumn::returnField().

You can, and should, use methods to avoid hardcoding other things as well, such as date time values. There are even methods for control structures, such as SysComputedColumn::if() (use "Find references" to see examples of how to use it).

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report

Thanks Martin. I will dive into this. I will post the outcome :-)

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report

Hi Martin, I tried several things but it keeps giving Sync errors. I am sure I use the Syscomputed column in the wrong way. I even tried to hardcode T1 just to try if it works, but also that does not help. The SQL part is just not my cup of tea I guess.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Willem van Duren,

Could you please share the latest code you are trying with (using SysComputedColumn)?

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report
Of course!

First I tried this:

return strFmt("CASE WHEN ELCWDActualConfirmedToCustomerDate != '1900-01-01 00:00:00.000' AND %3 != '1900-01-01 00:00:00.000' THEN DATEDIFF(DAY, %1, %2) ELSE 0 END", fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate), fieldStr(SalesLine, ReceiptDateConfirmed), SysComputedColumn::returnField(tableStr(ELCWDOrderMismatchView),identifierStr(SalesLine),fieldStr(Salesline,ReceiptDateConfirmed)));

Then I tried this (hard code). It still gave the same 'ambiguous column'error:

public static server str calcDates() { return strFmt("CASE WHEN T1.ELCWDActualConfirmedToCustomerDate != '1900-01-01 00:00:00.000' AND T1.ReceiptDateConfirmed != '1900-01-01 00:00:00.000' THEN DATEDIFF(DAY, %1, %2) ELSE 0 END", fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate), fieldStr(SalesLine, ReceiptDateConfirmed)); }

I am really not good at SQL programming, and for me this is a trial and error process I am afraid :-(

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

You still have both field names hardcoded. One after 'WHEN' and then both as %1 and %2.

You must fix all bugs, not just one.

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report
Ok! So I made it as follows:

return strFmt("CASE WHEN %2 != '1900-01-01 00:00:00.000' AND %1 != '1900-01-01 00:00:00.000' THEN DATEDIFF(DAY, %2, %1) ELSE 0 END", SysComputedColumn::returnField(tableStr(ELCWDOrderMismatchView),identifierStr(SalesLine),fieldStr(Salesline,ReceiptDateConfirmed)), SysComputedColumn::returnField(tableStr(ELCWDOrderMismatchView),identifierStr(SalesLine),fieldStr(Salesline,ELCWDActualConfirmedToCustomerDate)));

Sync engine gives error:

Severity Code Description Project File Line Suppression State
Error Database execution failed: 'identifierStr' is not a recognized built-in function name. 0

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (1)

Report

Hi Willem van Duren,

You can try using the SysComputedColumn::If. Please check this post which has a few examples and see if that helps you.

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report

Thank you Gunjan! This looks like a good instruction. I am working on it and keep you updated.

Was this reply helpful? Yes No
Willem van Duren 230 on at

Like (0)

Report
OK. I am getting somewhere :-)

//Viewname tablename viewname = tableStr(ELCWDOrderMismatchView); //Dates str actualconfirmeddate = SysComputedColumn::returnField(viewname, identifierStr(SalesLine), fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate)); str confirmedreceiptdate = SysComputedColumn::returnField(viewname, identifierStr(SalesLine), fieldStr(SalesLine, ReceiptDateConfirmed)); return SysComputedColumn::getDateDiff(actualconfirmeddate,confirmedreceiptdate);

Returns the difference in days.

The only challenge left is to exclude empty dates. I made this code to achieve this:

SysComputedColumn::if( SysComputedColumn::notEqualExpression( SysComputedColumn::comparisonField(viewname, identifierStr(SalesLine), fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate)), SysComputedColumn::comparisonLiteral('1900-01-01 00:00:00.000')) )

However, I need an "Or" in my If, because if ELCWDActualconfirmedToCustomerDate is empty OR ReceiptDateConfirmed is empty it should return "0".

I tried this, but with no luck:

return SysComputedColumn::if(SysComputedColumn::EqualExpression( SysComputedColumn::comparisonField(viewname, identifierStr(SalesLine), fieldStr(SalesLine, ELCWDActualConfirmedToCustomerDate)), SysComputedColumn::comparisonLiteral('1900-01-01 00:00:00.000')), SysComputedColumn::returnField(viewname, identifierStr(SalesLine), fieldstr(SalesLine, ELCWDActualConfirmedToCustomerDate)), SysComputedColumn::returnLiteral(0)); SysComputedColumn::if(SysComputedColumn::EqualExpression( SysComputedColumn::comparisonField(viewname, identifierStr(SalesLine), fieldStr(SalesLine, ReceiptDateConfirmed)), SysComputedColumn::comparisonLiteral('1900-01-01 00:00:00.000')), SysComputedColumn::returnField(viewname, identifierStr(SalesLine), fieldstr(SalesLine, ReceiptDateConfirmed)), SysComputedColumn::returnLiteral(0)); SysComputedColumn::getDateDiff(actualconfirmeddate,confirmedreceiptdate);

Was this reply helpful? Yes No

Join the conversation

Community site session details

Computed field in View - Ambiguous column name

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Computed field in View - Ambiguous column name

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates