Problem
With Dynamic Query Mode (DQM), the message is presented due to promptName being referenced in the Filter expression, for example:
case ?dateFilter?
when 0 then (
(
[BeginingDate] <= ?endDate? AND
[EndingDate] >= ?startDate? OR
[EndingDate] is null
)
)
when 1 then ( … )
when 2 then ( … )
when 3 then ( … )
when 4 then (
(
[BeginingDate] <= cast(cast(extract(year;?startDate?);VARCHAR(4))+'-01-01';DATE) AND
(
[EndingDate] >= cast(cast(extract(year;?startDate?);VARCHAR(4))+'-12-31';DATE) OR
[EndingDate] = 2999-12-31 OR
[EndingDate] is null
)
)
)
end
Cause
DQM attempts to bind the ?startDate? prompt parameter to a data type, however ?startDataum? resolves two conflicting data types:
1) [EndingDate] is a Query Item with its data type of DATE therefore ?startDate? resolves to Date.
2) The second argument of the extract function accepts a DateTime and therefore ?startDate? resolves to DateTime/TimeStamp.
With Compatible Query Mode (CQM), for 1) it is the same as DQM and for 2) the ?startDate? is resolved to a Date.
If there was no prompt page, and if the filter expression was defined as:
[BeginingDate] <= cast(cast(extract(year;?startDate?);VARCHAR(4))+'-01-01';DATE)
with DQM, the user would be prompted with a DateTime prompt where the user can enter date and time values. With CQM, the user would be prompted with a date prompt where the user can only enter a date value, no time value is allowed. DQM is therefore the preferred approach.
Resolving the problem
To avoid the DQM message, a Prompt Macro can be used to bind the parameter to a specific data type.
In this case, #prompt('startDate','date')# and the filter expression will therefore be:
case ?dateFilter?
when 0 then (
(
[BeginingDate] <= ?endDate? AND
[EndingDate] >= #prompt('startDate','date')# OR
[EndingDate] is null
)
)
when 1 then ( … )
when 2 then ( … )
when 3 then ( … )
when 4 then (
(
[BeginingDate] <= cast(cast(extract(year;#prompt('startDate','date')#);VARCHAR(4))+'-01-01';DATE) AND
(
[EndingDate] >= cast(cast(extract(year;#prompt('startDate','date')#);VARCHAR(4))+'-12-31';DATE) OR
[EndingDate] = 2999-12-31 OR
[EndingDate] is null
)
)
)
end