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?
Hello Chuy,
What type of error are you experiencing?
I've tested here in a SQL Server and it is working as expected.
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.
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.
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