Database view not limiting results

[gjz]

I've created a simple view for reporting use in our data warehouse on the Hardware (alm_hardware) table.  Only assets that are "In Use" should be in the view, but it's not limiting the assets and I can't figure out why (we aren't using life cycle yet).  Can someone help me understand why the where clause isn't working?  The view currently only has one table in it - Hardware.  TIA!

View:

Results when clicking "Try it":

[gjz]

It turns out a single = works, I just didn't work until I joined my second table, then it worked.  I'm not sure why that matters, but it fixed the issue.

[AndersBGS]

Hi @gjz ,

why creating a database view when you’re not joining two tables?why not just create a basic report according to your needs?

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

Best regards

Anders

[gjz]

Hi AndersBGS,

I am joining four tables in the view, but I always add them one at a time and validate I'm getting the data that I expect.  Since I knew I only wanted assets that are "In use" I didn't want the entire asset table of data when I only needed a subset, which is why I applied a filter in the where clause and was surprised it didn't work.  I come from a SQL/Oracle database background and am used to much more robust scripting than ServiceNow's database views allow.  As I noted, once I added the second table the filter did apply - which doesn't make sense to me, but that's how it worked.

And for your second question, the view is for a REST API to load the data into a data warehouse where much more robust reporting is available.