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
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;
/
Is there any other way to do this so that i can retrieve the real total count of people in that SUBURB
Some datas from PERSON TABLE
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
SOME DATA from PROPERTY TABLE
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
Sorry for the way the table is looking .
This is the value I get when I run the above script.
SQL> exec p1('Randwick')
Number of People :50
Number of property :33
I changed the PROCEDURE ,this is what I get .
SQL> 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> exec p1('KINGSFORD')
Number of People :0
Number of property :0
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exec p1('Randwick')
Number of People :0
Number of property :0
PL/SQL procedure successfully completed.
SQL>
The solution suppose to be this
SQL> exec p1('randwick');
Number of People: 7
Number of Property: 2
Best Answer
You named the variable the same as the field. In the query,
suburb
is first sought in the scope of the query, and it matches the fieldsuburb
even though it doesn't use thepp
table alias.So you're actually comparing the field with itself, therefore getting all records (where
suburb
is NOT NULL, that is). The procedure parameter isn't used in the query at all.The solution: change the name of the procedure parameter.
To prevent errors like this, I always use
P_
as a prefix for procedure/function parameters andV_
as a prefix for local variables. This way, they never mingle with field names.