Home » Developer & Programmer » Reports & Discoverer » 0 instead of null column based on 2-queries (CM merged 2)
|
|
|
|
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #613611 is a reply to message #613559] |
Thu, 08 May 2014 08:52   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
COUNT returns NULL when you group the rows using GROUP BY clause. If you remove the group by clause then count will return zero instead of no records(NULL).
The reason for this is :
- Without a group by clause, row aggregation happens for all the rows satisfying the where clause.
- When you apply group by, first the filter is applied, then the rows are grouped and then the count is applied. So, for a group which do not satisfy the filter condition, what is there to count at all? Hence, nothing to return or say NULL.
In your case, if you cannot remove group by, then as LF suggested, use NVL.
Hope that helps!
[Updated on: Thu, 08 May 2014 08:53] Report message to a moderator |
|
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #613623 is a reply to message #613617] |
Thu, 08 May 2014 09:21 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Something like this:
SELECT STUDENT.CLASS||' (' || STUDENT.SECTION || ')' , COUNT(STUDENT.STUID),
count(ABSENT1.ASTUID) aas
FROM ABSENT1, STUDENT
WHERE (ABSENT1.ABDATE = ABSENT.ABDATE)
and student.stuid=absent1.astuid(+)
and status='PRESENT'
AND ABSENT1.ABDATE(+)=:P_1
GROUP BY
STUDENT.CLASS||' (' || STUDENT.SECTION || ')'
You don't need two queries. You don't need to join to the absent table, it does nothing useful.
You just need to outer join absent1 to student and count data from each table.
If there are no matching absent1 records the count will be 0.
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #613626 is a reply to message #613611] |
Thu, 08 May 2014 09:43 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 08 May 2014 14:52COUNT returns NULL when you group the rows using GROUP BY clause. If you remove the group by clause then count will return zero instead of no records(NULL).
Don't confuse NULL and no rows, they are not equivalent. Count never returns null. The other aggregates will return null if there are no matching rows.
OP isn't getting null at any point, he's getting no rows for his 2nd query for certain rows from the 1st and nvl will do nothing to fix that.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615703 is a reply to message #615702] |
Sat, 07 June 2014 09:36 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
create table absent (abdate date not null);
create table absent1 (abdate date references absent(abdate),astuid number(6),arem varchar2(30));
create table student (stuid number(6) primary key,status varchar2(30),name varchar2(90),f_name varchar2(90),class varchar2(20),section varchar2(30))
if all student of one class are present on that particular day. this class will be shown and count(absent) will be 0.
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615751 is a reply to message #615706] |
Sun, 08 June 2014 21:48 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
SQL> insert into student (stuid,status,name,f_name,class,section)
values
(1,'PRESENT','ASLAM','NOOR','FIVE','PINK');
insert into student (stuid,status,name,f_name,class,section)
values
(2,'PRESENT','ALAM','NAZIR','FIVE','PINK');
insert into student (stuid,status,name,f_name,class,section)
values
(3,'PRESENT','AM','NR','FIVE','PINK');
insert into student (stuid,status,name,f_name,class,section)
values
(4,'PRESENT','A','N','FIVE','PINK');
Insert into student (stuid,status,name,f_name,class,section)
values
(5,'PRESENT','ASL','NO','FOUR','WHITE');
Insert into student (stuid,status,name,f_name,class,section)
values
(6,'PRESENT','AA','N','FOUR','WHITE');
Insert into student (stuid,status,name,f_name,class,section)
values
(7,'PRESENT','AAA','AAO','FOUR','WHITE');
Insert into student (stuid,status,name,f_name,class,section)
values
(8,'PRESENT','ASA','52OR','SIX','BLUE');
-------------------------------------------------------------------
INSER INTO ABSENT (ABDATE) VALUES ('13-MAY-2014');
INSER INTO ABSENT (ABDATE) VALUES ('14-MAY-2014');
INSER INTO ABSENT (ABDATE) VALUES ('15-MAY-2014');
--------------------------------------------------------------------
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('13-MAY-2014',1,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('13-MAY-2014',3,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('13-MAY-2014',4,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('14-MAY-2014',6,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('14-MAY-2014',7,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('15-MAY-2014',8,'ABSENT');
INSERT INTO ABSENT1 (abdate,astuid,arem)
VALUES
('15-MAY-2014',5,'ABSENT');
--------------------------------------------------------------
pLEASE ADVISED. TRYING TO INCREASE CLASSES AND SECTION FOR MORE VAREITY.
[Updated on: Sun, 08 June 2014 21:50] Report message to a moderator |
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615764 is a reply to message #615703] |
Mon, 09 June 2014 02:30 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
shahzad-ul-hasan wrote on Sat, 07 June 2014 20:06create table absent (abdate date not null);
create table absent1 (abdate date references absent(abdate),astuid number(6),arem varchar2(30));
How can abdate in absent1 table reference abdate in absent table when you have not mentioned abdate to be the primary key? Correct the DDL.
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615765 is a reply to message #615751] |
Mon, 09 June 2014 02:32 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
shahzad-ul-hasan wrote on Mon, 09 June 2014 08:18
INSER INTO ABSENT (ABDATE) VALUES ('13-MAY-2014');
INSER INTO ABSENT (ABDATE) VALUES ('14-MAY-2014');
INSER INTO ABSENT (ABDATE) VALUES ('15-MAY-2014');
Did you even execute the statements before posting? INSERT has a "T" missing. Also, always remember to provide the proper date format using to_date for date values.
|
|
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615784 is a reply to message #615771] |
Mon, 09 June 2014 04:16 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
SELECT STUDENT.CLASS||' (' || STUDENT.SECTION || ')' , COUNT(STUDENT.STUID),
count(ABSENT1.ASTUID) aas
FROM ABSENT1, STUDENT
WHERE (ABSENT1.ABDATE = ABSENT.ABDATE)
and student.stuid=absent1.astuid(+)
and status='PRESENT'
AND ABSENT1.ABDATE(+)=:P_1
GROUP BY
STUDENT.CLASS||' (' || STUDENT.SECTION || ')'
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #615788 is a reply to message #615784] |
Mon, 09 June 2014 04:34 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That query gives one row per "STUDENT.CLASS||' (' || STUDENT.SECTION || ')'" with a count for strength and a count for absent.
This would give the output you say you want.
It will not give the output in the last image you posted as that has one record for the strength for each group and one record for the absent for each group.
So what exactly did you do in the report to get the output in your last image? Did you use two queries?
|
|
|
|
|
|
|
|
|
|
| Re: 0 instead of null column based on 2-queries [message #617153 is a reply to message #617150] |
Wed, 25 June 2014 09:14 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Couple of points:
1) It would have been better to copy and paste the text from sqlplus and post it here in code tags, rather than use an image.
2) My example code has two (+), yours only has 1 so the outer join isn't an outer join any more, instead it's an inner join. Add the missing (+)
3) As I said above you don't need the absent table, it does nothing useful (and yes I know theres a reference to it in my example, I left it there by mistake).
|
|
|
|
|
|
| table With OuterJoine [message #617318 is a reply to message #613503] |
Fri, 27 June 2014 06:35 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
I have a table with Structure..
SQL> desc absent1
Name Null? Type
----------------------------------------- -------- -----------------------
ABDATE DATE
ASTUID NUMBER(9)
ABTYPE VARCHAR2(45)
Insert into absent1 (abdate,astuid,abtype)
values
('20-APR-2014',12,'ABSENT');
Insert into absent1 (abdate,astuid,abtype)
values
('20-APR-2014',16,'ABSENT');
Insert into absent1 (abdate,astuid,abtype)
values
('20-APR-2014',10,'LEAVE');
Insert into absent1 (abdate,astuid,abtype)
values
('20-APR-2014',42,'LEAVE');
One table snd output Should Be in Absent AND Leave Column...
|
|
|
|
|
|
|
|
|
|
| Re: table With OuterJoine [message #617326 is a reply to message #617323] |
Fri, 27 June 2014 07:30 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The question makes a lot more sense if you tack it onto the existing one, since it appears to be a variation of the above problem with an extra column.
So I've merged the two topics.
Count counts not null instances of an expression. So you use a case statement to give a not null thing to count:
count(CASE WHEN absent1.abtype = 'LEAVE' THEN ABSENT1.ASTUID END) as leave
|
|
|
|
|
|
| Re: table With OuterJoine [message #617387 is a reply to message #617326] |
Sat, 28 June 2014 08:42 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
Dear Sir,
i am making a matrix with group report.i am using your query with some amendments.please review.
SELECT STUDENT.CLASS|| ' ' ||
STUDENT.SECTION ss, STUDENT.NAME,student.stuid, count(astuid),substr(last_day(max(abdate)),1,2) aa,substr(last_day(abdate),4,3) aa1
FROM ABSENT1, STUDENT
where status='PRESENT'
and absent1.astuid(+)=student.stuid
group by
STUDENT.CLASS|| ' ' ||
STUDENT.SECTION, STUDENT.NAME, student.STUID,substr(last_day(abdate),4,3)
order by substr(last_day(max(abdate)),1,2)
Advisory No -1 (required): i want to arrange the Months in that format:
April - May - Jun (But it is showing Apr- Jun- May)
Advisory No -2 (required): the null columns not showing 0 instead of null values.Please see the attached file.
Please advised.
[Updated on: Sat, 28 June 2014 08:43] Report message to a moderator |
|
|
|
|
|
| Re: table With OuterJoine [message #617393 is a reply to message #617390] |
Sun, 29 June 2014 00:53 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
Order by clause....
Abdate contains (01-May-2014,02-May-2014,05-May-2014,14-May-2014,20-May-2014,01-Jun-2014,04-Jun-2014,06-Jun-2014)
i want to arrange them in May, June instead of Jun, May....
The students(Astuid) which is not present in Absent1 table showing 0 instead of null;
|
|
|
|
|
|
| Re: table With OuterJoine [message #617405 is a reply to message #617396] |
Sun, 29 June 2014 06:21 |
shahzad-ul-hasan
Messages: 640
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(last_day(abdate),'DD') AA
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(last_day(abdate),'DD')
i have made the changes..THE RESULT IS SAME....
|
|
|
|
| Re: table With OuterJoine [message #617406 is a reply to message #617405] |
Sun, 29 June 2014 06:40 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Where did you make those changes? I'm not sure what you meant to say with the MON format mask, as it returns month name (not number, as I suggested). Moreover, you didn't specify ORDER BY? All I can see is GROUP BY (you can't expect Oracle to sort values based on GROUP BY - ORDER BY is obligatory).
SQL> SELECT ename,
2 hiredate,
3 TO_CHAR (hiredate, 'mon') month_1,
4 TO_CHAR (hiredate, 'mm') month_2
5 FROM emp
6 WHERE deptno = 20
7 ORDER BY TO_CHAR (hiredate, 'mm');
ENAME HIREDATE MON MO
---------- ---------- --- --
ADAMS 12.01.1983 jan 01
JONES 02.04.1981 apr 04
FORD 03.12.1981 dec 12
SMITH 17.12.1980 dec 12
SCOTT 09.12.1982 dec 12
SQL>
Besides, check what is Break Order for month field property set to. If necessary, modify it to "Ascending".
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 01:35:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
