如何在同一查询中将一个数据字段用于另一个 case 表达式 [英] How to use one data field into another case expression in the same query

问题描述

我想在 CASE 表达式的第二部分使用 SNumber 但不知道如何使用它?

I want to use SNumber in the second part of CASE expression but not sure how to use it?

    select    
        PE.EDateTime,
        (case when(PE.EDateTime is not NULL and cast(PE.EDateTime as time) < '12:30') then cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'AM'
             when (PE.ADate is not NULL and cast(PE.ADate as time) < '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'AM'
             when (PE.EDateTime is not NULL and cast(PE.EDateTime as time) >= '12:30') THEN  cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'PM'
             when  (PE.ADate is not NULL and cast(PE.ADate as time) > '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'PM'
             else null 
            end) as SNumber,
   
     case 
   when (SNumber like'%AM') then 'AM'
   when SNumber like '%PM') then 'PM'
   else null 
end as [Session],
        Comments   
    from (
        select       
            PE1.RId,
            PE1.ADate,
            PE1.EDateTime
        from (
            select
                RegEId, 
                ADate,
                EDateTime,
                Comments
            from PatEnr
            where PreNumber is not null               
            ) as PE1
        left join Pat PEA 
            on PE1.RegEId = PEA.RegEId   
        left join PBooking PB 
            on PB.RegEId = PE1.RegEId
        ) as PE

我想在下面的同一查询中使用 SNumber ，但我无法使用它，因为这都属于一个查询.有没有办法在下面的 CASE 中使用上述内容?我想使用如下所示的内容.

I want to use SNumber here below in the same query but I am not able to use this as this all belongs to one query. Is there any way to use the above in below CASE? I want to use something like below.

case 
   when (SNumber like'%AM') then 'AM'
   when (SNumber like '%%PM') then 'PM'
   else null 
end as Session,

推荐答案

使用交叉应用来执行您可以重复使用的计算.

Use cross apply to perform your calculation which you can then reuse.

select
    SNumber
    , case when SNumber like '%AM%' then 'AM'
    when SNumber like  '%PM%' then 'PM'
    else null end as [Session]
from MyTable PE
cross apply (
values (
    case when(PE.EDateTime is not NULL and cast(PE.EDateTime as time) < '12:30') then cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'AM'
    when (PE.ADate is not NULL and cast(PE.ADate as time) < '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'AM'
    when (PE.EDateTime is not NULL and cast(PE.EDateTime as time) >= '12:30') THEN  cast(format(PE.EDateTime,'yyyyMMddhhmm') as varchar(50))+'PM'
    when (PE.ADate is not NULL and cast(PE.ADate as time) > '12:30') then cast(format(PE.ADate,'yyyyMMddhhmm') as varchar(50))+'PM'
    else null 
    end
)) x (SNumber)

注意:contains 在该上下文中不起作用 - 它是全文搜索的 where 子句谓词，因此我已替换为 喜欢.

Note: contains doesn't work in that context - its a where clause predicate for full-text search, so I've replaced with like.

这篇关于如何在同一查询中将一个数据字段用于另一个 case 表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！