Getting Error in BAQ - Conversion failed when converting the nvarchar value '#AUG2017' to data type int

shobdy · January 5, 2023, 10:19pm

Hello,

I am trying to create a BAQ that joins APInvHed to an innersubQuery ‘AddTotals’.
When I join them by InvoiceNum and add a field, I get the error:

Ice.Common.EpicorServerException: Conversion failed when converting the nvarchar value '#AUG2017' to data type int. ---> System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value '#AUG2017' to data type int.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Ice.Blaq.Execution.PagedReader.Read() in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\PagedReader.cs:line 178
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at Ice.Blaq.Execution.QueryExecutor.LoadTableRows(IDataReader rdr, DataTable table, Int32 offset, Int32 rowsToLoad, Int32& rowsRead) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 464
   at Ice.Blaq.Execution.QueryExecutor.PrepareSimplePage(IDataReader rdr, IDbCommand cmd, DataTable table, QueryInfo queryInfo, Int32 offset, Int32 rowsToLoad, Boolean needTotal, Boolean isSearchPaging, Action`1 readerIncrement) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 414
   at Ice.Blaq.Execution.QueryExecutor.ProcessResults(IDataReader rdr, IDbCommand cmd, DataTable table, QueryInfo queryInfo) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 312
   at Ice.Blaq.Execution.QueryExecutor.FillResultToResultsTable(IDataReader rdr, IDbCommand cmd, DataTable resultTable, QueryInfo queryInfo, Action`1 afterFillResultTable) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 248
   at Ice.Blaq.Execution.QueryExecutor.FillResultDataset(IDataReader rdr, IDbCommand cmd, DataSet resultDs, QueryInfo queryInfo, Action`1 afterFillResultTable) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 181
   at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action`2 perfLogger) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118
   at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37
   at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func`2 theJob) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
   --- End of inner exception stack trace ---

If I remove the join to the subQuery, the BAQ runs.

How do I stop the join from converting to INT?

Any help is appreciated!

Doug.C · January 5, 2023, 10:27pm

What table(s) are in your inner subquery?

shobdy · January 5, 2023, 10:31pm

I joined Erp.APInvDtl(TotalAPInvDtl1) to Erp.InvcTax and created a couple of calculated fields.

All display fields are:
TotalAPInvDtl1_Company
TotalAPInvDtl1_InvoiceNum
TotalAPInvDtl1_InvoiceLine
Calculated_sumTaxes
Calculated_LineTotal

The join between the two tables is:
All rows from TaoalAPInvDtl1
Company = Company
InvoiceNum = InvoiceNum
InvoiceLine = InvoiceLine

Do you need the calculations also?

Doug.C · January 5, 2023, 10:32pm

You’ll want Erp.APInvTax for AP … Erp.InvcTax is the AR side which has integers for InvoiceNum

Banderson · January 5, 2023, 10:35pm

On the join you can type in there, so you can actually cast the value to a string. Sometimes the system assumes a type based on the data, and it’s not always right.