用例何时插入值 [英] Using case when to insert values

问题描述

下面显示了一个可执行语句: 成功尝试:

Below shows a executable statement: Successful attempt:

INSERT INTO Personnel_Assignment (DATE, testno, Hours) 
SELECT '21-OCT-2011', '12345', 
   CASE
      WHEN Extract(day From(S.ENDTIME-S.STARTTIME) ) >= 1 
         THEN (Extract(Day From(S.ENDTIME-S.STARTTIME) ) * 24 
            + Extract(Hour From(S.ENDTIME-S.STARTTIME) ) )
      WHEN S.endtime IS NULL
         THEN NULL
      ELSE
         Extract(Hour From(S.ENDTIME-S.STARTTIME) ) )
      ||'hrs' End ||
      Extract(Minute From(S.ENDTIME-S.STARTTIME) ) || 'Min' As Hours 
FROM Schedule S`

请注意，结束时间和开始时间的数据类型是带有时区的时间戳，格式为:

Please note that the data type for endtime and start time is timestamp with timezone in this format:

Nls_Timestamp_Tz_Format='HH24:MI TZR'

我想问一个问题: 我几个小时的数据类型是varchar2 而且，如果我想根据上述结果总结一下我的小时数，将其转换成数字是否会很乏味?

Just a question that i would like to ask: My datatype for hours is varchar2 And if i wish to sum my hours in the end from the results above, would it be tedious in converting it into number?

谢谢

推荐答案

首先，|| Else没有任何意义. ||之后的部分必须是另一个要连接的表达式.

First of all, || Else doesn't make sense. The part after || has to be another expression to concatenate.

第二，您当然可以 嵌套case表达式，但是在您的情况下则不需要.单个case表达式可以具有多个when/then分支，形式为case when [condition_A] then [expression_if_A_is_true] when [condition_B] then [expression_if_A_is_false_and_B_is_true] else [expression_if_A_and_B_are_both_false] end.

Secondly, you certainly can nest case expressions, but in your case you don't need to. A single case expression can have multiple when/then branches, in the form case when [condition_A] then [expression_if_A_is_true] when [condition_B] then [expression_if_A_is_false_and_B_is_true] else [expression_if_A_and_B_are_both_false] end.

这篇关于用例何时插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！