Hi,
I am not sure if this is possible using conditional where clauses but am looking for a way to conditionally join tables in a database view. Under certain circumstances I am looking to join 2 tables, otherwise it would be a join of 3 tables.
If the asset.asset_type is not simple, then join the 3 tables (asset, m2m, expense)
if the asset.asset_type is simple, then only join 2 tables (asset, expense)
The conditional where clause for expense is
asset.asset_type!='simple'&&expense.sys_id = m2m.expense_line||asset.asset_type='simple'&&expense.asset = asset.sys_id
The conditional where clause for m2m is
asset.asset_type!='simple'&&m2m.asset=asset.sys_id
Which works for the case of asset.asset_type!='simple', but does not work for asset.asset_type='simple'.
This is expected because asset.asset_type='simple' would have no records. So I thought I would try to have all matches in m2m in that case and modified the the conditional where clause for m2m to be
asset.asset_type!='simple'&&m2m.asset=asset.sys_id||asset.asset_type='simple'&&m2m.sys_id!=-1
However, this just froze up when viewing the list, though no error appeared.
Is this possible?
