Home » Developer & Programmer » Reports & Discoverer » Report (Oracle8i and Form6i)
Report [message #416117] Thu, 30 July 2009 09:20 Go to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear Fellow,
I want to design a matix report. My report query is
SELECT ALL GUEST.GUESTNAME, GUEST.ROOMNO, GUEST.NOOFDAYS, 
NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0) RENT, GUEST.EXTRABEDAMT, GUEST.TAXES, 
GUEST.EXTRARENTAL, GUEST.RRAMOUNT, (NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0))+NVL(GUEST.EXTRABEDAMT, 0)+NVL(GUEST.TAXES, 0)+NVL(GUEST.EXTRARENTAL, 0)-NVL(GUEST.RRAMOUNT, 0) TRENTAL, 
OTHCHG.OTHCHGSTITLE, GUESTOTHCHG.AMOUNT
FROM GUEST, OTHCHG, GUESTOTHCHG
WHERE ((GUESTOTHCHG.GUESTID =  GUEST.GUESTID)
 AND (GUESTOTHCHG.OTHCHGID =  OTHCHG.OTHCHGID)
 AND (GUEST.ROOMSTATUS = 'T')) 

It is executing well gives output of those guests who availed the other charges also like minibar,landry etc.

But i wish that the record of those guests should also be displayed who have not avail the other charges along with those who have avail the other charges.
The rows are
GUEST.GUESTNAME, GUEST.ROOMNO, GUEST.NOOFDAYS, 
NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0) RENT, GUEST.EXTRABEDAMT, GUEST.TAXES, 
GUEST.EXTRARENTAL, GUEST.RRAMOUNT, (NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0))+NVL(GUEST.EXTRABEDAMT, 0)+NVL(GUEST.TAXES, 0)+NVL(GUEST.EXTRARENTAL, 0)-NVL(GUEST.RRAMOUNT, 0) TRENTAL

The columns are
OTHCHG.OTHCHGSTITLE

and the matrix column is
GUESTOTHCHG.AMOUNT


Would any one can help me in this regard. I have also tried to outer join sign(+) by sufixing with
GUEST.GUESTID
and
GUESTOTHCHG.GUESTID
and
OTHCHG.OTHCHGID
.

I there is another way please guide me.
Re: Report [message #416191 is a reply to message #416117] Fri, 31 July 2009 01:49 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
I think that i have not properly explained my question, therefore, I have not received any response from any one. I again try to explain my question.

My query generates fetch only those records from the table GUEST who have availed minibar,landry etc i.e theier child record exists in the table GUESTOTHCHG and omit those records where child record in the table GUESTOTHCHG does not exits.

I want to display all those records from the table GUEST who's child records either exist or not in the table GUESTOTHCHG.

I have also tried to use left outer join (+) as
SELECT ALL GUEST.GUESTNAME, GUEST.ROOMNO, GUEST.NOOFDAYS, 
NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0) RENT, GUEST.EXTRABEDAMT, GUEST.TAXES, 
GUEST.EXTRARENTAL, GUEST.RRAMOUNT, (NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0))+NVL(GUEST.EXTRABEDAMT, 0)+NVL(GUEST.TAXES, 0)+NVL(GUEST.EXTRARENTAL, 0)-NVL(GUEST.RRAMOUNT, 0) TRENTAL, 
OTHCHG.OTHCHGSTITLE, GUESTOTHCHG.AMOUNT
FROM GUEST, OTHCHG, GUESTOTHCHG
WHERE ((GUESTOTHCHG.GUESTID =  GUEST.GUESTID(+))
 AND (GUESTOTHCHG.OTHCHGID =  OTHCHG.OTHCHGID)
 AND (GUEST.ROOMSTATUS = 'T')) 

but the result is same i.e. it does not display all records from the table GUEST who have or not the child record in the table GUESTOTHCHG.

Would you please help me in this regard?

Muhammad Khalil
Re: Report [message #416215 is a reply to message #416117] Fri, 31 July 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got the (+) on the wrong side. It needs to go next to the table/column that's optional.
Re: Report [message #416291 is a reply to message #416215] Fri, 31 July 2009 08:32 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks a lot for reply. I feel some encouragement. Please guide/elaborate a little bit more where should i place (+) sign.

Muhammad Khalil
Re: Report [message #416296 is a reply to message #416291] Fri, 31 July 2009 08:44 Go to previous message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
I have solved my problem. The correct query is
SELECT ALL GUEST.GUESTNAME, GUEST.ROOMNO, GUEST.NOOFDAYS, 
NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0) RENT, GUEST.EXTRABEDAMT, GUEST.TAXES, 
GUEST.EXTRARENTAL, GUEST.RRAMOUNT, (NVL(GUEST.NOOFDAYS, 0)*NVL(GUEST.RATE, 0))+NVL(GUEST.EXTRABEDAMT, 0)+NVL(GUEST.TAXES, 0)+NVL(GUEST.EXTRARENTAL, 0)-NVL(GUEST.RRAMOUNT, 0) TRENTAL, 
OTHCHG.OTHCHGSTITLE, GUESTOTHCHG.AMOUNT
FROM GUEST, OTHCHG, GUESTOTHCHG
WHERE ((GUEST.GUESTID = GUESTOTHCHG.GUESTID(+))
 AND (OTHCHG.OTHCHGID(+) = GUESTOTHCHG.OTHCHGID)
 AND (GUEST.ROOMSTATUS = 'T')) 


I am posting the correct solution may it help others.

Muhammad Khalil
Previous Topic: problem in generating pdf
Next Topic: how to install Report Server in Oracle 10g Release 2 (10.1.2.0.2)
Goto Forum:
  


Current Time: Fri Mar 29 06:43:23 CDT 2024