BAQ Concatenate - Syntax OK, but Test Error

Epicor ERP 10
1

Hello,
I am very new to Epicor, BAQ and Calculations.
I created a Calculated Field to show PartNum_PONum_PO$ in one field. I checked the Syntax within the Calculated Filed SQL Editor and Analyze and the Syntax is OK. However, when I run Test, I get the following error:
Severity: Error, Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 7866.6191 ms.

My calculation looks like this: (Part.PartNum+‘‘+OrderHed.PONum+’’+OrderHed.DocTotalCharges)
My data type is: nvarchar

Any thoughts on why this is happening?

2 
CONCAT(Part.PartNum, ' ', OrderHed.PONum, ' ', OrderHed.DocTotalCharges)
3

Your syntax might work, but that’s not how I do mine in a BAQ. For example, jobs created from our orders use the naming convention of ‘00’+ OrderNum + ‘-’ + OrderLine + ‘-’ + OrderRel

In trying to connect, I’m looking for something that starts with 00 then has an order number followed by - and then I don’t care what comes next:

image
image1183×126 3.47 KB

Using LIKE instead or =
CONCAT (‘00’,OrderHed.OrderNum,‘-’,‘%’)
single quotes around the strings, no quotes around the field name, and then quotes around the % wildcard character.

Hopefully you can pull something useful from that.

edit Looks like Aaron beat me to it.

4

Your suggestion worked: Concat(Part.PartNum,‘','PO ‘,OrderHed.PONum,’’,‘$’,OrderHed.DocTotalCharges)
The only small issue is with the currency. Instead of seeing $37,112.00, I see $37112.000.

5

Thank you for your input!

6

Give this a blast.

CONCAT(Part.PartNum, ', PO ', OrderHed.PONum, ', $', FORMAT(OrderHed.DocTotalCharges, 2))
7

I get: “Argument data type int is invalid for argument 2 of format function.”

8

My apologies

CONCAT(Part.PartNum, ', PO ', OrderHed.PONum, ', $', CAST(OrderHed.DocTotalCharges AS DECIMAL(18, 2)))
9

Works! Only thing missing is the commas. I have some six figure+ orders, so the commas are important.

10

I would use FORMAT:

FORMAT(OrderHed.DocTotalCharges, 'C', 'en-us')

It produces this: $37,112.00

CONCAT(Part.PartNum, ', PO ', OrderHed.PONum, ', ', FORMAT(OrderHed.DocTotalCharges, 'C', 'en-us'))
2 Likes
11

Brilliant!
I ended up with two $$. but corrected it by deleting the dollar sign only. Here’s the final code:

CONCAT(Part.PartNum, ', PO ', OrderHed.PONum, ', ', FORMAT(OrderHed.DocTotalCharges,‘C’,‘en-us’))

Thank you Anthony!