I am using BIRT Spreadsheet Designer with an EXASOL database, and have a problem with an SQL data set query failing, only when using parameters, although a similar query has no problems, which I believe could be a bug with BIRT Spreadsheet Designer's handling of parametrs in SQL queries. Though I would love to be wrong
<br />
<br />
This query is fine,<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT CUR, PREV, (CUR - PREV) AS CHANGE
FROM (SELECT COUNT(DISTINCT XPT.CONTESTANT) AS CUR
FROM PREMIUM.XP_TRANSACTIONS XPT
WHERE XPT.ACTION_CREATED_TS < ADD_MONTHS(:PeriodFrom,-3)
AND XPT.CONTESTANT NOT IN (SELECT DISTINCT XPT2.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT2
WHERE XPT2.ACTION_CREATED_TS BETWEEN '2011-07-03' AND '2011-10-03')
AND (XPT.CONTESTANT IN (SELECT DISTINCT XPT3.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT3
WHERE XPT3.ACTION_CREATED_TS BETWEEN '2011-10-03' AND '2011-10-10')
AND XPT.FEATURE='rankedmatch')),
(SELECT COUNT(DISTINCT XPT.CONTESTANT) AS PREV
FROM PREMIUM.XP_TRANSACTIONS XPT
WHERE XPT.ACTION_CREATED_TS < '2011-06-26'
AND XPT.CONTESTANT NOT IN (SELECT DISTINCT XPT2.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT2
WHERE XPT2.ACTION_CREATED_TS BETWEEN '2011-06-26' AND '2011-09-26')
AND (XPT.CONTESTANT IN (SELECT DISTINCT XPT3.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT3
WHERE XPT3.ACTION_CREATED_TS BETWEEN '2011-09-26' AND '2011-10-03')
AND XPT.FEATURE='rankedmatch'));</pre>
<br />
However, this query is not, with the only change being adding parameters to one line,<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>WHERE XPT2.ACTION_CREATED_TS BETWEEN '2011-07-03' AND '2011-10-03')</pre>
changed to<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>WHERE XPT2.ACTION_CREATED_TS BETWEEN ADD_MONTHS(:PeriodFrom,-3) AND :PeriodFrom)</pre>
<br />
So,<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT CUR, PREV, (CUR - PREV) AS CHANGE
FROM (SELECT COUNT(DISTINCT XPT.CONTESTANT) AS CUR
FROM PREMIUM.XP_TRANSACTIONS XPT
WHERE XPT.ACTION_CREATED_TS < ADD_MONTHS(:PeriodFrom,-3)
AND XPT.CONTESTANT NOT IN (SELECT DISTINCT XPT2.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT2
WHERE XPT2.ACTION_CREATED_TS BETWEEN ADD_MONTHS(:PeriodFrom,-3) AND :PeriodFrom)
AND (XPT.CONTESTANT IN (SELECT DISTINCT XPT3.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT3
WHERE XPT3.ACTION_CREATED_TS BETWEEN '2011-10-03' AND '2011-10-10')
AND XPT.FEATURE='rankedmatch')),
(SELECT COUNT(DISTINCT XPT.CONTESTANT) AS PREV
FROM PREMIUM.XP_TRANSACTIONS XPT
WHERE XPT.ACTION_CREATED_TS < '2011-06-26'
AND XPT.CONTESTANT NOT IN (SELECT DISTINCT XPT2.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT2
WHERE XPT2.ACTION_CREATED_TS BETWEEN '2011-06-26' AND '2011-09-26')
AND (XPT.CONTESTANT IN (SELECT DISTINCT XPT3.CONTESTANT
FROM PREMIUM.XP_TRANSACTIONS XPT3
WHERE XPT3.ACTION_CREATED_TS BETWEEN '2011-09-26' AND '2011-10-03')
AND XPT.FEATURE='rankedmatch'));</pre>
<br />
Whereas a similar query in the same spreadsheet works fine,<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT NEW_VS_CUR, NEW_VS_PREV, (NEW_VS_CUR - NEW_VS_PREV) AS CHANGE
FROM (SELECT COUNT(*) AS NEW_VS_CUR
FROM ((SELECT DISTINCT VMP.PLAYER_ID
FROM PREMIUM.VERSUS_MATCH_PARTICIPANT VMP
JOIN PREMIUM.VERSUS_MATCH VM
ON VM.ID=VMP.MATCH_ID
WHERE VM.CREATED_AT BETWEEN :PeriodFrom AND :PeriodTo)
MINUS
(SELECT DISTINCT VMP2.PLAYER_ID
FROM PREMIUM.VERSUS_MATCH_PARTICIPANT VMP2
JOIN PREMIUM.VERSUS_MATCH VM2
ON VM2.ID=VMP2.MATCH_ID
WHERE VM2.CREATED_AT < :PeriodFrom))),
(SELECT COUNT(*) AS NEW_VS_PREV
FROM ((SELECT DISTINCT VMP.PLAYER_ID
FROM PREMIUM.VERSUS_MATCH_PARTICIPANT VMP
JOIN PREMIUM.VERSUS_MATCH VM
ON VM.ID=VMP.MATCH_ID
WHERE VM.CREATED_AT BETWEEN ADD_DAYS(:PeriodFrom,DAYS_BETWEEN(:PeriodFrom,:PeriodTo)) AND :PeriodFrom)
MINUS
(SELECT DISTINCT VMP2.PLAYER_ID
FROM PREMIUM.VERSUS_MATCH_PARTICIPANT VMP2
JOIN PREMIUM.VERSUS_MATCH VM2
ON VM2.ID=VMP2.MATCH_ID
WHERE VM2.CREATED_AT < ADD_DAYS(:PeriodFrom,DAYS_BETWEEN(:PeriodFrom,:PeriodTo)))));</pre>
