Integration using database view

[shashuma]

Hi all,

I'm currently doing integration between ServiceNow and MS SQL database. Trying to retrieve SN data and update the SQL db.

The data in SN will be coming from multiple tables. I've used the database view to join all the tables together and return the API of the database view.

Database view in ServiceNow doesn't provide much filtering, so I've run into the following issue.

I need to join 5 tables, but to simplify the question, that's say I'm only joining 2 tables.

TableA                                      

cost type(reference field)	table type	job number
Farm	cost_breakdown	JOB01
Agency	cost_breakdown	JOB01
Service	cost_breakdown	JOB02
Direct	cost_breakdown	JOB03

TableB

table type	cost type(string)	rank
cost_breakdown	Farm	2000
cost_breakdown	Others	3000

I need to return the following columns, job number and rank

Job number	rank
JOB01	2000
JOB01	3000
JOB02	3000
JOB03	3000

Join the two tables, if the cost type = 'Farm', rank will be 2000, otherwise, rank will be 3000.

As the cost type in TableA is a reference field, the join i had didn't work.

My where clause: tableA_table_type=tableB_table_type&&tableA_cost_type=tableB_cost_type

What have I done wrong here?

As explained in the beginning, all I'm trying to achieve is to pass data from multiple tables in SN to a single MS SQL db table. What other options do I have? I don't want to go with Linked Server approach.

Thanks in advance,

June

[Chris M3]

Yeah, the issue would be in your reference join to your string field.

I don't think you can dot-walk in the where clause, but you could try that.

More likely, you need to add the referenced table to your database view

so something like

table_a_cost_type = ref_table_sys_id

Then

table_b_cost_type = ref_table_display_field