Using parameters in GROUP BY query clause

[simonabertozzi]

Hello,

I was wondering if it's possible using iReport parameters in a GROUP BY clause?

I need to do something like this:

SELECT CASE WHEN ($P{period} = 'MONTH') THEN "MONTH"
WHEN ($P{period} = 'QUARTER') THEN "QUARTER"
WHEN ($P{period} = 'YEAR') THEN "YEAR" END AS PERIOD,
SUM("QTY_GOOD") AS TEU, SUM("QTY_TON_GOOD") AS TON
FROM "APV_DM"."APV_F_GOODS" A
INNER JOIN "APV_DM". "APV_L_GOODS" B ON (A."IDS_GOOD" = B."IDS_GOOD")
INNER JOIN "APV_DM"."APV_L_TIME" C ON (A."IDS_TIME_START_OP" = C."IDS_DAY_TIME")
GROUP BY CASE WHEN ($P{period} = 'MONTH') THEN "MONTH"
WHEN ($P{period} = 'QUARTER') THEN "QUARTER"
WHEN ($P{period} = 'YEAR') THEN "YEAR" END
ORDER BY PERIOD

But I keep getting the error: "MONTH" must appear in the GROUP BY clause or be used in an aggregate function

If I remove che CASE WHEN everything works.

Best regards,

Simona

[ajinkya_c]

Hi,

Try to implement logic using variable.

Write your case logic in variable with if else statement.

then use variable in your query select & group by.

Reply if it helps or not.

Regards,

Ajinkya

[simonabertozzi]

Hi Ajinkya,

it seems it's not possible to use variables in query: I get a syntax error at or near "$"

SELECT $V{variable1} AS PERIOD,
SUM("QTY_GOOD") AS TEU, SUM("QTY_TON_GOOD") AS TON
FROM "APV_DM"."APV_F_GOODS" A
INNER JOIN "APV_DM". "APV_L_GOODS" B ON (A."IDS_GOOD" = B."IDS_GOOD")
INNER JOIN "APV_DM"."APV_L_TIME" C ON (A."IDS_TIME_START_OP" = C."IDS_DAY_TIME")
GROUP BY $V{variable1}
ORDER BY PERIOD

Thanks,

Simona

[lucianc]

Try select $P!{period} as PERIOD ... group by $P!{period}

HTH,

Lucian

[simonabertozzi]

I finally managed to solve it.

The error I got was in the GROUP BY clause: if I write the query like this

SELECT CASE WHEN ($P{period} = 'MONTH') THEN "MONTH"
WHEN ($P{period} = 'QUARTER') THEN "QUARTER"
WHEN ($P{period} = 'YEAR') THEN "YEAR" END AS PERIOD,
SUM("QTY_GOOD") AS TEU, SUM("QTY_TON_GOOD") AS TON
FROM "APV_DM"."APV_F_GOODS" A
INNER JOIN "APV_DM". "APV_L_GOODS" B ON (A."IDS_GOOD" = B."IDS_GOOD")
INNER JOIN "APV_DM"."APV_L_TIME" C ON (A."IDS_TIME_START_OP" = C."IDS_DAY_TIME")
GROUP BY PERIOD
ORDER BY PERIOD

using the renaming, everything works!

It still seems a bit strange to me as to why this error shows up at all (if the renaming works, why can't I specify the whole CASE WHEN clause?), but thank you everyone who tried to help.

Best regards,

Simona