Additional WHERE Clause in Data Model Properties
|
|
Additional WHERE Clause in Data Model Properties
|
If you do not see how to define static relations between the tables of a data model, you can use an additional WHERE clause in its properties. For example, you do not want to join the tables in a query, it is merely required to check the existence of equivalent rows in two tables. Then you will have to create a subquery using the EXISTS construction. The syntax will be the following:
and Exists(Select * From <#<table_name.Data_Source>#> alias Where alias.Field1=alias1.Field1 ...)
To set references to the tables that are not included in the data model use the construction <#<table_name.Data_Source>#>. For the tables of the data model use their aliases only!
For example, you do not want to join a GOODS table in a query, but the data should be retrieved relating to the goods existing in the GOODS table only. The additional WHERE clause will be:
and Exists(Select * From <#<GOODS.DSP>#> b Where d.GOODS=b.CODE)
|
d |
– the alias of INVOICE_ITEMS |
|
d.GOODS |
– a field with goods codes in INVOICE_ITEMS |
|
b.CODE |
– the key field in the GOODS table |