Help Creating a Database view

[Alex319]

Hi All, 
I'm trying to create a database view to link a custom cmdb table and the case table. 
When I add the cmdb table to the database view and click on 'try me' the table shows no records. 
The purpose of this database view is to create a report which can look at an asset and see if there are any cases related to that asset. 

Any help would be greatly appreciated

Thanks

Alex

[Siddhesh Wani1 · Accepted solution]

Hi Alex 

The where clause doesn't support the dot walking. You have to think about the correct condition 

Something unique on the both table and write the query like below

here com is company and checking the user's company and give the correct data. 

Both field should are referring to the same sys_id.

[Alex319]

Hi Siddhesh, Removing the .sys_id does show some records now. I will do some testing to confirm if all issues are sorted. 
Thanks

Alex

[Siddhesh Wani1]

Please mark the answer as solution accepted.

Thanks and Best regards 

Siddhesh

[Alex319]

Hi Siddhesh, 
It looks like only some of the records are appearing on the table. 
case has 120 records and only 7 are showing up. (110 cases have asset populated). 
Any suggestions as to why this would be the case?

Thanks
Alex

[Siddhesh Wani1]

If you want the records of the specific asset then you can join the all those 3 tables 

1) Asset 

2) sn_customerservice_case

3) u_cmdb_ci_****

Asset	100	as	as_sys_id = '005b1c08471e3d94fa51e929736d439f'
sn_customerservice_case	200	ca	ca_asset = as_sys_id
u_cmdb_ci	300	pp	ca_asset = pp_asset

try this and let me know

Please mark the answer as solution accepted.

Thanks and Best regards 

Siddhesh