• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
Sheriffs:
  • Devaka Cooray
  • paul wheaton
  • Mark Herschberg
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
  • Jj Roberts
Bartenders:
  • Carey Brown
  • salvin francis
  • Piet Souris

PL/SQL count in different tables

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So i need to pick out information from 2 tables and do a COUNT in one of them in pl/sql. My code so far looks like this:

declare

v_pnr varchar2(13);
v_fnamn varchar2(20) ;
v_enamn varchar2(20);

begin
select count(fordon.pnr),bilägare.fnamn,bilägare.enamn
into v_pnr,v_fnamn,v_enamn
from bilägare,fordon
where bilägare.pnr = fordon.pnr
group by bilägare.fnamn,bilägare.enamn;
dbms_output.put_line('Namn: '||v_fnamn||','||'Efternamn: '||v_enamn||','||'Bilar '||v_pnr);
end;

I know this dosent work. Any way i can fix this?
 
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The common syntax for COUNT is simple SELECT COUNT(*), which returns the number of rows that the WHERE clause of the SQL finds agreeable.

You also appear to be attempting to put the results of what you are doing into non-table fields. So perhaps you could explain what you hope to get.
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So PNR is the main key in BILÄGARE and is a reference key in FORDON. So i want to COUNT how many times the same PNR occurs in FORDON
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I made it sir! Thank you! But I stumbled upon a new problem:

/*This works*/
declare
cursor c_kund is select count(fordon.pnr),bilägare.fnamn,bilägare.enamn
from bilägare,fordon
where bilägare.pnr = fordon.pnr
group by bilägare.fnamn,bilägare.enamn;

v_pnr fordon.pnr%type;
v_fnamn bilägare.fnamn%type;
v_enamn bilägare.enamn%type;

begin
if not c_kund%isopen then  
open c_kund;
end if;

loop
fetch c_kund
into v_pnr,v_fnamn,v_enamn;
exit when c_kund%notfound;
dbms_output.put_line(initcap(v_fnamn)||', '||initcap(v_enamn)||', '||v_pnr);
end loop;

But i need to display one messeage if only one is found and another message if more than one are found something like this:

if v_pnr < 2
dbms_output.put_line(initcap(v_fnamn)||', '||initcap(v_enamn)||', '||v_pnr||'Bil');
else
dbms_output.put_line(initcap(v_fnamn)||', '||initcap(v_enamn)||', '||v_pnr||'Bilar');
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Never mind i made it!
 
Tim Holloway
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Congratulations!

One thing: when you have structured text such as Java code, XML or SQL/DDL, you can use our "code" button in the message editor to wrap the structured text and make it more readable.

Also I would caution you not to become too quick to use PL/SQL to solve all your database problems. There are a number of reasons why the use of stored procedures should be avoided wherever possible, including lack or portability and the extra load it places on the database server. You can do a lot with ordinary (non-procedural) SQL, although I will admit that it requires you to learn to fold your brain in ways you're not used to.
 
Forget Steve. Look at this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic