BAQ using table aliases with a dash - how to address in a calculated field?

Kinetic ERP
1

Typically I would think when using aliases/names that have special characters, you would escape with brackets: []
But in doing that, calculated fields don’t seem to respect the []
my calculated field is this:
KitToFG.PartMtl-Kit_MtlPartNum or SubQuery.TableAlias_Field generically
i tried to escape with [KitToFG.PartMtl-Kit_MtlPartNum], am i missing some funky syntax? :slight_smile:

2

Couple things.

  1. Just don’t use special characters. It’s not necessary so why do it?
  2. Don’t the brackets go around the field names? Not the table and the field name?
  3. I believe they are automatically bracketed in the back-end anyways so I don’t think it will cause an issue.

Now I haven’t tested it, but is it giving you an error? Or are you just anticipating a problem?

2 Likes
3

Got it!!
This syntax works after all, just required me reloading the BAQ designer several times:
[PartMtl-Kit].[QtyPer]

1.) Sure, but this is a query already written with dashes all over the various subqueries it consists of, so understanding how to utilize the dash in an alias would be the path of least resistance to modification. Also, Epicor states you can use a dash (even though it goes against SQL best practice dating back to forever) - so I wanna know how! :wink:
image
3.) i would have thought it auto-bracketed in the backend as well, but it does not in a calculated field, per the Query Phrase viewer and error kicked out:
SUM(PartMtl-Kit.QtyPer)

Invalid column name 'PartMtl'.
The multi-part identifier "Kit.QtyPer" could not be bound.
No column name was specified for column 8 of 'KitToFG'.

2.) that was the first syntax I tried, but also no-go:
Syntax that would be acceptable in most SQL:
SUM([PartMtl-Kit].[QtyPer])

Column 'Erp.PartMtl.PartNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
No column name was specified for column 8 of 'KitToFG'.

It looks like this syntax should work when viewing the query phrase in BAQ Designer, the first row is added by the designer itself when selecting a column, the second row is my calculated field.

	[PartMtl-Bulk].[QtyPer] as [PartMtl-Bulk_QtyPer], 
	(SUM([PartMtl-Kit].[QtyPer])) as [Calculated_dashtest]

The BAQ returns normally when removing the calculated field Calculated_dashtest.

Per this post, the syntax i’m attempting to use should work, however the hyphen is located within a field name there, whereas mine is a table alias, perhaps it’s a bug? BAQ Calculated Field with Hyphen in Column Name - #4 by CSmith

To the last question regarding if it throws an error when the syntax is as used above, I used that syntax because the column is present in the same subquery and aliased, so i tried to reference it using the alias.

SUM([PartMtl-Kit_QtyPer])

SELECT
....
	[PartMtl-Kit].[QtyPer] as [PartMtl-Kit_QtyPer],

However, this results in the error:

Invalid column name 'PartMtl-Kit_QtyPer'.