Home » Developer & Programmer » Reports & Discoverer » 0 instead of null column based on 2-queries (CM merged 2)
Re: table With OuterJoine [message #617408 is a reply to message #617406] Sun, 29 June 2014 08:35 Go to previous messageGo to next message
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 #617410 is a reply to message #617408] Sun, 29 June 2014 08:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you also apply "Break order" for the AWS field?
Re: table With OuterJoine [message #617419 is a reply to message #617410] Sun, 29 June 2014 21:21 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Yes/forum/fa/11993/0/
Re: table With OuterJoine [message #617434 is a reply to message #617419] Mon, 30 June 2014 02:59 Go to previous messageGo to next message
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 #617435 is a reply to message #617434] Mon, 30 June 2014 03:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like you've got aws in the wrong group. It needs to be in the same group as aa1.
Re: table With OuterJoine [message #617462 is a reply to message #617435] Mon, 30 June 2014 06:41 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
i have placed that aws in the correct group now it is arranged the months. some columns not showing the 0 instead of Null.
please advised...
Re: table With OuterJoine [message #617463 is a reply to message #617462] Mon, 30 June 2014 06:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you try my revised query above?
Re: table With OuterJoine [message #617535 is a reply to message #617463] Tue, 01 July 2014 06:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
This query works fine in SQLplus. but not in report. how i can use this query in reports????
Re: table With OuterJoine [message #617536 is a reply to message #617535] Tue, 01 July 2014 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what happened when you tried to use it in the report?
Re: table With OuterJoine [message #617769 is a reply to message #617536] Thu, 03 July 2014 08:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
how i can Use this in report main query
WITH mon AS (SELECT add_months(TRUNC(to_Date('01-APR-2014', 'DD-MON-YYYY')), ROWNUM - 1) mn
             FROM dual CONNECT BY LEVEL < 4)
Re: table With OuterJoine [message #617775 is a reply to message #617769] Thu, 03 July 2014 09:20 Go to previous messageGo to next message
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 #617838 is a reply to message #617775] Thu, 03 July 2014 22:43 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Report version: 6.0 8.8.3
Re: table With OuterJoine [message #617874 is a reply to message #617838] Fri, 04 July 2014 03:02 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Report engine crashed
Next Topic: how to use param's in reports
Goto Forum:
  


Current Time: Fri Mar 29 03:53:07 CDT 2024