Database Views Variable Prefixes Underscores and/or Periods

[Ryan153]

I was building a database view and ran into the following error

"Syntax Error or Access Rule Violation detected by database (Unknown column 'r.u_resolved_by' in 'on clause')"

After checking on permissions and validating that the field was indeed part of the table I was running out of options. Then on a whim I changed the periods after the prefixes to underscores in the join and the view worked!

The view actually had a few other tables, and for standardization sake I switched the periods to underscores and the other joins broke?

Is there a rhyme or reason on when to use periods vs underscores in db views?

Thanks for responding

[Tony Cattoi]

I believe underscores are what should be used in database views.  From what I've seen all of the ServiceNow provided ones utilize underscores for the clause. 

The documentation also shows to use underscores for the syntax.  https://docs.servicenow.com/bundle/newyork-platform-administration/page/use/reporting/task/t_AddATab...

[Ryan153]

Hi Tony,

Yes I noticed all the documentation has the underscores, but for some reason for this view behaves very differently depending on the usage of periods vs underscores.

[ggg]

you don't use a period .. you use an underscore ...

so it's the prefix _ field name

r_u_resolved_by

[Ryan153]

ggg,

Thanks for the reply, however using the prefix_ throws the error "Syntax Error or Access Rule Violation detected by database (Unknown column [field] in 'on clause')" when I change all of the fields to that model.  That is why I am so confused as to why it appears that prefix_ is required sometimes and prefix. works in other cases.