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

[Ryan153]

Update:

It appears that the usage of View Fields will impact the ability to use underscores vs periods. I went back and added all of the fields that were in Where and Join criteria to View Fields list for each table. I was then able to use underscores for all of the Where and Join statements.  

[Lennart]

Can someone explain why Servicenow uses _ (underscore) instead of . (dot) as separator between table and field?

Normal SQL syntax is [table_name].[field_name] and you often use _ in both table names and field names - like "first_name" og "change_request".

Both . and _ seems to work for me though, but i'm puzzled why the documentation dictates _ as the separator.

[VaranAwesomenow]

Had similar issue, when using _ it wasnt working since column name was calculation_complete

mi_id = inc_sys_id && mi_definition = 'XXXXXXXXXXXXX' && mi_calculation_complete=true

was getting following error

Syntax Error or Access Rule Violation detected by database ((conn=1276255) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') WHERE inc.`sys_class_name` = 'incident' /* questdev001, gs:D1EAC118DBED5850D5' at line 1)

Fix - Replaced _ with . (dot) throughout.

New join query = mi.id = inc.sys_id && mi.definition = 'XXXXXXXXXXXXXXXX' && mi.calculation_complete = true

It worked fine.