Incorrect Syntax

Epicor ERP 10
1

I keep getting an incorrect syntax from this sum(orderdtl.DocExtPriceDtl) in building a BAQ. But AVG works fine. Ideas as to why?

2

Is there a lot of stuff in the query? It’s possible the problem is somewhere else. I’ve had it happen to me where if I change/add certain things it fails on some things but not others. The problem ended up being from somewhere else though.

3

Yeah, its a little hairy. I need to pull in every dropship made in 2017 to AZ and give an sum of the dollars spent by the customer. Trying to get the sum to work is a pain. And it is the only calculated field. I am using Cust, orderdtl, shipdtl,shipto, and shiphead.

4

Does it run fine without the calculated field?

5

yes

6

slow, but it runs!

7

Without clicking around in the actual BAQ, I don’t have any specific ideas. If it were me, I would build it up and test piece by piece until I find the problem. Not a great solution, but it’s all I got.

1 Like
8

Yeah, i deconstructed it and am rebuilding it now to see if I can find the error. Wish I didn’t have to pull form so many tables as it makes it run a lot slower.

9

That shouldn’t be too bad, as long as you have your joins and your filters set, I don’t think it would take too long to run. All of those tables have the standard joins built in right? (Epicor fills in the connection when you join them)

How many lines does the un-grouped query return?

10

Where are you getting the drop ship from? That usually is in the PORel table. I don’t see it in the other tables that you listed.

I found it also in the OrderRel table. Do you have that one in there too?

11

I was using the ShipTo table and joining it with ShipDtl or ShipHead, either will work. Then joining all of htat with OrderDTl and then Customer. I didn’t think of the OrderRel table. That may be better.

12

Neither of those have a DropShip field. How are you determining if your order was drop ship or not?

13

Ah, i see. Our drop ship, and this is how it was set up before I started, is the ShipTo’s. So dropship may be wrong way to use the word.

14

So you just want everything that was shipped to AZ in 2017. right?

Drop ship generally means that you buy it from an vendor, and they send it directly to your customer, so it never arrives in your facility.

I think that you can do what you want with just ShipDtl, ShipHead, ShipTo and Cust tables. I think the orderDtl isn’t necessary. The prices show up in the ShipDtl table, so you should be able to sum by those.

15

Try this one. You’ll have to change the state in the filters, (I have MN in there) but it should be what you described. I had to add the InvcDtl table, because for some reason all of the price fields in ShpDtl showed a 0.

Sum by Customer filter by state and date.baq (43.4 KB)

1 Like
16

Thanks! That was very close to what I had after I removed the OrderDtl. And it worked. Much appreciated.

17

Sales Tax reporting, right?

Epicor does have an easy way to do this but users don’t like it for some reason. Even though many manufacturing companies pay no sales tax in the US, they still have to report what they’ve shipped into states (or other countries). If you create a tax code for each taxing authority and assign it at the sales order then all you have to do is run the built-in Tax Report and you’re done. Our codes were in the format: “MI00” for the entire state of Michigan and “MIGR” for Grand Rapids. That way, you can get city and state information easily.

Food for thought,

Mark W.