an INTO clause is expected in this SELECT statement please help on this one-CodePudding

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 please help on this one

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;

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;

fiddle