不能够连接的价值根据不同的列情况时的发言--雪花

Question 1

希望你们做好了!..我试图连接价值的情况下，当声明，根据不同的列在雪花..请找到代码块下面

select *,

case when checkouttime is null then ',Patient is not checked out' else '' END
+ case when primarypatientinsuranceid is null then ',No insurance information' else '' END
+ case when closedby is null then ',Encounter not signed off' else '' END
+ case when billingtabcheckeddate is null then ',Billing tab is not checked' else '' 
+ case when alreadyrouted is null then ',Missing slip already routed' else 'Valid Missing slip'

END as resultant

from final

我得到了错误说"意外的"

我在试图建立得到的列出如下面的

Patient is not checked out/Billing tab is not checked
Missing slip already routed
Encounter not signed off/No insurance information /Billing tab is not checked
Valid Missing slip

谢谢，阿伦

Question 2

一个更清洁的替代方案，添加逗号，如有必要，使用 array_to_string(array_construct_compact()):

with data as (
    select null checkouttime
        , 2 primarypatientinsuranceid
        , null closedby
        , 4 billingtabcheckeddate
        , 5 alreadyrouted
)

select array_to_string(array_construct_compact(
    iff(checkouttime is null, 'Patient is not checked out', null) 
    , iff(primarypatientinsuranceid is null, 'No insurance information', null)
    , iff(closedby is null, 'Encounter not signed off', null)
    , iff(billingtabcheckeddate is null, 'Billing tab is not checked', null)
    , iff(alreadyrouted is null, 'Missing slip already routed', 'Valid Missing slip')
    ), ',  ')
as resultant
from data

Question 3

在雪花，使用"||"以concat串，不是"+":

select 
case when true then ',Patient is not checked out' else '' END
|| case when false then ',No insurance information' else '' END
|| case when true then ',Encounter not signed off' else '' END
|| case when true then ',Billing tab is not checked' else '' END
|| case when false then ',Missing slip already routed' else 'Valid Missing slip' END 
as resultant;

https://docs.snowflake.com/en/sql-reference/functions/concat.html

Felipe Hoffa · Answer 1 · 2021-11-16T21:53:34

一个更清洁的替代方案，添加逗号，如有必要，使用 array_to_string(array_construct_compact()):

with data as (
    select null checkouttime
        , 2 primarypatientinsuranceid
        , null closedby
        , 4 billingtabcheckeddate
        , 5 alreadyrouted
)

select array_to_string(array_construct_compact(
    iff(checkouttime is null, 'Patient is not checked out', null) 
    , iff(primarypatientinsuranceid is null, 'No insurance information', null)
    , iff(closedby is null, 'Encounter not signed off', null)
    , iff(billingtabcheckeddate is null, 'Billing tab is not checked', null)
    , iff(alreadyrouted is null, 'Missing slip already routed', 'Valid Missing slip')
    ), ',  ')
as resultant
from data

Eric Lin · Answer 2 · 2021-11-16T11:33:34

在雪花，使用"||"以concat串，不是"+":

select 
case when true then ',Patient is not checked out' else '' END
|| case when false then ',No insurance information' else '' END
|| case when true then ',Encounter not signed off' else '' END
|| case when true then ',Billing tab is not checked' else '' END
|| case when false then ',Missing slip already routed' else 'Valid Missing slip' END 
as resultant;

https://docs.snowflake.com/en/sql-reference/functions/concat.html

谢谢@埃里克林..这是真的很有帮助。...可以请你让我知道怎么删除第一个字来作为逗号
对不起，你可以请你澄清? 我不完全理解您的上述问题。
嗨埃里克，...在输出结果我得到一个逗号在开始...我是在问到，如何摆脱的逗号...
这是因为有一个"、"在'，病人不是检查了我的猜测？

不能够连接的价值根据不同的列情况时的发言--雪花

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别