Calculated Field Error – Ambiguous Column Name
Jay asked 4 years ago
Smartlist – Sales – Receivables Transactions : Join Sales Transaction smartlist.
Creating calculated field using both smartlist data and getting an error for the field that exist in both smartlist, like “Discount taken Amount” field. here is an example Calculated field : {Receivables Transactions:Sales Amount} – {Receivables Transactions:Applied Amount} – {Sales Transactions:Discount Taken Amount} – {Receivables Transactions:Write Off Amount}
ERROR : There is an error in the calculated field [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name ‘DISTKNAM’.
Creating calculated field using both smartlist data and getting an error for the field that exist in both smartlist, like “Discount taken Amount” field. here is an example Calculated field : {Receivables Transactions:Sales Amount} – {Receivables Transactions:Applied Amount} – {Sales Transactions:Discount Taken Amount} – {Receivables Transactions:Write Off Amount}
ERROR : There is an error in the calculated field [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name ‘DISTKNAM’.
Answers
Patrick Roth Staff answered 4 weeks ago
Jothi,
A little late here but ….
How did you define the 2 fields in the calculation?
We can see here in your SQL (vs the original post user) that the 2 fields in the calculation aren’t prefixed by the table alias. And so in this case it fails due to having the fields in 2 sql tables.
In the calculation in SLB, it should be
{Project Table 1: PA Receipts Amount} + {Project Table 1: PA Actual Receipts Amoun}
(or however it comes out using the proper fields from the Fields list)
That way SLB can take these “tokens” and replace it with the proper alias to come out to be:
T1.PA_Receipts_Amount + T1.PA_Actual_Receipts_Amoun as [Total Receipts]
(or T2 or whatever other table you wanted to pull from)
patrick
I have the same issue when you join the same table . Joined PA01201 on the PA Projects report and it errors on calculated field PA_Receipts_Amount + PA_Actual_Receipts_Amoun as [Total Receipts].
eOne – can u pls advise.
“select
PA_Receipts_Amount + PA_Actual_Receipts_Amoun as [Total Receipts]
from (select * from PA01201 with (NOLOCK)) T1
LEFT JOIN [PRO]..PA61040 T2 with (nolock)
on
T2.[PAPROJNUMBER] = T1.PAPROJNUMBER
LEFT JOIN [PRO]..PA01409 T3 with (nolock)
on
T3.[PAPROJNUMBER] = T1.PAPROJNUMBER
LEFT JOIN [PRO]..PA01201 T4 with (nolock)
on
T4.[CUSTNMBR] = T1.CUSTNMBR and T4.[PACONTNUMBER] = T1.PACONTNUMBER and T4.[PAPROJNUMBER] = T1.PAPROJNUMBER
”
Thanks,