at the query below I get error 403 Forbidden and I can't find a solution. if I remove the part with CASE or the columns with SUM( GROUP BY) then it works, but I need all those columns. I would be grateful if anyone could help me. thanks :-)
SELECT
campaign_id,
campaign_name,
campaign_price,
campaign_external_id,
campaign_goal_adrequest,
campaign_goal_banner_event,
campaign_goal_banner_event_key,
banner_id,
banner_name,
website_id,
website_name,
time_day,
time_month,
CASE
WHEN contentunit_name LIKE 'web_%' THEN 'web'
WHEN contentunit_name LIKE 'mob_%' THEN 'mob'
WHEN contentunit_name LIKE 'app_%' THEN 'app'
WHEN contentunit_name LIKE 'smart_%' THEN 'smart'
ELSE 'others'
END AS platform,
SUM(measures_adrequests) as adrequests,
SUM(measures_views) as views,
SUM(measures_truecount) as truecounts,
SUM(events_4970_count) as count_events,
SUM(events_4453_visibility) as visibility_events,
SUM(vast_click) as click_events,
SUM(vast_impression) as vast_impressions,
SUM(vast_start) as vast_start,
SUM(vast_firstquartile) as vast_firstquartile,
SUM(vast_midpoint) as vast_midpoint,
SUM(vast_thirdquartile) as vast_thirdquartile,
SUM(vast_complete) as vast_complete,
SUM(vast_loaded) as vast_loaded
FROM
MV_ADI_SUPERVIEW
WHERE
campaign_id = :campaign_id
GROUP BY
campaign_id,
campaign_name,
campaign_price,
campaign_external_id,
campaign_goal_adrequest,
campaign_goal_banner_event,
campaign_goal_banner_event_key,
banner_id,
banner_name,
website_id,
website_name,
time_day,
time_month,
platform
CodePudding user response:
In the Group By
, there is no reference to contentunit_name
at all.
In fact, you should probably group by the entire "case" statement:
...
GROUP BY
CASE
WHEN contentunit_name LIKE 'web_%' THEN 'web'
WHEN contentunit_name LIKE 'mob_%' THEN 'mob'
WHEN contentunit_name LIKE 'app_%' THEN 'app'
WHEN contentunit_name LIKE 'smart_%' THEN 'smart'
ELSE 'others'
END,
campaign_id,
campaign_name,
campaign_price,
campaign_external_id,
campaign_goal_adrequest,
campaign_goal_banner_event,
campaign_goal_banner_event_key,
banner_id,
banner_name,
website_id,
website_name,
time_day,
time_month,
platform
The error message is confusing, tho. Are you using Oracle? How are you calling this query? (Please tag your question more specifically.)