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 Go to next message
shahzad-ul-hasan
Messages: 615
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 1131 times)
Re: show all values instead of null or as field type. [message #633983 is a reply to message #633981] Mon, 02 March 2015 05:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you querying a specific date or a range of dates?
Re: show all values instead of null or as field type. [message #633985 is a reply to message #633981] Mon, 02 March 2015 05:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try NVL function?

If you provide test case, I might change my mind, though.
Re: show all values instead of null or as field type. [message #633989 is a reply to message #633983] Mon, 02 March 2015 05:43 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Range of dates.
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 1065 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 #634028 is a reply to message #634020] Tue, 03 March 2015 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've told you what you need to do. Have you tried it?
Re: show all values instead of null or as field type. [message #634029 is a reply to message #634028] Tue, 03 March 2015 03:33 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Quote:
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.

Please elborate this.
Re: show all values instead of null or as field type. [message #634030 is a reply to message #634029] Tue, 03 March 2015 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which bit do you not understand?
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Why are there duplicates (marked with ???)? Is it the case in real world, or did you just not pay attention while composing INSERT statements?

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 as
to_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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #634179 is a reply to message #634041] Thu, 05 March 2015 05:06 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
thanks for your nice help.how i can subtract/exclude Sundays from this query;

[Updated on: Fri, 06 March 2015 00:34]

Report message to a moderator

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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 #634229 is a reply to message #634228] Fri, 06 March 2015 00:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Exclude Sundays? Over here, Sunday is the 7th day in a week. So, I'd modify it as follows:
...
     (select trunc(date '2014-04-03', 'mm') + level - 1 datum
      from dual
      where to_char(trunc(date '2014-04-03', 'mm') + level - 1, 'd') <> 7      --> this line
      connect by level <= 4
     ) d,
...
Re: show all values instead of null or as field type. [message #634230 is a reply to message #634229] Fri, 06 March 2015 01:27 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
thanks for your help.
Re: show all values instead of null or as field type. [message #634361 is a reply to message #634230] Mon, 09 March 2015 02:23 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
how i can find difference of count(Present) And Count(Absent)????from the above query.

[Updated on: Mon, 09 March 2015 02:25]

Report message to a moderator

Re: show all values instead of null or as field type. [message #634362 is a reply to message #634361] Mon, 09 March 2015 02:28 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Subtract them?
Re: show all values instead of null or as field type. [message #634366 is a reply to message #634362] Mon, 09 March 2015 02:34 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
function CF_1Formula return Number is
a number;
b number;
begin
 select count('P')-COUNT('A') into a from absent1 a, student
 where a.astuid=s.stuid
 and s.status='PRESENT'
 and a.abdate in (:ym);
 return(a);
 end;

PLEASE ADVISED. please view the attached file.
  • Attachment: 45.JPG
    (Size: 60.56KB, Downloaded 1157 times)

[Updated on: Mon, 09 March 2015 02:37]

Report message to a moderator

Re: show all values instead of null or as field type. [message #634368 is a reply to message #634366] Mon, 09 March 2015 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to
select count('P') from ...

What did you get?

What do you get when you
select count('A') from ...


What do you get when you
select count('Angeline Jolie') from ...
Re: show all values instead of null or as field type. [message #634370 is a reply to message #634368] Mon, 09 March 2015 02:41 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
It shows me 1 result.
For Count('A')
It shows me 1 result.

[Updated on: Mon, 09 March 2015 02:41]

Report message to a moderator

Re: show all values instead of null or as field type. [message #634375 is a reply to message #634370] Mon, 09 March 2015 02:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is "1 result"? Please, post your SQL*Plus session which executes
select count('P'), COUNT('A')
from absent1 a, student
where a.astuid = s.stuid
  and s.status = 'PRESENT'
  and a.abdate in (&ym);
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 1183 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 #634510 is a reply to message #634416] Wed, 11 March 2015 01:38 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
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 ABDATE BETWEEN  TRUNC(:YM,'MONTH') AND LAST_DAY(:YM)
 and S.CLASS=:CLS AND S.SECTION=:SEC AND STATUS='PRESENT'
 AND ASTUID(+)=:STUID1
 and  to_char(trunc(:YM, 'mm') + level - 1, 'd') <> 7;
 return(a);
end;

plese view th attached file.
  • Attachment: 123.JPG
    (Size: 86.89KB, Downloaded 1186 times)
Re: show all values instead of null or as field type. [message #634512 is a reply to message #634510] Wed, 11 March 2015 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to split it to two steps: first calculate how many "presents" there are. Then subtract that number from number of days in a month.
Re: show all values instead of null or as field type. [message #634523 is a reply to message #634512] Wed, 11 March 2015 03:09 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
i will do and tell you shortly.
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 #634567 is a reply to message #634564] Wed, 11 March 2015 06:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Run the following query in SQL*Plus; paste result over here.
WITH datumi
     AS (    SELECT TRUNC (SYSDATE, 'w') + LEVEL datum
               FROM DUAL
         CONNECT BY LEVEL <= 7)
  SELECT datum, 
         TO_CHAR (datum, 'day', 'nls_date_language = english') day_name, 
         TO_CHAR (datum, 'd') day_num
    FROM datumi
ORDER BY datum;
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #634619 is a reply to message #634615] Thu, 12 March 2015 03:38 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Quote:

where to_char(trunc(date '2014-04-03', 'mm') + level - 1, 'd') <> 7 --> this line

replace this with:
   where to_char(trunc(date '2014-04-03', 'mm') + level - 1, 'd') <> 1      --> this line
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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
Re: show all values instead of null or as field type. [message #635280 is a reply to message #635279] Thu, 26 March 2015 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How do you exclude anything from a query?
Re: show all values instead of null or as field type. [message #635281 is a reply to message #635280] Thu, 26 March 2015 02:50 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
Quote:

How do you exclude anything from a query?

i wan to exclude the (independence Day, St Patrick day) from the main query like Sundays as i exclude from the above query.
Re: show all values instead of null or as field type. [message #635284 is a reply to message #635281] Thu, 26 March 2015 03:14 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so do it. What's the problem?

Again: how do you exclude anything from any query? What mechanism do you use?
Previous Topic: Oracle Frames and Repeating Frames
Next Topic: returning value date from formula
Goto Forum:
  


Current Time: Thu Mar 28 15:19:23 CDT 2024