在检查条件后连接2列

Question

我正在尝试连接这两列"pv.name package_version_name"one_answers";sf_li.sf_lma_version_number_c"我试图将它与下面的if语句连接起来。
[Package_version_name] ([Version_Number])示例输出:Chicago Update 2 (14.4.2)我再举一个例子，版本号:(13.0.10);package_version_name:芝加哥;输出/新列(版本发布):Chicago(13.0.10)。在某些情况下，package_version_name已经有两个字段，例如package_version_name: Brooklyn(9.5.14)，所以我们可以在新列(版本发布):Brooklyn(9.5.14)中单独输出它，而不添加版本号，因为它已经在那里了。

sf_li.sf_lma_version_number_c AS version_number,
pv.name package_version_name,
IF( CONTAINS(pv.Name , ")"), pv.Name, pv.Name + "(" + sf_li.sf_lma_version_number_c + ")"),
FROM "prod"."salesforce"."sf_lma_license_c" sf_li
left join "prod"."salesforce"."sf_lma_package_version_c" pv
on sf_li.sf_lma_package_version_c=pv.id

Answer 1

运行代码:

SELECT 
CASE 
WHEN package_version_name NOT LIKE '%(%' OR package_version_name NOT LIKE '%)%'  
THEN   CONCAT(        
CONCAT(cast(package_version_name as char(200)),
cast('(' as char (200))) 
, 
CONCAT(cast(version_number as char(200)),
cast(')' as char(200)))        
) 
ELSE 
package_version_name   as final_version_field
FROM 
[TABLE NAME] / from {{ref('[TABLE NAME]')}}

额外条件:

你也可以添加另一个条件，如:

WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' AND IS_VARCHAR(package_version_name) = 'True'

这也将检查您没有括号，即(和)，并且值的类型为varchar。我使用IS_VARCHAR，因为我使用雪花，你可以找到一个相关的sql

Answer 2

SELECT distinct sf_li.id license_id,
sf_li.created_date as license_created_date,
sf_li.name                               AS license_name,
sf_li.sf_lma_install_date_c              AS install_date,
sf_li.sf_lma_subscriber_org_id_c org_id,
sf_li.sf_lma_org_type_c                  AS org_type,
sf_li.sf_lma_subscriber_org_is_sandbox_c AS sandbox_org,
sf_li.sf_lma_status_c                    AS status,
sf_li.sf_lma_version_number_c            AS skuid_version_number,
sf_li.sf_lma_expiration_c expiration_date,
sf_li.sf_lma_license_type_c license_type,
sf_li.sf_lma_package_license_id_c package_license_id,
sf_li.sf_lma_package_version_c package_version_id,
pv.sf_lma_package_c package_id,
pv.name package_version_name,
pv.sf_lma_release_date_c package_release_date,
pv.release_number_c package_release_number,
CASE WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' THEN CONCAT(package_version_name, skuid_version_number) else package_version_name end skuid_release,
--CASE WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' THEN CONCAT(package_version_name, '(',skuid_version_number,')') else package_version_name end skuid_release,
-- CASE WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' AND VARCHAR(package_version_name) = 'True',
-- CASE WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' THEN CONCAT(package_version_name, '(',version_number,')') else package_version_name as final_version_field
CASE WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' THEN CONCAT(package_version_name, '(',skuid_version_number,')') else package_version_name end skuid_release, 
sf_li.sf_lma_seats_c subscription_count,
sf_li.sf_lma_used_licenses_c subscriptions_used,
case when subscription_count > 0 then subscriptions_used::decimal(10,2)/subscription_count::decimal(10,2) else -1::decimal(10,2) end utilization,
case when sf_li.sf_lma_org_type_c LIKE 'Developer%' then TRUE::boolean else FALSE::boolean end is_developer,
l.id lead_id,
l.email lead_email,
l.has_opted_out_of_email lead_email_opt_out,
l.title,
l.industry,
l.behavior_score_c behavior_score,
l.demographic_score_c demographic_score,
l.webinar_score_c webinar_score,
coalesce(l.behavior_score_c,0) + coalesce(l.demographic_score_c,0) + coalesce(l.webinar_score_c,0) combined_lead_score,
c.id contact_id,
c.email contact_email,
c.has_opted_out_of_email contact_email_opt_out,
sf_li.installation_category_c            AS installation_category,
case when sf_li.sf_lma_subscriber_org_is_sandbox_c = TRUE then 'Sandbox' when is_developer = TRUE then 'Developer' else 'Production' end org_category,
l.datahugapps_ma_c                       AS matched_account,
case when not c.id is null then 'Contact' when not l.id is null then 'Lead' else 'Unknown' end installer_type,
coalesce(c.id,l.id) lead_or_contact_id,
coalesce(c.name,l.name) lead_or_contact_name,
coalesce(c.owner_id,l.owner_id) lead_or_contact_owner_id,
coalesce(sf_c_o.name,sf_c_o_g.developer_name,sf_l_o.name,sf_l_o_g.developer_name) lead_or_contact_owner_name,
coalesce(c.account_id,l.datahugapps_ma_c) account_id,
coalesce(sf_c_a.name,sf_l_a.name) account_name,
coalesce(sf_c_a.vertical_c,sf_l_a.vertical_c) account_vertical,
coalesce(sf_c_a.type,sf_l_a.type) account_type,
coalesce(sf_c_a.segment, sf_l_a.segment) account_segment,
coalesce(sf_c_a.owner_id,sf_l_a.owner_id) account_owner_id,
coalesce(sf_c_a_o.name, sf_l_a_o.name) account_owner_name
FROM {{source('salesforce','license')}} sf_li
left join {{source('salesforce','package_version')}} pv
on sf_li.sf_lma_package_version_c=pv.id
left join {{source('salesforce','package')}} pk
on pv.sf_lma_package_c=pk.id
LEFT JOIN {{source('salesforce','contact')}} c
ON sf_li.sf_lma_contact_c=c.id
LEFT JOIN {{source('salesforce','lead')}} l
ON sf_li.sf_lma_lead_c=l.id
left join {{source('salesforce','account')}} sf_c_a -- salesforce contact account
on c.account_id=sf_c_a.id
left join {{source('salesforce','account')}} sf_l_a -- salesforce lead account
on l.datahugapps_ma_c=sf_l_a.id
left join {{source('salesforce','user')}} sf_c_a_o -- salesforce contact account owner
on sf_c_a.owner_id=sf_c_a_o.id
left join {{source('salesforce','user')}} sf_l_a_o -- salesforce lead account owner
on sf_l_a.owner_id=sf_l_a_o.id
left join {{source('salesforce','user')}} sf_c_o -- salesforce contact owner
on c.owner_id=sf_c_o.id
left join {{source('salesforce','user')}} sf_l_o -- salesforce lead owner
on c.owner_id=sf_l_o.id
left join {{source('salesforce','group')}} sf_l_o_g -- salesforce lead owner group
on c.owner_id=sf_l_o_g.id
left join {{source('salesforce','group')}} sf_c_o_g -- salesforce contact owner group
on c.owner_id=sf_c_o_g.id
where not sf_li.is_deleted