Home » Developer & Programmer » Reports & Discoverer » show all values instead of null or as field type. (developer 6i,)
| show all values instead of null or as field type. [message #633981] |
Mon, 02 March 2015 04:09  |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Dear Sir.
I have a table Student and other table Absent1. please view the structure:
SQL> desc student
Name Null? Type
----------------------------------------- -------- ---------------------
STUID NOT NULL NUMBER(8)
STATUS VARCHAR2(30)
CLASS VARCHAR2(25)
SECTION VARCHAR2(30)
SESION VARCHAR2(30)
ROLLNO NUMBER(4)
ADM_NO VARCHAR2(25)
STUDENTID VARCHAR2(15)
NAME VARCHAR2(250)
F_NAME VARCHAR2(250)
SQL> desc absent1
Name Null? Type
----------------------------------------- -------- --------------------------
ABDATE DATE
ASTUID NUMBER(9)
AREMARK VARCHAR2(200)
ABTYPE VARCHAR2(45)
The student contains main information and absent1 table contains students absent information. the students which is not absent on specific date i want to show them present students and which is absent at absent1 table.they are shown as absent. the main query for report is:
SELECT distinct STUDENT.STUID, ABSENT1.ABDATE,' '||STUDENT.NAME name,ABTYPE
FROM STUDENT, ABSENT1
where student.status='PRESENT'
AND STUDENT.STUID=ABSENT1.ASTUID(+)
ORDER BY STUID
The Required output which i required.
-
Attachment: 3434.JPG
(Size: 45.14KB, Downloaded 1670 times)
|
|
|
|
|
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #633990 is a reply to message #633989] |
Mon, 02 March 2015 05:49  |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Test case:
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',1,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',3,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',5,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',6,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',3,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',5,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',7,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',7,'ABSENT');
|
|
|
|
| Re: show all values instead of null or as field type. [message #633994 is a reply to message #633990] |
Mon, 02 March 2015 07:06 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
A test case would include data for all relevant tables, and I really shouldn't need to keep pointing that out to you.
You need a table that has an entry for each date in the range. If one doesn't exist then you need to use a row-generator to mimic one.
You then outer-join absent1 to that and use a case statement to show what you need.
|
|
|
|
| Re: show all values instead of null or as field type. [message #634020 is a reply to message #633994] |
Tue, 03 March 2015 00:53 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
this is the all tables.
insert into student (stuid,status,name,class,section)
values
(1,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(2,'PRESENT','BC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(3,'PRESENT','DFG','PREP','A');
insert into student (stuid,status,name,class,section)
values
(4,'PRESENT','DBC','ONE','A');
insert into student (stuid,status,name,class,section)
values
(5,'PRESENT','AC','ONE','A');
insert into student (stuid,status,name,class,section)
values
(6,'PRESENT','ABC','ONE','A');
insert into student (stuid,status,name,class,section)
values
(7,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(8,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(9,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(10,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(11,'PRESENT','ABC','PREP','A');
insert into student (stuid,status,name,class,section)
values
(12,'PRESENT','ABC','ONE','A');
insert into student (stuid,status,name,class,section)
values
(13,'PRESENT','ABC','TWO','A');
insert into student (stuid,status,name,class,section)
values
(14,'PRESENT','ABC','TWO','A');
insert into student (stuid,status,name,class,section)
values
(15,'PRESENT','ABC','TWO','A');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',1,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',3,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',5,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('02-APR-2014',6,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',3,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',5,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',7,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',7,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',7,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',2,'ABSENT');
insert into absent1 (abdate,astuid,abtype)
values
('03-APR-2014',3,'ABSENT');
please view the attached file it is showing this result.
-
Attachment: 33333.JPG
(Size: 63.60KB, Downloaded 1603 times)
[Updated on: Tue, 03 March 2015 01:29] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #634033 is a reply to message #634030] |
Tue, 03 March 2015 04:16 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I'm not very happy with anti-test cases ("anti" because they don't represent the story someone is telling). For example, your ABSENT1 table looks like this:
SQL> select abdate, astuid, abtype from absent1 order by abdate, astuid;
ABDATE ASTUID ABTYPE
-------- ---------- ---------------------------------------------
02.04.14 1 ABSENT
02.04.14 3 ABSENT
02.04.14 5 ABSENT
02.04.14 6 ABSENT
03.04.14 2 ABSENT
03.04.14 3 ABSENT
03.04.14 3 ABSENT ???
03.04.14 5 ABSENT
03.04.14 7 ABSENT
03.04.14 7 ABSENT ???
03.04.14 7 ABSENT ???
11 rows selected.
SQL>
Besides, ABDATE's data type is DATE. You used '02-APR-2014' in your INSERTs. That won't work for many of us (certainly doesn't for me). First of all, you are inserting a string into a DATE datatype column and force Oracle to attempt implicit conversion. It probably works for you, but doesn't for me. My (default) date format is much different from that; furthermore, me and my database speak Croatian, so your 'APR' is, acutally, 'TRA' for me. So I had to edit all those statements in order to be able to insert data into tables. Next time, stick to date format which is easily recognized, such asto_date('02-04-2014', 'dd-mm-yyyy')
Or, your STUDENT table: what is the purpose of the STATUS column? It contains 'PRESENT' for everyone. I thought that presence is based on contents of the ABSENT1 table; is it not?
Moreover, you failed to post CREATE TABLE statements (so I had to type them too).
Generally speaking, you didn't do a good job here.
Anyway: here's one option; see what it does and adjust it if necessary. Row generator techniques are behind the link - see what's offered and pick the one you find the most appropriate. I pretended it is 03.04.2014 today (DD.MM.YYYY), truncated it to 'MM' (first of the month) and created 4 consecutive days.
You should outer join dates with the ABSENT1 table (on the date column), but there's a problem as you have to outer join ABSENT1 table with the STUDENT table, and you can't outer join the same table to two (or more) different tables. I joined STUDENT and ABSENT1, while dates are taken care of within the CASE.
SQL> with dates as
2 -- Row generator technique; I'm creating 4 dates in range between
3 -- 01.04.2014 - 04.04.2014 (DD.MM.YYYY)
4 (select trunc(date '2014-04-03', 'mm') + level - 1 datum
5 from dual
6 connect by level <= 4
7 )
8 select
9 d.datum,
10 s.stuid,
11 s.name,
12 -- MIN(CASE ...) because - if someone is ABSENT, you want to select that
13 -- value, not PRESENT ('present' > 'absent')
14 min(case when a.astuid = s.stuid and
15 a.abdate = d.datum and
16 a.abtype = 'ABSENT'
17 then 'absent'
18 else 'present'
19 end
20 ) abtype
21 from student s,
22 dates d,
23 absent1 a
24 where a.astuid (+) = s.stuid
25 group by d.datum, s.stuid, s.name
26 order by d.datum, s.stuid;
DATUM STUID NAME ABTYPE
-------- ---------- ---------- -------
01.04.14 1 ABC present
01.04.14 2 BC present
01.04.14 3 DFG present
01.04.14 4 DBC present
01.04.14 5 AC present
01.04.14 6 ABC present
01.04.14 7 ABC present
01.04.14 8 ABC present
01.04.14 9 ABC present
01.04.14 10 ABC present
01.04.14 11 ABC present
01.04.14 12 ABC present
01.04.14 13 ABC present
01.04.14 14 ABC present
01.04.14 15 ABC present
02.04.14 1 ABC absent
02.04.14 2 BC present
02.04.14 3 DFG absent
02.04.14 4 DBC present
02.04.14 5 AC absent
02.04.14 6 ABC absent
02.04.14 7 ABC present
02.04.14 8 ABC present
02.04.14 9 ABC present
02.04.14 10 ABC present
02.04.14 11 ABC present
02.04.14 12 ABC present
02.04.14 13 ABC present
02.04.14 14 ABC present
02.04.14 15 ABC present
03.04.14 1 ABC present
03.04.14 2 BC absent
03.04.14 3 DFG absent
03.04.14 4 DBC present
03.04.14 5 AC absent
03.04.14 6 ABC present
03.04.14 7 ABC absent
03.04.14 8 ABC present
03.04.14 9 ABC present
03.04.14 10 ABC present
03.04.14 11 ABC present
03.04.14 12 ABC present
03.04.14 13 ABC present
03.04.14 14 ABC present
03.04.14 15 ABC present
04.04.14 1 ABC present
04.04.14 2 BC present
04.04.14 3 DFG present
04.04.14 4 DBC present
04.04.14 5 AC present
04.04.14 6 ABC present
04.04.14 7 ABC present
04.04.14 8 ABC present
04.04.14 9 ABC present
04.04.14 10 ABC present
04.04.14 11 ABC present
04.04.14 12 ABC present
04.04.14 13 ABC present
04.04.14 14 ABC present
04.04.14 15 ABC present
60 rows selected.
SQL> |
|
|
|
| Re: show all values instead of null or as field type. [message #634040 is a reply to message #634033] |
Tue, 03 March 2015 06:13 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Select (select trunc(:ym, 'MM') + level - 1 from dual
connect by level <= to_char(last-day(:ym),'DD') datum,
Student.stuid,
student.name,
min(case when a.astuid = s.stuid and
a.abdate = datum and
a.abtype = 'ABSENT'
then 'A'
else 'P'
end
) abtype
from student s,
absent1 a
where a.astuid (+) = s.stuid
group by datum, s.stuid, s.name
order by datum, s.stuid
Please view the above query. It gives me error.i am using this query in reports 6i. (with data as not working)
[Updated on: Tue, 03 March 2015 06:14] Report message to a moderator |
|
|
|
| Re: show all values instead of null or as field type. [message #634041 is a reply to message #634040] |
Tue, 03 March 2015 06:30 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is a mess. You shouldn't post code that contains syntax errors. What is "last-day" in line 2?
If WITH factoring clause doesn't work, simply move DATES into a FROM clause (i.e. make it an inline view):select
d.datum,
s.stuid,
s.name,
-- MIN(CASE ...) because - if someone is ABSENT, you want to select that
-- value, not PRESENT ('present' > 'absent')
min(case when a.astuid = s.stuid and
a.abdate = d.datum and
a.abtype = 'ABSENT'
then 'absent'
else 'present'
end
) abtype
from student s,
-- Row generator technique; I'm creating 4 dates in range between
-- 01.04.2014 - 04.04.2014 (DD.MM.YYYY)
(select trunc(date '2014-04-03', 'mm') + level - 1 datum
from dual
connect by level <= 4
) d,
absent1 a
where a.astuid (+) = s.stuid
group by d.datum, s.stuid, s.name
order by d.datum, s.stuid; |
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #634228 is a reply to message #634179] |
Fri, 06 March 2015 00:45 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
SQL> ed
Wrote file afiedt.buf
1 WITH T AS
2 (
3 select to_date('JUN-2010','MON-YYYY')+LEVEL-1 DT FROM DUAL
4 CONNECT BY LEVEL < ADD_MONTHS(to_date('JUN-2010','MON-YYYY'),1) - to_date('JUN-2010','MON-Y
5 )
6 SELECT DT FROM T WHERE
7* TRIM(TO_CHAR(DT,'DAY')) = 'SUNDAY'
SQL> /
DT
---------
06-JUN-10
13-JUN-10
20-JUN-10
27-JUN-10
please advised how i can used the above query in (LF)main query.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #634392 is a reply to message #634375] |
Mon, 09 March 2015 07:15 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
i have the following result the above one is not correct.please view the below code.
SQL> ed
Wrote file afiedt.buf
1 select count('P'), COUNT('A')
2 from absent1 a, student s
3 where a.astuid = s.stuid
4 and s.status = 'PRESENT'
5* and a.abdate in ('02-OCT-2014')
SQL> /
COUNT('P') COUNT('A')
---------- ----------
149 149
===========================================================i want to correct calculate the no of [present subtract from no of absent students.
02.04.14 1 ABC absent
02.04.14 2 BC present
02.04.14 3 DFG absent
02.04.14 4 DBC present
02.04.14 5 AC absent
02.04.14 6 ABC absent
02.04.14 7 ABC present
02.04.14 8 ABC present
02.04.14 9 ABC present
02.04.14 10 ABC present
02.04.14 11 ABC present
02.04.14 12 ABC present
02.04.14 13 ABC present
02.04.14 14 ABC present
[b]Count('P')= 10 Count('A')= 4[/b] this is correct result.
|
|
|
|
| Re: show all values instead of null or as field type. [message #634398 is a reply to message #634392] |
Mon, 09 March 2015 08:10 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It seems that you didn't understand what is wrong here. You aren't calculating Ps or As in COUNT('P') - it returns total number of rows returned by that query. That's why you got 149 in both cases (and COUNT('Angelina Jolie') would return 149 as well).
You have to use a different approach. Here's what you are currently doing:
SQL> with test as (select 1 stuid, 'A' status from dual union
2 select 2 , 'A' from dual union
3 select 3 , 'P' from dual union
4 select 4 , 'A' from dual
5 )
6 select count('P'), count('A')
7 from test;
COUNT('P') COUNT('A')
---------- ----------
4 4
Here's what you could try to do:
SQL> with test as (select 1 stuid, 'A' status from dual union
2 select 2 , 'A' from dual union
3 select 3 , 'P' from dual union
4 select 4 , 'A' from dual
5 )
6 select sum(decode(status, 'P', 1, 0)) present,
7 sum(decode(status, 'A', 1, 0)) absent
8 from test;
PRESENT ABSENT
---------- ----------
1 3 |
|
|
|
| Re: show all values instead of null or as field type. [message #634416 is a reply to message #634398] |
Tue, 10 March 2015 00:35 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
In absent1 table i only store (absent) values. i want to calculate the count(absent) in a month. the count(Present) will be calculate from total no of days in month-count(absent).
function CF_1Formula return Number is
a number;
begin
SELECT TO_CHAR(LAST_DAY(:YM),'DD')-COUNT(ASTUID)-:B INTO A
FROM ABSENT1 A,STUDENT S
WHERE S.CLASS=:CLS AND S.SECTION=:SEC AND STATUS='PRESENT'
AND ASTUID=:STUID1
AND ABDATE BETWEEN TRUNC(:YM,'MONTH') AND LAST_DAY(:YM);
RETURN(A);
END;
ON REPORTS IT SHOWS: Please view the attached file.
but on sql it show this accurate result:
SQL> /
ASTUID ASD
---------- ----------
1 24
2 25
3 22
4 24
5 24
6 23
7 22
8 25
9 22
10 24
please advised.
-
Attachment: 44.JPG
(Size: 114.54KB, Downloaded 1718 times)
[Updated on: Tue, 10 March 2015 06:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #634564 is a reply to message #634523] |
Wed, 11 March 2015 06:31 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Quote:and to_char(trunc(:YM, 'mm') + level - 1, 'd') <> 7;
In December, 2014 sundays fall on (7,14,21,28). But this statement count sundays on (6,13,20,27). For example: if we make attendance till today it shows me the next dates also which is not fall/occur.
[Updated on: Wed, 11 March 2015 06:35] Report message to a moderator |
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #634613 is a reply to message #634567] |
Thu, 12 March 2015 02:12 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
SQL> WITH datumi
2 AS ( SELECT TRUNC (SYSDATE, 'w') + LEVEL datum
3 FROM DUAL
4 CONNECT BY LEVEL <= 7)
5 SELECT datum,
6 TO_CHAR (datum, 'day', 'nls_date_language = english') day_name,
7 TO_CHAR (datum, 'd') day_num
8 FROM datumi
9 ORDER BY datum;
DATUM DAY_NAME D
--------- --------- -
09-MAR-15 monday 2
10-MAR-15 tuesday 3
11-MAR-15 wednesday 4
12-MAR-15 thursday 5
13-MAR-15 friday 6
14-MAR-15 saturday 7
15-MAR-15 sunday 1
7 rows selected.
|
|
|
|
| Re: show all values instead of null or as field type. [message #634615 is a reply to message #634613] |
Thu, 12 March 2015 03:00 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
OK, so you want to exclude Sundays. In message #634229 I said:LF
Over here, Sunday is the 7th day in a week.
...
where to_char(trunc(date '2014-04-03', 'mm') + level - 1, 'd') <> 7 --> this line
Number 7 (at the end of the WHERE line) represents my Sunday (which is day number 7 here).
Now, it is your turn to think it over and fix the problem.
[Updated on: Thu, 12 March 2015 03:01] Report message to a moderator |
|
|
|
|
|
| Re: show all values instead of null or as field type. [message #635279 is a reply to message #634619] |
Thu, 26 March 2015 02:40 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
How i can Exclude the Gazetted holiday from this query like (4-May, 31-Apr).
select distinct
d.datum,
s.stuid,
s.rollno,
' '||s.name,
min(case when a.astuid = s.stuid and
a.abdate = d.datum and
a.abtype = 'ABSENT'
then 'A'
else 'P'
end
) abtype, count(astuid) aa
from student s,
(select distinct trunc(:ym, 'MM') + level - 1 datum from dual
where
-- to_char(abdate,'MONTH') in to_char(:ym,'MONTH')),
to_char(trunc(:ym, 'MM') + level-1 , 'd') <> 1
connect by level <= to_char(last_day(:ym),'DD')) d ,
absent1 a
where a.astuid = s.stuid(+)
and s.status='PRESENT'
and class=:cls
and section=:sec
and ABDATE BETWEEN TRUNC(:YM,'MONTH') AND LAST_DAY(:YM)
group by d.datum, s.stuid, s.name, s.rollno
order by d.datum, s.stuid
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 16:47:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
