Home » Developer & Programmer » Reports & Discoverer » Report (Oracle8i and Form6i)
| Report [message #416117] |
Thu, 30 July 2009 09:20  |
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
and the matrix column is
Would any one can help me in this regard. I have also tried to outer join sign(+) by sufixing with and and .
I there is another way please guide me.
|
|
|
|
| Re: Report [message #416191 is a reply to message #416117] |
Fri, 31 July 2009 01:49  |
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 |
cookiemonster
Messages: 13962
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 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Wed Jan 08 20:10:41 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
