OraFAQ Forum: Reports & Discoverer » student in specific year

Home » Developer & Programmer » Reports & Discoverer » student in specific year (orale 10g, Reports 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

student in specific year [message #683714]

Tue, 16 February 2021 01:13

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

Database: 10G
Reports Version: 6i

create table student (
stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);
insert into student1 (stuid,status,name,class,doa) 
values 
(1,'PRESENT','AHA','PREP','01-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(4,'PRESENT','A','ONE','01-FEB-2016');
insert into student1 (stuid,status,name,class,doa) 
values 
(3,'PRESENT','AQ','ONE','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(2,'PRESENT','AER','PREP','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(29,'PRESENT','AERRRRRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(28,'PRESENT','AQRRRRH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(27,'PRESENT','AARAG','P.G','09-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(26,'PRESENT','AEARRTAA','P.G','11-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(30,'PRESENT','AER44RRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(31,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(32,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(33,'N_PRESENT','AEAAA','P.G','16-FEB-2016','19-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAVE_DATE)
values
(40,'N_PRESENT','AEARRTAA','P.G','11-FEB-2016','20-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(36,'N_PRESENT','AER44RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(37,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(38,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(39,'PRESENT','AEAAA','P.G','16-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(41,'PRESENT','AERRERRRRJ','P.G','20-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(42,'PRESENT','AQRWERWERRRRH','P.G','20-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(43,'PRESENT','AARAEEWG','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(44,'PRESENT','AEAWERRTAA','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(45,'PRESENT','AEREWEW44RRJ','P.G','13-MAR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(46,'PRESENT','AQEWTYTRYR3RH','P.G','01-APR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(47,'PRESENT','AATYRTYAG','P.G','19-FEB-2017');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(48,'N_PRESENT','AERWERAAA','P.G','16-FEB-2017','10-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(49,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(17,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(19,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(16,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
COMMIT;

I want to count the "PRESENT" student in specific year like how many "PRESENT" students in 2016 and 2017.

Report message to a moderator

Re: student in specific year [message #683716 is a reply to message #683714]

Tue, 16 February 2021 01:35

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post a VALID test case.

Quote:

create table student
insert into student1

In addition, '23-JUN-2016' is a string NOT a date:

SQL> select to_date('23-JUN-2016') from dual;
select to_date('23-JUN-2016') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

ALWAYS use TO_DATE and specify the format you use.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.

In the end, you just have to count grouping by year:

SQL> select extract(year from hiredate) year, count(decode(deptno,10,empno)) nb_in_dept10
  2  from emp
  3  group by extract(year from hiredate)
  4  /
      YEAR NB_IN_DEPT10
---------- ------------
      1982            1
      1987            0
      1980            0
      1981            2

SQL> select extract(year from hiredate) year, count(*)  nb_in_dept10
  2  from emp
  3  where deptno = 10
  4  group by extract(year from hiredate)
  5  /
      YEAR NB_IN_DEPT10
---------- ------------
      1982            1
      1981            2

depending if you want all years in the table or only those with some rows.

[Updated on: Tue, 16 February 2021 06:53]

Report message to a moderator

Re: student in specific year [message #683733 is a reply to message #683716]

Wed, 17 February 2021 00:11

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

sorry for writing problem

create table student1 (
stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);
insert into student1 (stuid,status,name,class,doa) 
values 
(1,'PRESENT','AHA','PREP','01-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(4,'PRESENT','A','ONE','01-FEB-2016');
insert into student1 (stuid,status,name,class,doa) 
values 
(3,'PRESENT','AQ','ONE','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(2,'PRESENT','AER','PREP','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(29,'PRESENT','AERRRRRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(28,'PRESENT','AQRRRRH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(27,'PRESENT','AARAG','P.G','09-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(26,'PRESENT','AEARRTAA','P.G','11-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(30,'PRESENT','AER44RRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(31,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(32,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(33,'N_PRESENT','AEAAA','P.G','16-FEB-2016','19-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAVE_DATE)
values
(40,'N_PRESENT','AEARRTAA','P.G','11-FEB-2016','20-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(36,'N_PRESENT','AER44RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(37,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(38,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(39,'PRESENT','AEAAA','P.G','16-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(41,'PRESENT','AERRERRRRJ','P.G','20-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(42,'PRESENT','AQRWERWERRRRH','P.G','20-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(43,'PRESENT','AARAEEWG','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(44,'PRESENT','AEAWERRTAA','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(45,'PRESENT','AEREWEW44RRJ','P.G','13-MAR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(46,'PRESENT','AQEWTYTRYR3RH','P.G','01-APR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(47,'PRESENT','AATYRTYAG','P.G','19-FEB-2017');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(48,'N_PRESENT','AERWERAAA','P.G','16-FEB-2017','10-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(49,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(17,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(19,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(16,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
COMMIT;

Requried result:
Quote:

required result
no of PRESENT Student doa < '01-JAN-2017'
----------------------
34

no of PRESENT Student doa < '01-JAN-2015'
----------------------
24

Report message to a moderator

Re: student in specific year [message #683734 is a reply to message #683716]

Wed, 17 February 2021 00:28

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 16 February 2021 08:35

...
Quote:
create table student
insert into student1
In addition, '23-JUN-2016' is a string NOT a date:
SQL> select to_date('23-JUN-2016') from dual;
select to_date('23-JUN-2016') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ALWAYS use TO_DATE and specify the format you use.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.
...

SQL> create table student1 (
  2  stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);

Table created.

SQL> insert into student1 (stuid,status,name,class,doa)
  2  values
  3  (1,'PRESENT','AHA','PREP','01-JAN-2017');
(1,'PRESENT','AHA','PREP','01-JAN-2017')
                          *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: student in specific year [message #683752 is a reply to message #683734]

Wed, 17 February 2021 21:22

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

please find the attach file as test case.My NLS_Date format is : 'DD-MON-YY'

Attachment: New Text Document.txt
(Size: 3.32KB, Downloaded 2929 times)

[Updated on: Wed, 17 February 2021 21:23]

Report message to a moderator

Re: student in specific year [message #683753 is a reply to message #683752]

Thu, 18 February 2021 00:15

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I see your test case in your previous topic, no need to post it again as a file.
I don't care about your date format, I won't change my settings for you.
You MUST update your test case to make it work for ANYONE.
So I repeat one more time:

Michel Cadot wrote on Wed, 17 February 2021 07:28

Michel Cadot wrote on Tue, 16 February 2021 08:35

...
ALWAYS use TO_DATE and specify the format you use in this function.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.
...
...

This is basic SQL but as I am in a good mood I give the link you have to read:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm#i1003589

Report message to a moderator

Previous Topic:	Arabic Report In Adobe PDF printer
Next Topic:	how to create chart in oracle reports 6i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:10:01 CST 2025