Can we create custom views with sql - i want to write a little bit different sql

[ashwinipingle]

Hello,

I wanted to write custom SQLs which are not simple joins. This SQL will be join of pm_project and another custom table. I need to do union ALL with multiple sqls. Is there a way where i can write such SQL views (the way we would in a standard SQL database) ?

What i need to do is to transpose few rows of the custom table into columns and then join with pm_project to have this view.

#SPM 

[Bert_c1]

Hi @ashwinipingle 

The way you do that in Servicenow is using Database Views. There is no direct SQL access to the database. There was a long time ago, caused too many problems as customers would cause data loss and other problems.

[ashwinipingle]

Hello Bert,

Even with views, I am trying to find sysparams where i can filter by column value = year - from sysdate.I could not find how to achieve this. I would want to have views for 10 years (one for each year) starting with year of sysdate. I could not find a way to do it. Each year, the views will slide to next 10 years.

Best Regards,

Ashwini Pingle 

[Bert_c1]

Hi,

See:

https://developer.servicenow.com/dev.do#!/reference/api/utah/server_legacy/c_GlideDateTimeAPI?navFil...

And the methods 'getYear()', 'getYearLocalTime()', 'getYearUTC'.

to determine the year from a GlideDateTime field.

As far as querying a table's DateTime field for specific year values, I believe that will require script logic.  Build start and end values as yearValue+"-01-01 00:00:00" and yearValue+"-12-31 23:59:59".

Maybe other Community members have a better approach.