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
– Caius Jard, Commented Nov 26, 2017 at 4:05

Gordon Linoff · Accepted Answer · 2017-11-26 04:04:12Z

3

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;

answered Nov 26, 2017 at 4:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using COUNT(*) and INTO oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related