Using COUNT(*) and INTO oracle sql

Question

I have select statement like this:

    with input as
    (select id,date,quantity
    from
    abc a,xyz z
    .......)
         select count(*)
         from input t
         where .....;

this statement gives me a result of 0 and i want to use this count=0 my part of procedure . I added select count(*) output from which looks like this now:

 select count(*) output
 with input as
    (select id,date,quantity
    from
    abc a,xyz z
    .......)
         select count(*)
         from input t
         where .....);

now the output will not be 0(zero) anymore because it counts the result of zero itself and give the final result as 1..how can i use INTO statment to pass/hold that zero or any other real result in ORACLE/SQL?

I'm not really sure how the second statement is valid sql? Is it joined? Unioned? You can't just mix and match select/with statements without some operation to connect them. Give the first count an alias, and then refer to it later by alias, possibly in a group by, with max(alias) — Caius Jard, Nov 26 '17 at 04:05

score 3 · Accepted Answer · answered Nov 26 '17 at 04:04

You would have something like this:

declare
    v_cnt number;
begin
    with input as (
          select id,date,quantity
          from abc a join
               xyz z
               .......
         )
    select count(*) into v_cnt
    from input t
    where .....;
end;

Using COUNT(*) and INTO oracle sql

1 Answers1