Database View Where Clause Confusion

[brandon_chamber]

Hello,

I'm trying to setup a very basic database view of the Service Catalog table (sc_cat_item). However, I'd like the view to contain only active items. When I try to setup the view, add the view table and setup a Where clause (cat_active = true), the resulting view returns all records in the table. Why is this? This seems like a simple enough "filter" clause....


Thanks,

Brandon

[nielsonlee]

I am also having the same issue.   Could this be a bug and still not yet resolved in jakarta?   Another workaround I've tried is using a Module with filter condition.   I think this is a more efficient workaround compared to business rule approach on large size views containing transaction data.   I believe Module is optimized to filter records in batch mode unlike in business rule where it validates a single record at a time. The business rule workaround will work fine on small size views - reference table type.

[Nick McKeel3]

I had a similar issue around database views. I found that if I tried to put my 'active=1' condition on the top most table in my view, it did not work. however, if i moved the condition to the second table in my view, everything works fine.

See what I mean? This view gets all active work orders with category="Request"

[GerardP27524936]

I got this working on a choice (TRUE/FALSE) field:

(cirel_child=eaapps_sys_id || cirel_parent=eaapps_sys_id) && eaapps_u_critical_application=1

The text in black joins two of the same tables with an OR condition.  The text in red returns only TRUE values for the critical application field.

Took a lot of tries - I'm rapt.

[Ryan153]

Another approach you can try is to join back to the main table with a higher order and put the Where criteria in that table. Appears to have worked for my implementation.

Ex.

Table A Prefix:A Order:100 Join Type: Blank Where:Blank 

Table B Prefix:B Order:200 Join Type:Left Where:A.[field] = B.[field]

Table C Prefix:C Order:300 Join Type:Left Where:B.[field] = C.[field]

Table A Prefix:A2 Order:400 Join Type:Blank Where:A.sys_id = A2.sys_id && A2.[field]='[value]'