Re: table With OuterJoine [message #617408 is a reply to message #617406] |
Sun, 29 June 2014 08:35 |
shahzad-ul-hasan
Messages: 615 Registered: August 2002
|
Senior Member |
|
|
SELECT STUDENT.CLASS|| ' ' ||
STUDENT.SECTION ss, STUDENT.NAME,student.stuid, count(astuid),to_char(LAST_DAY(abdate),'MON') aa1,to_char(abdate,'MM') aws
FROM ABSENT1, STUDENT
where status='PRESENT'
and absent1.astuid(+)=student.stuid
group by
STUDENT.CLASS|| ' ' ||
STUDENT.SECTION, STUDENT.NAME, student.STUID,to_char(last_day(abdate),'MON'),to_char(abdate,'MM')
order by to_char(abdate,'MM')
nothing happend
|
|
|
|
|
Re: table With OuterJoine [message #617434 is a reply to message #617419] |
Mon, 30 June 2014 02:59 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
What is the revised query supposed to do?
I suspect you need to join to a calendar query, e.g.
WITH mon AS (SELECT add_months(TRUNC(to_Date('01-APR-2014', 'DD-MON-YYYY')), ROWNUM - 1) mn
FROM dual CONNECT BY LEVEL < 4)
SELECT stmn.CLASS|| ' ' || stmn.SECTION ss,
stmn.NAME,
stmn.stuid,
count(astuid),
to_char(mn,'MON') aa1,
to_char(mn,'MM') aws
FROM (SELECT STUDENT.CLASS, STUDENT.SECTION,
STUDENT.NAME,
student.stuid,
mn
FROM STUDENT, mon
where status='PRESENT') stmn,
absent1
WHERE absent1.astuid(+)=stmn.stuid
AND trunc(absent1.abdate(+), 'MON')=stmn.mn
group by
stmn.CLASS|| ' ' ||
stmn.SECTION, stmn.NAME, stmn.STUID,to_char(mn,'MON'),to_char(mn,'MM')
order by to_char(mn,'MM');
|
|
|
|
|
|
|
|
|
Re: table With OuterJoine [message #617775 is a reply to message #617769] |
Thu, 03 July 2014 09:20 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Depending on your version of reports that will either work as is, probably replacing the date with a parameter, or you will have to create a view based on my query and then query that. In the later case you'll need probably need to use a parameterized view.
Or you could try a ref cursor.
|
|
|
|
Re: table With OuterJoine [message #617874 is a reply to message #617838] |
Fri, 04 July 2014 03:02 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Either your version of reports recognizes WITH or it doesn't. I'm not in a position to check, you are.
If it doesn't work I've suggested alternatives.
|
|
|
Re: table With OuterJoine [message #617878 is a reply to message #617874] |
Fri, 04 July 2014 03:19 |
|
Littlefoot
Messages: 21806 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
6i is an old piece of sotware; I'm not sure WITH factoring clause was invented back then. Can't verify myself either, though.
|
|
|