DATABASE VIEWS* for beginners.

[ktillman]

DATABASE VIEWS* for beginners.
Hello all,
Does anyone have any database view best practices? I understand them yet it is hit or miss when I join tables for reporting purposes.(I'm throwing darts in the dark)
****
My questions are:
1)What should I attempt to join between two tables to get a db view of that join? (ie a field in common between the two tables?SYSID to SYSID?)
2)Table order. Does it matter?
3)Where clause: What is allowed? What isn't allowed? Appears any syntax is allowed regarding logic (ie || && = ==)
4)Are there any detailed examples on the web I can use? Google: "Servicenow Database view" even google can't find much but a single you tube video and a knowledge article from servicenow.com Other than those there is nothing explaining this feature.

thanks!

Kimani

[Daniel Draes]

Hi Kimani,

database views are in essence just joins of tables. So if you lookup SQL Joins in google you will find enough hits (e.g. http://www.w3schools.com/sql/sql_join.asp).

If you understand the underlying concept it probably gets a lot easier to work with views.

To your questions:

1) Always the correlating fields. Examples: incident.sys_id -> sys_user.sys_id will not give you any useful data. incident.caller_id -> sys_user.sys_id will give you incidents incl. the full details on the user record of the caller.

2) Table order is important for performance reasons. If you working with smaller instanced most likely this has not to much of relevance.

3) Couldn't find to much documentation on this either, just http://wiki.servicenow.com/index.php?title=Database_Views which does not outline to much on the conditions. Unfortunately.

4) Anything in particular that you are looking for ?

Daniel

[ktillman]

Hi Daniel thanks for your response.
I am reading though the links you shared. These are very enlightening. Armed with this I would consider this thread closed.
To answer you I wasn't looking for anything inparticular in my current instance of SN. Moreso looking for a best practice as to the methodology I should take when joining two (or more) tables.
You've given me something to digest. I am sure question my come from my reading but for now this is great!
thanks again!
Kimani

[amadosierra]

Be careful with the aliases you give to the tables in the join. Make sure you're not using a MySQL reserved word as a situtation like this may not be that obvious or reported like that.
For the list of reserved keywords:
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

/Amado Sierra

[ktillman]

Thank you Amado. I will consider this. I made a join successfully just yesterday. It was a join of 5 tables so that survey data returned from and the tech assigned to that ticket that a survey was performed against could be merged. This way if a tech gets a low survey we have data to follup up and accountability so the tech can give better service.