CODEWITHSUNDEEP
business-objects
IgorShch
IgorShch

Reputation: 159

BOBJ 4.1 Use values from two not joined tables in one pre-defined condition

I use a number of pre-defined conditions to compare the Date field from Trade table against GETDATE() function to work out the trades for this month, this year, previous year etc.

Now I need to create an additional table, where I will have a set of dates representing the start and the end dates for reporting periods, e.g. the start and end dates of current year reporting, the start and the end dates for previous year reporting etc.

This additional table and Trade table are not joined. In fact the additional table is not linked to anything.

I need to create a new set of pre-defined conditions where Date from Trade table will be compared from the values from the new dates table, i.e. I would like the new conditions to look like the following 

Trade.Date < ReportingDates.CurrentYearEndDate 
AND 
Trade.Date > ReportingDates.CurrentYearStartDate

The condition validates fine, but unfortunately I get the "The query cannot run because it contains incompatible objects. (IES 00008)" error" when I try to execute the condition.

Upvotes: 0

Views: 1080

Answers (1)

Joe
Joe

Reputation: 6827

You could use subselects in the predefined condition like:

trade.date between (select currentyearstartdate from reportingdates)
               and (select currentyearenddate from reportingdates)

Since reportingdates is not actually joined to anything, it does not need to be in the universe structure. This does have one disadvantage -- it's not obvious from the list of tables that it is actually needed in the universe.

Alternatively, you can create a derived table that joins trade and reportingdates as a cartesian product. The derived table would include all columns from both tables, so your condition would simply be (assuming the derived table is named DT):

dt.date between dt.currentyearstartdate and dt.currentyearenddate

The downside to this approach is that reportingdates is included in all queries, whether it's needed or not, and the SQL can be a little harder to read.

Upvotes: 1

Related Questions