Syntax Error in Database View when joining the kb_knowledge to kb_use table

[PAnewbie]

I am trying to create a database view that joins the kb_knowledge to the kb_use table. The purpose of this is for performance analytics. However, whenever I add kb_use and the where clause of kb.sys_id = use.article, I'm getting a syntax error.

Syntax Error or Access Rule Violation detected by database (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 'ON kb.sys_id = use.article ) 

Query is: SELECT count(*) AS recordcount FROM (kb_knowledge kb LEFT JOIN kb_use use ON kb.sys_id = use.article )

Syntax Error or Access Rule Violation detected by database (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 'use.`sys_created_on` AS use_sys_created_on, use.`user` AS use_user, use.`sys_id`' at line 1 Query is: SELECT kb.`sys_domain_path` AS kb_sys_domain_path, use.`sys_created_on` AS use_sys_created_on, use.`user` AS use_user, use.`sys_id` AS use_sys_id, kb.`number` AS kb_number, kb.`sys_domain` AS kb_sys_domain, kb.`sys_id` AS kb_sys_id FROM (kb_knowledge kb LEFT JOIN kb_use use ON kb.sys_id = use.article ) 

From what I can see, its because the tables are in parentheses after stating from, but this is done by the tool automatically, so not sure what I can do to fix this. Any help would be greatly appreciated. Please note I am not the Admin, I have the role of PA Admin and was given the capability to create views for reporting purpose, so I am not familiar with any configurations.

Below is a snapshot of the database view I created:

[Goran WitchDoc]

Hi,

The reason is that by some strange reason, you shall not have dots in your where clause. dots are replaced with underlined. So your where clause should be kb_sys_id=use_article.

//Göran

Feel free to connect with me:
LinkedIn & Twitter
Subscribe to my YouTube Channel
Buy The Witch Doctor's Guide To ServiceNow

[PAnewbie]

Hi Goran,

I actually tried this and I'm still getting the error. Please see below.

Error Message

Syntax Error or Access Rule Violation detected by database (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 'ON kb.`sys_id` = use.`article` ) SELECT count(*) AS recordcount FROM (kb_knowledge kb LEFT JOIN kb_use use ON kb.`sys_id` = use.`article` ) 

Syntax Error or Access Rule Violation detected by database (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 'use.`sys_created_on` AS use_sys_created_on, use.`user` AS use_user, use.`sys_id`' at line 1 Query is: SELECT kb.`sys_domain_path` AS kb_sys_domain_path, use.`sys_created_on` AS use_sys_created_on, use.`user` AS use_user, use.`sys_id` AS use_sys_id, use.`article` AS use_article, kb.`number` AS kb_number, kb.`sys_domain` AS kb_sys_domain, kb.`sys_id` AS kb_sys_id FROM (kb_knowledge kb LEFT JOIN kb_use use ON kb.`sys_id` = use.`article` ) 

I really think the issue is that the open parenthese is before the table, usually when writing a sql statement the parenthese should be after ON, but because its before the table, its assuming I have a subquery that its pulling data from, and in that case, what is inside the parenthese is not a complete sql statement since there is no select. But I don't know how to change this as the system automatically wrote the query. I just gave it a where clause in the tool.

[Goran WitchDoc]

Hi,

I have no clue how you managed to do it, but when I try to replicate your setup, it complains about "use" as a variable prefix since it's a reserved word and probably the reason why it isn't working for you. I did like this and it works:

//Göran

Feel free to connect with me:
LinkedIn & Twitter
Subscribe to my YouTube Channel
Buy The Witch Doctor's Guide To ServiceNow 

[reginabautista · Accepted solution]

Hi PANewbie,

Try changing the variable prefix for kb_use to a different one say kbu, then try again. Apparently "use" is a reserved keyword. 

I tested the below and worked perfectly in my PDI.