Home » SQL & PL/SQL » SQL & PL/SQL » Overloading problem (Oracle)
Overloading problem [message #446093] Fri, 05 March 2010 07:28 Go to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

Im currently having a problem with my overload code.
My group made a code that fetches the "free seats" in a cinema
CREATE OR REPLACE
PROCEDURE FINN_LEDIGE_SETER_pp
( p_visningID IN INTEGER
)
IS
lv_visningsid_int INTEGER := p_visningid;
lv_antallmax_int INTEGER;
lv_antalltatt_int INTEGER;

BEGIN

SELECT COUNT(sete.setenr)
INTO lv_antallmax_int
FROM sete
INNER JOIN sal
ON sete.salid = sal.salid
INNER JOIN visning
ON visning.salid = sal.salid
INNER JOIN billett
ON billett.visningsid = visning.visningsid
WHERE visning.visningsid = lv_visningsid_int;

SELECT COUNT(billett.setenr)
INTO lv_antalltatt_int
FROM billett
INNER JOIN visning
ON billett.visningsid = visning.visningsid
WHERE visning.visningsid = lv_visningsid_int;

dbms_output.put_line('det er : ' || (lv_antallmax_int - lv_antalltatt_int) || ' ledige seter');

END FINN_LEDIGE_SETER_pp;



Next task is to get group consists of p_antallSeter seats. This procedure is used when anyone want to buy some (eg 4) tickets and sit together. All seats in a group must be in the same row.

So we tried to use overload, but we are getting it wrong all the time. Can someone give us some tips, hints, and tell us what is wrong ? would be very helpful

create or replace package body finn_ledige_seter_pkg
is
procedure finn_ledige_seter_pp
( p_visningsId in integer
)

IS
lv_visningsid_int INTEGER := p_visningid;
lv_antallmax_int INTEGER;
lv_antalltatt_int INTEGER;

BEGIN

SELECT COUNT(sete.setenr)
INTO lv_antallmax_int
FROM sete
INNER JOIN sal
ON sete.salid = sal.salid
INNER JOIN visning
ON visning.salid = sal.salid
INNER JOIN billett
ON billett.visningsid = visning.visningsid
WHERE visning.visningsid = lv_visningsid_int;

SELECT COUNT(billett.setenr)
INTO lv_antalltatt_int
FROM billett
INNER JOIN visning
ON billett.visningsid = visning.visningsid
WHERE visning.visningsid = lv_visningsid_int;

dbms_output.put_line('det er : ' || (lv_antallmax_int - lv_antalltatt_int) || ' ledige seter');
end;
procedure finn_ledige_seter_pp
( p_visningsId in integer
, p_antallIgruppe in integer
);
end;


Thanks for all help Smile
Re: Overloading problem [message #446096 is a reply to message #446093] Fri, 05 March 2010 07:47 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define getting it wrong.

There is no way we can tell from what you've posted what you are really trying to achieve or what problems you are encountering.

Also I hope you're not relying on dbms_output for proper results - a lot of tools that access the DB don't display it.
Re: Overloading problem [message #446097 is a reply to message #446093] Fri, 05 March 2010 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but we are getting it wrong all the time.

And what does this mean exactly?

Regards
Michel
Re: Overloading problem [message #446099 is a reply to message #446093] Fri, 05 March 2010 07:52 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

We cant understand how to find the seats that is next to eachother in one row. Thats kind of the main problem.

Thanks
Re: Overloading problem [message #446101 is a reply to message #446099] Fri, 05 March 2010 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand the relation between this question and the title.
Or are they not related at all?

Now we don't know your model, you can have a look at the following if your model is closed: Finding adjacent seats in theater

Regards
Michel
Re: Overloading problem [message #446105 is a reply to message #446093] Fri, 05 March 2010 08:26 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

Ok im sorry if im writing this in a bad way, let me try to explain.

We first created FINN_LEDIGE_SETER_PP to find how many free seats it is in one showing, for one movie, at one cinema.

Then we wanted to make an overload procedure that took the same parameters as the before mentioned procedure, adding one parameter for how many people wanting to sit together (in a row). This procedure is used when a group of people buy tickets and want to sit on the same row.
I got a picture here:
bildr.no/image/601746.jpeg

Its kinda hard to describe, i hope you understand better now.
Re: Overloading problem [message #446108 is a reply to message #446093] Fri, 05 March 2010 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That much we understood.

What we don't understand is:
a) what actual problems you are having in creating the overloaded procedure.
b) How your data is stored in order to work out a query that'll get the results you want.
c) What exact results you do want.

Remember we know nothing about your database except what you tell us.
We don't know what data is in what tables, and since the table names aren't in English most of us can't even make an educated guess.
We don't know how the tables relate to each other.
Re: Overloading problem [message #446112 is a reply to message #446105] Fri, 05 March 2010 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually what you have to do is to create one procedure with last parameter with a default value (null for instance), then you can call it with 2 or 3 parameters.

Regards
Michel
Re: Overloading problem [message #446123 is a reply to message #446105] Fri, 05 March 2010 09:07 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Overloaded procedures and functions are created within packages. An overloaded object is actually several objects that all have the same name, but each object differs from the others in type and/or number of parameters. See the fine manual:
Overloading Subprogram Names

Best luck.
Re: Overloading problem [message #446138 is a reply to message #446123] Fri, 05 March 2010 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And overloading is not necessary (and more can't be used) in this case, just a default value on a parameter.

Regards
Michel
Re: Overloading problem [message #446434 is a reply to message #446093] Mon, 08 March 2010 05:55 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member


a) We want to create a procedure that: is used when anyone want to buy some (example 4) tickets in a cinema and sit together. All seats in a group must be in the same row and next to eachother. The procedure is only gonna write how many groups there are available in total for the showing.

b) How your data is stored in order to work out a query that'll get the results you want:
Our data is stored like this:
Showing- showingId(PK), movieId(fk), auditoriumId(fk), cinemaId(fk)
Cinema - CinemaId(PK),name, place etc
Auditorium - CinemaId(PK,FK) AuditoriumId(PK)
Seat - Row(PK), Seat(PK), cinemaId(PK,FK), AuditoriumId(PK,FK)
Ticket - ticketId(pk), showingId(fk), row(fk), seat(fk)


c) We want one or two procedurses that showes us how many seats availible and how many seats there are in groups(One group is 2 or more that want to sit together) based on what the user typed into the parameter.

How do we procede with this task?
Re: Overloading problem [message #446436 is a reply to message #446434] Mon, 08 March 2010 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Quote:
next to eachother

How is this explained in your model? How can we know that 2 seats are next to each other?

Regards
Michel

[Updated on: Mon, 08 March 2010 06:17]

Report message to a moderator

Re: Overloading problem [message #446439 is a reply to message #446093] Mon, 08 March 2010 06:15 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

If i posted it the right place, here is some of the database orafaq.com/wiki/Talk:Test_case
All the database tables are filled in with testdata.

Quote:
How can we know that 2 seats are next to each other?
Thats what we are trying to find out.

We use this code to find free seats and on wich row they are, the question is how are we going to use this data to find seats in groups?
SELECT seat.seatnr as seat, seat.row as row
FROM seat
INNER JOIN auditorium
ON seat.auditoriumid = auditorium.auditoriumid
INNER JOIN showing
ON showing.auditoriumid = auditorium.auditoriumid
INNER JOIN ticket
ON ticket.showingsid = showing.showingsid
WHERE showing.showingsid = 1
MINUS
SELECT ticket.seatnr as seat, ticket.row as row
FROM ticket
INNER JOIN showing
ON ticket.showingsid = showing.showingsid
WHERE showing.showingsid = 1
ORDER BY seat DESC;


We get this from the select statement:
Seat Row
---------- ----------
9 2
9 1
8 2
8 1
7 2
7 1
6 2
5 2
5 1
4 1
4 2
3 1
3 2
2 2
2 1
10 2
10 1
1 2


Thanks for helping everyone, really nice Smile

[Updated on: Mon, 08 March 2010 07:05]

Report message to a moderator

Re: Overloading problem [message #446443 is a reply to message #446439] Mon, 08 March 2010 07:06 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lebowski wrote on Mon, 08 March 2010 12:15
If i posted it the right place, here is some of the database orafaq.com/wiki/Talk:Test_case


Nope - you should have posted it here using code tags like this:

create table cinema ( cinemaId int NOT NULL, CONSTRAINT cinemaId_pk Primary key (cinemaId), ) 

create table auditorium ( auditoriumId int NOT NULL, cinemaId int NOT NULL, CONSTRAINT auditoriumId_pk Primary key (auditoriumId), CONSTRAINT cinemaId_fk Foreign key (cinemaId) References cinema (cinemaId) 

create table showing ( showingsId int NOT NULL, filmId int NOT NULL, auditoriumId int NOT NULL, cinemaId int NOT NULL, CONSTRAINT showingsId_pk Primary key (showingsId), CONSTRAINT filmId_fk Foreign key (filmId) References Filminfo (filmId), CONSTRAINT auditoriumId_fk Foreign key (auditoriumId) References auditorium (auditoriumId), CONSTRAINT cinemaId_fk2 Foreign key (cinemaId) References cinema (cinemaId) 

create table seat ( row varchar2(10), cinemaId int NOT NULL, seatNr varchar2(10) NOT NULL, auditoriumId int NOT NULL, CONSTRAINT rad_pk Primary key (rad,cinemaId,seatNr,auditoriumId), CONSTRAINT cinemaId_fk3 Foreign key (cinemaId) References cinema (cinemaId), CONSTRAINT auditoriumId_fk2 Foreign key (auditoriumId) References auditorium (auditoriumId) 


You should also post it in a form that'll actually run - I can tell from a glance that won't.

Lebowski wrote on Mon, 08 March 2010 12:15

All the database tables are filled in with testdata.

Which unfortunately isn't any use to us unless you give us some insert statements to populate our copies of your tables.

Lebowski wrote on Mon, 08 March 2010 12:15

Quote:
How can we know that 2 seats are next to each other?
Thats what we are trying to find out.


Well If you don't know how to tell which seats are together we haven't got a hope.
This isn't a SQL problem - you haven't got that far.
This is a basic business logic problem.
Until you tell us how to tell from your data which seats are grouped together there is nothing we can do to help.
If you can tell us the relationship we can give you SQL that'll do what you want, but without it - no.
Re: Overloading problem [message #446444 is a reply to message #446093] Mon, 08 March 2010 07:13 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

I edited my previous post to show you what kind of results we got now.

This database world is exciting.
Re: Overloading problem [message #446592 is a reply to message #446093] Tue, 09 March 2010 08:17 Go to previous messageGo to next message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

We cant get further with getting the groups, if someone understand my post where i wrote the code and the result and how to use that info to get groups it would be really nice.

Thanks
Re: Overloading problem [message #446601 is a reply to message #446093] Tue, 09 March 2010 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you give us a working test case - i.e. create table statements that'll actually run along with insert statements to populate the tables with usable data - then we'll be able to recreate your setup on our DBs and play with it to come up with something that works.
Remember we all have day jobs, if you make it easier for us to help you then you're far more likely to get a usefull answer.

EDIT: meant create table statements and insert statements. NOt insert statements and insert statements.

[Updated on: Tue, 09 March 2010 09:08]

Report message to a moderator

Re: Overloading problem [message #446604 is a reply to message #446093] Tue, 09 March 2010 08:55 Go to previous message
Lebowski
Messages: 8
Registered: March 2010
Junior Member

I understand, let me see what we can do about that. Thanks cookiechief.

[Updated on: Tue, 09 March 2010 09:22]

Report message to a moderator

Previous Topic: Problem in creating body of package! (merged by CM)
Next Topic: Materializd
Goto Forum:
  


Current Time: Sat Dec 03 19:55:30 CST 2016

Total time taken to generate the page: 0.11088 seconds