問題描述

我想在 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 表達式的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！