Join a new table to an existing query based on date parameter and return the respective tables value-CodePudding

I have a query below that is currently joining on multiple tables. ( Both x and y tables have the same exact table columns just different values. )My goal is to get values from table x if date_formatted > '2022-10-01' and if date_formatted < '2022-10-01' it returns the y tables values.

Query before adding in x table:

create or replace table REGION(
    "geo_region",
    "paid_organic",
    "Desk",
    "editor",
    "has_yt",
    "news",
    "device_devicecategory"
) as 
(SELECT 
iso.region as "geo_region",
hand_tagged.PAID_ORGANIC as "paid_organic",
gsheet_editor.DESK as "Desk",
il_cms.HAS_YT as "has_yt",
y.news as "news",
y.DEVICE_DEVICECATEGORY as "device_devicecategory"

FROM y
LEFT JOIN iso
on y.geo_country = iso.COUNTRYNAME
LEFT JOIN il_cms
on y.cms_web_id = il_cms.web_id
AND y.published = il_cms.url_fragment
LEFT JOIN gsheet_editor
on il_cms.editor = gsheet_editor.editor
LEFT JOIN hand_tagged
on y.traffics = hand_tagged.traffics
WHERE y.DATE_FORMATTED > DATEADD(year,-2,current_date()));

Trying to get it correct with y but I have no clue what I'm doing and I keep seeing an error w. my case when statement:

create or replace table REGION(
    "geo_region",
    "paid_organic",
    "Desk",
    "editor",
    "has_yt",
    "news",
    "device_devicecategory"
) as 
(SELECT 
  case when 
     y.date_formatted < '2022-10-01' then (
     iso.region as "geo_region",
      hand_tagged.PAID_ORGANIC as "paid_organic",
     gsheet_editor.DESK as "Desk",
     il_cms.HAS_YT as "has_yt",
     y.news as "news"
     y.DEVICE_DEVICECATEGORY as "device_devicecategory")
 when 
    x.date_formatted > '2022-10-01' then (
     iso.region as "geo_region",
      hand_tagged.PAID_ORGANIC as "paid_organic",
     gsheet_editor.DESK as "Desk",
     il_cms.HAS_YT as "has_yt",
     x.news as "news"
     x.DEVICE_DEVICECATEGORY as "device_devicecategory")

FROM y
JOIN X
on x.device = y.device
and x.search = y.search
LEFT JOIN iso
on y.geo_country = iso.COUNTRYNAME
LEFT JOIN il_cms
on y.cms_web_id = il_cms.web_id
AND y.published = il_cms.url_fragment
LEFT JOIN gsheet_editor
on il_cms.editor = gsheet_editor.editor
LEFT JOIN hand_tagged
on y.traffics = hand_tagged.traffics
WHERE y.DATE_FORMATTED > DATEADD(year,-2,current_date()));

CodePudding user response：

CASE WHEN THEN END statement returns single expression and not column list:

SELECT 
case when 
     y.date_formatted < '2022-10-01' then (
     iso.region as "geo_region",
      hand_tagged.PAID_ORGANIC as "paid_organic",
     gsheet_editor.DESK as "Desk",
     il_cms.HAS_YT as "has_yt",
     y.news as "news"
     y.DEVICE_DEVICECATEGORY as "device_devicecategory")
 when 
    x.date_formatted > '2022-10-01' then (
     iso.region as "geo_region",
      hand_tagged.PAID_ORGANIC as "paid_organic",
     gsheet_editor.DESK as "Desk",
     il_cms.HAS_YT as "has_yt",
     x.news as "news"
     x.DEVICE_DEVICECATEGORY as "device_devicecategory")

should rather be multiple CASE when output differs:

SELECT   
    iso.region as "geo_region",
    hand_tagged.PAID_ORGANIC as "paid_organic",
    gsheet_editor.DESK as "Desk",
    il_cms.HAS_YT as "has_yt",
    CASE WHEN y.date_formatted < '2022-10-01' THEN y.news 
         ELSE x.news 
    END AS news,
    CASE WHEN y.date_formatted < '2022-10-01' THEN y.DEVICE_DEVICECATEGORY 
         ELSE x.DEVICE_DEVICECATEGORY 
    END AS "device_devicecategory"
FROM ...