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:
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.
