In-database Coalesce formula for Postgres using GreenPlum

Question

Hi,

I'm having an issue running a simple formula for an in-database calculation. I want to be able to override the value of Col_A if it happens that Col_B is not null. I'm using the "Formula In-DB" tool. I select "Col_A" as the Output Field and my SQL Expression is:

coalesce("Col_B", "Col_A")

However it doesn't work. This runs fine in DataGrip but Alteryx doesn't like it. I also tried to format it as a CASE statement and still have the same issue.

Any idea what am I doing wrong?

simonaubert_bd · Answer

Hello @Chuy 
1/ Have you tried to create a temporary table (or several). Your query is really big
2/ Since a few years, Alteryx now distingue psotgresql and greenplum

Best regards,

Simon

Chuy · Answer

The error text resolves to this long string: Error: Formula In-DB (26): Error opening "SELECT "appl_id", "s_application_origin", "appl_dt", "R_appl_id", (coalesce("s_application_origin","channel")) AS "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step", "CountDistinct_visit_id" FROM (SELECT "Left"."appl_id", "Left"."s_application_origin", "Right"."appl_dt", "Right"."appl_id" AS "R_appl_id", "Right"."channel", "Right"."device", "Right"."state_cd", "Right"."dma", "Right"."zip_cd", "Right"."funnel_step", "Right"."CountDistinct_visit_id" FROM (SELECT "appl_id", MAX("s_application_origin") AS "s_application_origin" FROM (SELECT * FROM (SELECT "n_id_application" AS "appl_id", "s_activate_account", "s_reservation_code", "s_channel_code", "s_channel_medium_code", "d_creation_application" AS "appl_dt", "s_description_product", "s_application_origin" FROM (SELECT * FROM "ci_owner"."master_oao") AS "a") AS "a" WHERE "s_application_origin" in ('MOBILE','ONLINE')) AS "a" GROUP BY "appl_id") AS "Left" RIGHT JOIN (SELECT "appl_dt", "appl_id", "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step", COUNT(DISTINCT "visit_id") AS "CountDistinct_visit_id" FROM (SELECT * FROM (SELECT "os" AS "device", "evar54" AS "appl_id", "geo_dma" AS "dma", "geo_region" AS "state_cd", "geo_zip" AS "zip_cd", "date" AS "appl_dt", "visit_id", "channel", "visit_num", "funnel_step" FROM (SELECT * FROM (SELECT "id", "current_flag", "os", "visit_keywords", "evar54", "browser_useragent", "platform_useragent", "browser_height", "browser_width", "ref_domain", (case when os = 'DESKTOP' then 'DESKTOP' when os = 'MOBILE' then 'MOBILE' when device = 'x11' then 'DESKTOP' else 'OTHER' end ) AS "device", "visit_start_page_url", "visit_referrer", "application_visit", "application_start", "application_fundingscreen", "application_esignature", "application_complete", "application_declined", "application_approved", "application_review", "post_application_complete", "post_application_declined", "post_application_approved", "post_application_visit", "landing_page_url", "previous_url", "aggregated_application_field", "post_landing_page_url", "post_previous_page_url", "post_aggregated_application_field", "event_listtext", "geo_city", "geo_country", "geo_dma", (upper(geo_region)) AS "geo_region", "geo_zip", "user_agenttext", "date", "day", "hour", "minute", "millisecond", "time_of_day", "visit_id", "visits_metric_exclusions", (upper( case when visit_referrer like '%?cid=sem%' then 'paid search' when visit_referrer like '%?cid=aff' then 'affiliate' when visit_referrer like '%google%' and channel NOT IN ('sem','dis','eml','dir') then 'organic' when visit_referrer like '%yahoo%' and channel NOT IN ('sem','dis','eml','dir') then 'organic' when visit_referrer like '%/mail%' then 'email' when visit_referrer like '%nerdwallet.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%duckduckgo.com%' then 'organic' when visit_referrer like '%myway.com%' then 'organic' when visit_referrer like '%facebook%' then 'social' when visit_referrer like '%yelp%' then 'organic' when visit_referrer like '%reddit%' then 'organic' when visit_referrer like '%outlook%' and channel NOT IN ('sem','dis','eml','aff') then 'email' when visit_referrer like '%xfinity.com%' then 'email' when visit_referrer like '%websnips.net%' then 'display' when visit_referrer like '%/t.co/%' then 'social' when visit_referrer like '%dealbiscuit.com%' then 'sponsorship' when visit_referrer like '%channelServiceCode=39&%' then 'sponsorship' when visit_referrer like '%magnifymoney.com%' then 'organic' when visit_referrer like '%touch.kaspersky.com%' then 'organic' when visit_referrer like '%usa.visa.com%' then 'organic' when visit_referrer like '%search.aol.com%' then 'organic' when visit_referrer like '%portal.adp.com%' then 'internal employee' when visit_referrer like '%yellowpages.com%'then 'organic' when visit_referrer like '%hustlermoneyblog.com%' then 'organic' when visit_referrer like '%search.frontier.com%' then 'display' when visit_referrer like '%?cid=oth:non:oth%' then 'mobile app' when visit_referrer like '%nortonsafe.search.ask.com%' then 'organic' when visit_referrer like '%www.bbvacompass.com' then 'direct' when visit_referrer like '%www.bbvacompass.com%' and channel NOT IN ('sem','dis','eml','aff') then 'direct' when visit_referrer like '%dnserrorassist.att.net%' then 'organic' when visit_referrer like '%/sessionExpired?%' then 'sso expired' when visit_referrer like '%/resume?%' then 'resume' when visit_referrer like '%?source=affiliate%' then 'affiliate' when visit_referrer like '%online-banking%' then 'online banking' when visit_referrer like '%online.bbvacompass.com/apply/error%' then 'error' when visit_referrer like '%secure.bbvacompass.com/locations/error.jsp%' then 'error' when visit_referrer like '%?cid=eml:%' then 'email' when visit_referrer like '%www.valuepenguin.com%' then 'organic' when visit_referrer like '%cdn.plaid.com%' then 'other referral site' when visit_referrer like '%crep.myappzcollection.com/%' then 'other referral site' when visit_referrer like '%clearspend.bbvacompass.com%' then 'clearspend site' when visit_referrer like '%USA/TX/Carrollton/3640-N-Josey-Ln/%' then 'direct' when visit_referrer like '%https://webmail.uwmail.com%' then 'email' when visit_referrer like '%echeck.com%' then 'organic' when visit_referrer like '%search.handy-tab.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%gopher.com%' then 'organic' when visit_referrer like '%search.tb.ask.com%' then 'organic' when visit_referrer like '%?app=%' then 'mobile app' when visit_referrer like '%www.clipsit.net%' then 'display' when visit_referrer like '%cid%3Ddis:ret:gdn%' then 'display' when visit_referrer like '%New_Hire_Direct_Deposit_Authorization%' then 'internal employee' when visit_referrer like '%icims%' then 'internal employee' when visit_referrer like '%www.smarter.com%'and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%search.fulltabsearch.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%email10.godaddy.com%' then 'email' when channel = 'aff' then 'affiliate' when channel = 'seo' then 'organic' when channel = 'sem' then 'paid search' when channel = 'spo' then 'sponsorship' when channel = 'soc' then 'social' when channel = 'eml' then 'email' when channel = 'dir' then 'direct' when channel = 'mob' then 'mobile app' when channel = 'olb' then 'online banking' when channel = 'dis' then 'display' when va_closer_detail like '%app:::' then 'mobile app' when va_closer_detail like 'oth:non:oth%' then 'mobile app' when va_closer_detail like 'oth:oth:oth%' then 'mobile app' else 'unknown' end ) ) AS "channel", "post_campaign_custom1", "platform", "post_campaign_custom2", "post_campaign_custom3", "post_campaign_custom4", "post_campaign_custom5", "post_campaign_custom6", "post_campaign_custom7", "post_campaign_custom8", "ad_size", "ad_location", "post_campaign_custom9", "parent_product", (CASE WHEN PRODUCT = 'CHKG' THEN 'CHECKING' WHEN SUB_PRODUCT <> 'MMA' AND SUB_PRODUCT <> 'CD' AND PRODUCT = 'SAV' THEN 'SAVINGS' WHEN PRODUCT = 'credit cards' THEN 'CREDIT CARD' WHEN SUB_PRODUCT = 'MMA' THEN 'MONEY MARKET' WHEN SUB_PRODUCT = 'CD' THEN 'CERTIFICATE OF DEPOSIT' WHEN PRODUCT = 'LEND' THEN 'EPL' WHEN PRODUCT = 'prepaid cards' THEN 'PREPAID CARDS' WHEN sub_product = 'FC' THEN 'CHECKING' WHEN SUB_PRODUCT = 'CCNC' THEN 'CHECKING' WHEN SUB_PRODUCT = 'CCS' THEN 'SAVINGS' WHEN SUB_PRODUCT = 'easy checking' THEN 'CHECKING' ELSE NULL END ) AS "product", "sub_product", "rundt", "dayid", "va_closer_detail", "va_closer_id", "va_finder_detail", "va_finder_id", "va_instance_event", "va_new_engagement", "visit_num", (CASE WHEN post_aggregated_application_field LIKE '%:app started:%' THEN 'APPLICATION START' WHEN post_aggregated_application_field LIKE '%:end' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field LIKE '%:app complete:' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field LIKE '%:end' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field = 'clearspend:2 add secondary cardholder' AND sub_product = 'clearspend' then 'APPLICATION COMPLETE' ELSE null END) AS "funnel_step" FROM (SELECT * FROM (SELECT * FROM "ci_owner"."master_clickstream") AS "a" WHERE "date"::date BETWEEN to_date('20180101','YYYYMMDD') AND current_date) AS "a") AS "a" WHERE "funnel_step" IS NOT NULL) AS "a") AS "a" WHERE "funnel_step" = 'APPLICATION START') AS "a" GROUP BY "appl_dt", "appl_id", "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step") AS "Right" ON "Left"."appl_id" = "Right"."appl_id") AS "a"": No Columns Returned. This is the workflow I have so farand this is the definition of the formula

Chuy · Answer

I would expect that exact behavior, but at this point is giving me an error in the formula. My database is PostgreSQL and I'm accessing it through the Greenplum connector.