开发者

Oracle 10g simple query error

Oracle SQL Developer complains about next SQL though I can't seem to find the reason:

IF to_number(to_char(sysdate, 'HH24')) > 6 THEN
  IF to_number(to_char(sysdate, 'HH24')) < 9 THEN
    SELECT 1 FROM dual;
  ELSE
    SELECT (CASE WHEN result = 'SUCCESS' THEN 1 ELSE 0 END) FROM t_job WHERE to_char(start_time, 'yyyy/mm/dd') = to_char(sysdate, 'yy开发者_运维技巧yy/mm/dd');
  END IF;
ELSE
    SELECT (CASE WHEN result = 'SUCCESS' THEN 1 ELSE 0 END) FROM t_job WHERE to_char(start_time, 'yyyy/mm/dd') = to_char(sysdate, 'yyyy/mm/dd');
END IF;

What's the mistake in provided query?

Error report:

Error starting at line 7 in command:
ELSE
Error report:
Unknown Command
(CASEWHENRESULT='SUCCESS'THEN1ELSE0END) 
--------------------------------------- 
1                                       


Error starting at line 9 in command:
END IF
Error report:
Unknown Command


There are a couple of problems with your code (which is PL/SQL, not just SQL):

1) You are missing the begin and end around the block.

2) Your selects need an into clause

try:

DECLARE
  l_result number;
BEGIN
  IF to_number(to_char(sysdate, 'HH24')) > 6 THEN
    IF to_number(to_char(sysdate, 'HH24')) < 9 THEN
      SELECT 1 INTO l_result FROM dual;
    ELSE
      SELECT (CASE WHEN result = 'SUCCESS' THEN 1 ELSE 0 END) 
       INTO l_result 
       FROM t_job WHERE to_char(start_time, 'yyyy/mm/dd') = to_char(sysdate, 'yyyy/mm/dd');
    END IF;
  ELSE
      SELECT (CASE WHEN result = 'SUCCESS' THEN 1 ELSE 0 END) 
       INTO l_result 
       FROM t_job WHERE to_char(start_time, 'yyyy/mm/dd') = to_char(sysdate, 'yyyy/mm/dd');
  END IF;
  dbms_output.put_line('result is '||l_result);
END;


Since this is a PL/SQL block, your SELECT statements would need to select the data into some local variable or they would need to be used in a cursor. Which approach you want would depend on how many rows in T_JOB could potentially match the criteria you're specifying. Assuming all three statements would return exactly 1 row, you could do something like this (code simplified to avoid repeating the same query twice)

DECLARE
  l_some_local_variable PLS_INTEGER;
BEGIN
  IF( to_number( to_char( sysdate, 'HH24' ) ) > 6 and
      to_number( to_char( sysdate, 'HH24' ) ) < 9 )
  THEN
    SELECT 1 
      INTO l_some_local_variable 
      FROM dual;
  ELSE
    SELECT (CASE WHEN result = 'SUCCESS' 
                 THEN 1
                 ELSE 0
             END) 
      INTO l_some_local_variable 
      FROM t_job
     WHERE trunc( start_time ) = trunc( sysdate );
  END IF;
END;

Of course, once you populate the data in your local variable, you would need to actually do something with the value. Potentially, you may want to create a function that returns the local variable rather than using an anonymous PL/SQL block as I have done here.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜