Hi.
I have strange problem with dates comparision. When im using lt,gt
operators on date i always get
'General error: "java.lang.ArrayIndexOutOfBoundsException" [50000-164]
HY000/50000' error
Here is my query example:
SELECT
DR.*,
PC.CONVERSIONS
FROM (SELECT
CR.DATE AS date,
CR.CREATIVE_ID,
SUM(CASE WHEN CR.CHANNEL = 1 AND A.PRICING_TYPE = 1 THEN CR.CT *
A.AMOUNT
WHEN CR.CHANNEL = 1 AND A.PRICING_TYPE = 2 THEN CR.PV * A.AMOUNT
ELSE CR.ADV_PAYOUT END) AS budget,
SUM(CR.PV) AS shows,
SUM(CR.CT) AS clicks
FROM CREATIVE_REPORT CR JOIN AD A ON CR.AD_ID = A.ID
JOIN CAMPAIGN C ON C.ID = CR.CAMPAIGN_ID
JOIN CREATIVE CV ON CV.ID = CR.CREATIVE_ID
WHERE CR.DATE <= DATE '2014-08-07' AND CR.DATE >= DATE '2014-07-07'
GROUP BY CR.DATE, CR.CREATIVE_ID) AS DR LEFT JOIN (SELECT
COUNT(PC.*) AS
conversions,
PC.TRACK_DATE AS
date,
PC.CREATIVE_ID
FROM (SELECT
CAST(TRACK_DATE AS DATE) AS TRACK_DATE,
POST_CLICK_PIXEL_ID,
CREATIVE_ID
FROM
POST_CLICK_REPORT) AS PC
WHERE
PC.TRACK_DATE <= DATE '2014-08-07' AND
(PC.TRACK_DATE >= DATE '2014-07-07')
GROUP BY
PC.TRACK_DATE, PC.CREATIVE_ID) PC
ON PC.DATE = DR.DATE AND PC.CREATIVE_ID = DR.CREATIVE_ID
also top subqueries which are joined together work fine if executed
separatly.
Any help? Thanks
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
