BAQ Linking UD Table

Kinetic ERP
1

So… I’m just trying to do a very simple BAQ linking a UD Table where I’ve stored a bunch of historical legacy system Sales Order data.

UD01.Number03 is a number (decimal) of our Customer Account Number.
Customer.CustID is a nvarchar

I’ve seen posts where I can CAST one or the other to get them to link up, but nothing seems to work.

What’s odd is… a straight Number03 = CustID… fails:

image
image979×228 89.2 KB

…but, by simply adding a Table Criteria and filtering by a specific CustID… the query works:

image
image990×290 107 KB

I can’t explain why “filtering” the Customer table with a specified constant would provide results where unfiltered it claims it is a bad SQL statement.

The problem is, I don’t want to query one CustID at a time… I need to see all, so filtering (although works) isn’t the goal.

Any tips/tricks to get this to work?

2

@dcamlin Number03 might work to CustNum if you had that, but not CustID since it is a character field.

In the join use convert(nvarchar,UD01.Number03) = CustID

If you had CustID as one of the 5 key fields then it would already be an string field.

3

Cast the decimal to int in the join.

image

image

EDIT: just noticed you’re trying to join the UD.Num field to CustID, which is a string field. Are all of your CustID’s numeric? If not, you gotta handle that in the table filters or your query will blow up.

Better solution is to use a UD.Char field to store your CustID foreign key instead. Match the system data types, not just whatever business practice your company follows today.

1 Like
4

I vaguely recall an Epicor Idea of adding additional UD table fields that are integers (but still keeping Number01 also). Seems like a common need (like here).

5

The Customer Account isn’t a key field. The legacy data is based on Sales Orders, so the keys are based on order number, line, release, etc. Customer account is just a field on the record.

Converting the Number03 to nvarchar resulted in an okay SQL statement, but for some reason resulted in 0 rows.

Natively, the BAQ will join the Number03 and the CustID… (without converting/casting)… but only if the CustID is filtered. That’s why I’m trying to determine why that is the case. Is it converting the CustID to a different “type” when applying a specific constant filter?

7

Looks like you can add a UD column to a UD table also and define as integer not decimal.
Never thought or needed to try, but out of curiosity I just did.

8

Yes, the “constant” thing in BAQs is intelligent to convert 127 to a string if that’s what the field needs.

9

Yes, I’ve attempted to cast both sides of the join to the same thing… but not getting any results unless I add the filter.

So, even if I cast both values to integer it won’t produce results… unless I add the filter, and then it will give results. I just can’t find a way to do this “unfiltered”.

Maybe I’ll have to wipe my UD table and start over by importing the account numbers as strings.

10

Well I’ll tell you what, I am at a loss here. I was going to explain something and then ran into some weirdness.

Number01 (etc.) in all my UD tables are decimals (as we’ve discussed at length already), yet come across as single-digit integers. It’s preventing me from entering more digits into the field in an updatable BAQ. I can change the output format, but why is the BAQ being all funky?

image
image847×479 13.8 KB

11

So, unrelated to my problem…

Perhaps you should cast Number01 to an Integer and then cast it to a string. It may be retaining .0000000 at the end or something.

12

Not sure how to cast twice. What’s the correct syntax for that?

13

CAST(CAST(Number03 as int) as varchar)

14

I think @JasonMcD has the idea. stuff is int then varchar, stuff2 is just varchar

image

In SQL you just wrap them up and they resolve from the inside out.

Cast(Cast(UD01.Number03 as Int) as nvarchar)
15

That did it! I think you’re right, the trailing zeros if casting directly to nvarchar must have been getting in the way.

Can’t thank you guys enough for the help!

2 Likes
16

Setting the UD Table as the 1st Table in the TopLevel query and then using a Right Outer Join seems to work. I am joining UD11 with Key2 (PONum) to POHeader. Key2 is cast as an integer. Returns all POHeader records without having to use a filter.