如何在where子句中正确使用大小写 [英] how to correctly use case in where clause

问题描述

所以我有一个正在调试的过程，并且将其范围缩小到此select语句.

So i have a procedure that i'm currently in the process of debugging and i've narrowed it down to this select statement.

注意:其中to_date('')，3300、5220表示来自参数的内容.

Note: where the to_date(''), 3300, 5220 is a representation of what would come from a parameter.

现在应该做的是，将参数作为时间戳并减去偏移值

Now what this is suppose to do, is take the parameter which is a timestamp and subtract an offset value

偏移量是指自周初起午夜= 0的一周开始经过的分钟数.(因此，如果是星期一，则偏移量= 1440).

the offset is the number of minutes that have gone by since the beginning of the week where sunday at midnight = 0. (So if it was monday at midnight the offset would = 1440).

从参数中减去偏移量后，便可以得出一周的开始时间.然后，您从已经预先确定的表中获取偏移值，并将该值添加到一周的开始时间以获取时间戳.

when the offset is subtracted from the parameter, you then get the beginning of the week. You then get the offset value from the table which was already predetermined and add that value to the start of the week to obtain the timestamp.

这样做是为了获得轮班的开始日期和结束日期.

This is done in order to get the start date and end date of a shift.

下面的代码没有问题，但是缺少周六到周日的边界条件.

My original code works no problem with is below, however it is missing the boundary condition of saturday going into sunday.

SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
         SHIFT_START_DAY, SHIFT_START_TIME,
         SHIFT_END_DAY, SHIFT_END_TIME, 
         SITE_ID_FK, SHIFT_DAY_ID,
         STARTOFFSET, ENDOFFSET,
         TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET  / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),  'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,
         TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP -  (VENDOFFSET / 24 / 60)) + (ENDOFFSET   / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM') ,'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_END_DATE
  from   shift_tbl
  WHERE
         ENDOFFSET >= VSTARTOFFSET
  and    STARTOFFSET < VENDOFFSET 
  order by shift_start_date asc;

现在我想出的处理这种边界条件的条件是在脚本中进行测试的水平.

Now what i have come up with to handle this boundary condition is below which i have been testing in a script.

   declare
  VSTARTOFFSET integer;
  VENDOFFSET integer;
  SHIFTOFFSET integer;
  PSTARTTIMESTAMP timestamp;
  PENDTIMESTAMP timestamp;
  begin 
    VSTARTOFFSET := 10020;
    VENDOFFSET := 420; 
    PSTARTTIMESTAMP := TO_DATE('3/17/2012 23:00', 'mm/dd/yyyy hh24:mi');
    PENDTIMESTAMP :=   TO_DATE('3/18/2012 7:00', 'mm/dd/yyyy hh24:mi');

  SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
         SHIFT_START_DAY, SHIFT_START_TIME,
         SHIFT_END_DAY, SHIFT_END_TIME, 
         SITE_ID_FK, SHIFT_DAY_ID,
         STARTOFFSET, ENDOFFSET,
         TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,  
         TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP- (VENDOFFSET / 24 / 60)) + (ENDOFFSET   / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') AS SHIFT_END_DATE
  from   SHIFT_TBL
  where
    case 
      when SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 then          
                 SHIFTOFFSET:= ENDOFFSET + 10080; 
          and    VENDOFFSET := VENDOFFSET + 10080;
      else
                 SHIFTOFFSET := ENDOFFSET;
    end
                 SHIFTOFFSET >= VSTARTOFFSET
          and    STARTOFFSET < VENDOFFSET 
    order by SHIFT_START_DATE asc; 
  end;

如您所见，我不确定如何处理where子句中的case语句.基本上我想做的是，如果开始日期是星期六，结束日期是星期日，则将10080(一个星期)添加到结束偏移量/排放偏移量中，如果它不满足该条件，则使用原始值.

As you can see i am unsure as to how to handle the case statement inside of the where clause. Basically what i am trying to do is if the start day is Saturday and the end day is Sunday, then add 10080(one week) to the end offset/vend offset and if it does not meet that condition, then use the original values.

基本上，我的问题很简单...我相信，但是我很难获得解决方案.所以我想知道的是如何在where子句中正确使用case语句.而且，如果我不打算在where子句中以这种形式使用case语句，那么我将如何准确地设置此select语句.

Basically my question is fairly simple...i believe but i am having difficultly obtaining the solution. So what i would like to know is how to properly use a case statement inside of the where clause. And if i am not suppose to use a case statement in this form inside a where clause how exactly would i set up this select statement.

任何帮助或建议，我们将不胜感激. 谢谢.

Any help or suggestions are greatly appreciated. Thank you.

推荐答案

您不需要在WHERE子句中设置任何变量，实际上甚至不能这样做. 您要做的是编写描述要获取的行的正确逻辑谓词(即返回true或false的表达式).

You do not need to set any variables in the WHERE clause, actually even you can't do it. What you want to do is to write correct logical predicate (that is an expression returning true or false) describing rows you want to get.

这里有两个示例，我将尝试定义它(据我了解您的要求):

Here are 2 examples how I would try to define it (as far as I understand your requirements):

1. 没有大写字母:

1. without CASE:

WHERE
( SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1  AND ENDOFFSET + 10080 >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET + 10080) OR
( NOT (SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1) AND SHIFTOFFSET >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET )

(带大写字母):

with CASE:

WHERE
(CASE WHEN SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 THEN ENDOFFSET + 10080 ELSE ENDOFFSET END) >= VSTARTOFFSET
AND    STARTOFFSET < (CASE WHEN SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 THEN VENDOFFSET + 10080 ELSE VENDOFFSET END)

我没有调试此表达式，所以不要指望它们能起作用；)，但我希望您已经明白了.

I did not debug this expressions so do not expect them to work ;), but I hope that you have got the idea.

这篇关于如何在where子句中正确使用大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！