i am trying to create a database view to show records from table a but not exists in table b using left join

[Srinivasa Rao]

Hi,

I have two tables with similar column names and I need to return records from the left table which are not found in the right table

I know how to write this in SQL Server but not familiar with ServiceNow Left Join any help would be really appreciated..

SELECT a.*
FROM TableA a

LEFT JOIN TableB b

ON b.col1 =a.col2

WHERE b.col1 is NULL

What i need in ServiceNow is

SELECT a.*
FROM Change_Request a

LEFT JOIN sysapproval_approver b

ON b.sysapproval =a.SYS_ID

WHERE b.sys_id is NULL

Please advise...

Thanks in advance..

[Srinivasa Rao]

sorry i didn't get the results what i want.

When i change my Left Join to sysapproval_approver then i get a ton of other records and still missing my expected results...

What I need is Change Request records which are in State = 'Authorize' and without any approver assigned to change request...

[gyedwab]

If in future you want to define these kinds of reports without building a database view, you can use Explore Analytics to compare records on any two (or more) tables using the Mash-Up capability. It looks like this:

1) Create a report with the Approvals, grouped by the associated change request:

(note: I relabelled the 'Approval For' field to 'Number' just for this report, for the mash-up to work)

2) Create a report with all of the records on the change request table:

3) Create a 'Mashup' view by selecting those two reports:

Now you'll have a report that shows all of the changes, with the number of approvals (if any):

With a right-click, you can filter on only the records that have no approvals:

Now you can see any of the records on table A (change_request) that are not on table be (sysapproval_approver).

This report is live, drillable, and can be embedded in ServiceNow or any non-ServiceNow portal.

[Srinivasa Rao]

Hi Guy Yedwab,

I have tried all my options with database views, unfortunately i was not getting correct results..

i am going to try Explore Analytics..

Could you please give more details, how to i make sure my instance has   Explore Analytics activated and steps to create reports as you suggested, how different is that from creating a regular report...

Please advice...

Thanks,

Srini

[gyedwab]

Hi Srinivasa,

Explore Analytics is an integrated solution that can get you more advanced live reporting for ServiceNow. It allows you to combine data from multiple tables (like the example above), do real-time calculations, connect to other sources of data, etc. that ServiceNow doesn't provide.

To try it, you can go here to sign up for a free trial, and then follow these steps to connect to ServiceNow. Basically, all you need to do is apply the update set to ServiceNow and provide a login account for the connectivity.

Once you've done that I can send you an email directly with the step-by-step on the reports I've shown above.

[Srinivasa Rao]

Please send me the step-by-step on the reports to this email   jandres@align.com and syadlapalli@align.com

Thanks