i have this error when i run this query.

PLS-00428: an INTO clause is expected in this SELECT statement

Query:

DECLARE
v_flag_tipo_factu varchar2(20);

BEGIN
  SELECT valor_param INTO v_flag_tipo_factu FROM t_param WHERE cod_param = 
  'TIPOFAC';

if v_flag_tipo_factu = 'FN' THEN 
    SELECT
        substr('00' || to_char(cta.cod_correo), -2) ||
        substr('000000000' || to_char(cta.num_cta_cte), -9) ||
        substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
    FROM
        t_acc_const cta,
        t_fac fac
    WHERE
        cta.num_cta_cte    = fac.num_cta_cte   AND
        fac.num_factura    < 900000000
    GROUP BY cta.cod_correo, cta.num_cta_cte;
end if;
END;

I expect 240,000 results like this:

13000291956000774037
06000167340000223372
13000089241000246480
13000057283000105163
06001632092003471840
13000093581000257191
12000252313001947873
06000120216000066999
06000309294001948770
13000192054000285006

CodePudding user response：

As you were told, SELECT (the one inside the IF) lacks in then INTO clause. You'll have to declare one more local variable and use it:

DECLARE
  v_flag_tipo_factu varchar2(20);
  --
  v_result varchar2(50);                           --> this
BEGIN
  SELECT valor_param INTO v_flag_tipo_factu FROM t_param WHERE cod_param = 
  'TIPOFAC';

if v_flag_tipo_factu = 'FN' THEN 
    SELECT
        substr('00' || to_char(cta.cod_correo), -2) ||
        substr('000000000' || to_char(cta.num_cta_cte), -9) ||
        substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
    INTO v_result                                  --> this
    FROM
    ...

It'll work if that select returns a single value; otherwise, query will raise

• no_data_found if there are no rows that satisfy conditions
• too_many_rows. How to handle it? It depends on what you want to do ...

CodePudding user response：

If you don't like the PL/SQL rules, where the queries must be stored in variables for each step, you could use a query like this which should return the same

with v as (
  SELECT valor_param FROM t_param WHERE cod_param = 'TIPOFAC'
)
SELECT
  substr('00' || to_char(cta.cod_correo), -2) ||
  substr('000000000' || to_char(cta.num_cta_cte), -9) ||
  substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
FROM
  t_acc_const cta,
  t_fac fac
WHERE
  cta.num_cta_cte    = fac.num_cta_cte   AND
  fac.num_factura    < 900000000 AND
  EXISTS (select 1 from v where valor_param = 'FN')
GROUP BY cta.cod_correo, cta.num_cta_cte;