I'm going to write a if statement within that if condition pass need to run first select statement else second one. But it showing an INTO clause is expected in this SELECT statement
declare
x integer;
begin
select to_char(sysdate,'hh') into x from dual;
if x > 12 then
select sysdate from dual;
else
select sysdate 1 from dual;
end if;
end;
CodePudding user response:
You are running all three queries in a PL/SQL context (within an anonymous PL/SQL block, between the begin
and end
), so they all need to follow the PL/SQL rules.
That means they all need to be selected into something (or treated as cursors), so you need a date variable to select into, and then you need to do something with that variable:
declare
x integer;
d date;
begin
select to_number(to_char(sysdate, 'hh24')) into x from dual;
if x > 12 then
select sysdate into d from dual;
else
select sysdate 1 into d from dual;
end if;
-- do something with the variable
dbms_output.put_line(to_char(d, 'YYYY-MM-DD HH24:MI:SS'));
end;
/
Notice that I've also changed the first query to use the hh24
format element, because hh
gives the 12-hour-clock time, which can never be more than 12.
You don't really need the first query, you can just do:
declare
d date;
begin
if to_number(to_char(sysdate, 'hh24')) > 12 then
select sysdate into d from dual;
...
But it looks like you're only using PL/SQL so you can use the if/then/else
construct, which isn't available in plain SQL. That does have case
though, so as @jarlh said you can do:
select
case
when to_number(to_char(sysdate,'hh24')) > 12
then sysdate
else sysdate 1
end as result
from dual;
or
select
case
when extract(hour from systimestamp) > 12
...
You are also retaining the current system time, even if you change the date to tomorrow; you might want to set the time to midnight, which you can do with trunc(sysdate)
. It's unclear which you want though.
The logic also looks odd, but again it's unclear what you intended; 00-12 is modified to tomorrow and 13-23 kept as today, which might be backwards. If you want any time after (and including?) midday to be treated as tomorrow then you should be adding a day if the hour is 12 or above, so 00-11 is kept as today and 12-23 is modified to tomorrow. If that is what you meant then you can do:
select case
when extract(hour from systimestamp) >= 12
then trunc(sysdate) 1
else trunc(sysdate)
end as result
from dual;
Or even more simply, assuming you don't want to preserve the actual time this is run, you can do:
select trunc(sysdate 12/24) as result
from dual;