Databases
StackOverflow2013
Postsdbo
POCOUNT in PLSQL ORACLE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCOUNT in PLSQL ORACLE
    primarykey
    data
    text
    <p>I have asked this question before but I did not get any help. I want to get the count of rows in two different table given an attribute. This is my code . Instead of fetching the total count where the condition holds, I am getting the whole count of the table </p> <pre><code> create or replace PROCEDURE p1( suburb IN varchar2 ) as person_count NUMBER; property_count NUMBER; BEGIN SELECT count(*) INTO person_count FROM person p WHERE p.suburb = suburb ; SELECT count(*) INTO property_count FROM property pp WHERE pp.suburb = suburb ; dbms_output.put_line('Number of People :'|| person_count); dbms_output.put_line('Number of property :'|| property_count); END; / </code></pre> <p>Is there any other way to do this so that i can retrieve the real total count of people in that SUBURB</p> <p>Some datas from PERSON TABLE</p> <pre><code> PEID FIRSTNAME LASTNAME ---------- -------------------- -------------------- STREET SUBURB POST TELEPHONE ---------------------------------------- -------------------- ---- ------------ 30 Robert Williams 1/326 Coogee Bay Rd. Coogee 2034 9665-0211 32 Lily Roy 66 Alison Rd. Randwick 2031 9398-0605 34 Jack Hilfgott 17 Flood St. Bondi 2026 9387-0573 </code></pre> <p>SOME DATA from PROPERTY TABLE</p> <pre><code> PNO STREET SUBURB POST ---------- ---------------------------------------- -------------------- ---- FIRST_LIS TYPE PEID --------- -------------------- ---------- 48 66 Alison Rd. Randwick 2031 12-MAR-11 Commercial 8 49 1420 Arden St. Clovelly 2031 27-JUN-10 Commercial 82 50 340 Beach St. Clovelly 2031 05-MAY-11 Commercial 38 </code></pre> <p>Sorry for the way the table is looking .</p> <p>This is the value I get when I run the above script.</p> <pre><code> SQL&gt; exec p1('Randwick') Number of People :50 Number of property :33 </code></pre> <p>I changed the PROCEDURE ,this is what I get .</p> <pre><code> SQL&gt; create or replace PROCEDURE p1( location varchar2 ) IS person_count NUMBER; property_count NUMBER; BEGIN SELECT count(p.peid) INTO person_count FROM person p WHERE p.suburb = location ; SELECT count(pp.pno) INTO property_count FROM property pp WHERE pp.suburb = location ; dbms_output.put_line('Number of People :'|| person_count); dbms_output.put_line('Number of property :'|| property_count); END; / 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Procedure created. SQL&gt; exec p1('KINGSFORD') Number of People :0 Number of property :0 PL/SQL procedure successfully completed. SQL&gt; SQL&gt; SQL&gt; exec p1('Randwick') Number of People :0 Number of property :0 PL/SQL procedure successfully completed. SQL&gt; </code></pre> <p>The solution suppose to be this </p> <pre><code> SQL&gt; exec p1('randwick'); Number of People: 7 Number of Property: 2 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USshakur007
    1. USshakur007
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COplease show some data from the 2 tables and the value of the param `suburb`
      singulars
      1. POCOUNT in PLSQL ORACLE
      1. USYahia
    2. COYour last two examples differ. The penultimate says you supply 'Randwick' and the last says you supply 'randwick' (with a lower case "r"). You issue is probably associated with how you are storing your suburb value and what case your parameters are that are being passed to it. You must standardise them to make sure the match is made.
      singulars
      1. POCOUNT in PLSQL ORACLE
      1. USOllie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload