How to get a count rows in a column in table 2 using column in table 1 using PLSQL-CodePudding

I want to count number of rows of table 'proc' which has column called proc_id and proc_dt (date column) using table called 'work' which has column called prod_id and counts rows of today's date.

All the tables have schema 'prd'

I've tried multiple things but its not working. I am not sure how to deal with it! I am new to plsql

prd-->schema

work--> table 1 has columns --> proc_id
proc-->table 2 -->proc_id and proc_dtt

output--> count of proc_id in table 1 on today's date in table 2

CodePudding user response：

That looks like join between two tables.

Presuming that proc_dtt contains only date (with no time component, i.e. no hours/minutes/seconds, i.e. they are truncated to midnight), then you'd

select count(*)
from work w join proc p on p.proc_id = w.proc_id
where p.proc_dtt = trunc(sysdate);

You said you want to use PL/SQL - that's Oracle's procedural extension to its SQL. Basically, you do NOT need PL/SQL, but - if you must, then

declare
  l_cnt number;
begin
  select count(*)
  into l_cnt
  from work w join proc p on p.proc_id = w.proc_id
  where p.proc_dtt = trunc(sysdate);

  dbms_output.put_line('Number of today''s records = ' || l_cnt);
end;
/

CodePudding user response：

Not sure what you want. You have to specify more, but if I understood correctly this should work?

SELECT COUNT(*) FROM proc