Link to home
Create AccountLog in
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad ImranFlag for United Kingdom of Great Britain and Northern Ireland

asked on

creating view based on query

study these results please

select * from absent where sid = 7;

       AID        SID ABSDATE   RE
---------- ---------- --------- --
        64          7 06-DEC-10 A
       216          7 10-DEC-10 A

select * from absent where sid = 112;

       AID        SID ABSDATE   RE
---------- ---------- --------- --
        89        112 06-DEC-10 A
       134        112 07-DEC-10 A
       719        112 24-DEC-10 A

 select rownum,to_char(absdate,'dd/mm/yyyy fmDay') absdate,
 case when days_ago = 0 then 'Today'
      when days_ago = 1 then 'Yesterday'
      else days_ago ||' days ago' end total_days_ago,
      nvl(to_char((lag(absdate, 1) over (order by absdate desc)  - absdate)-1),'Most recent') as days_gaps
 FROM  (SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
        FROM ABSENT
        WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
                          AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
        AND SID in ( 7)
        ORDER BY ABSDATE DESC)
 /

ROWNUM ABSDATE              TOTAL_DAYS_AGO  DAYS_GAPS
------ -------------------- --------------- ---------------
     1 10/12/2010 Friday    17 days ago     Most recent
     2 06/12/2010 Monday    21 days ago     3

 select rownum,to_char(absdate,'dd/mm/yyyy fmDay') absdate,
 case when days_ago = 0 then 'Today'
      when days_ago = 1 then 'Yesterday'
      else days_ago ||' days ago' end total_days_ago,
      nvl(to_char((lag(absdate, 1) over (order by absdate desc)  - absdate)-1),'Most recent') as days_gaps
 FROM  (SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
        FROM ABSENT
        WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
                          AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
        AND SID in (112)
         ORDER BY ABSDATE DESC)
 /

ROWNUM ABSDATE              TOTAL_DAYS_AGO  DAYS_GAPS
------ -------------------- --------------- ---------------
     1 24/12/2010 Friday    3 days ago      Most recent
     2 07/12/2010 Tuesday   20 days ago     16
     3 06/12/2010 Monday    21 days ago     0

how can i convert this query into a view so that instead i can run

select * from myview where sid = 7
select * from myview where sid = 112

Avatar of ajexpert
ajexpert
Flag of United States of America image

I am not 100% sure but I believe both the queries are same except for SID's

Use this to create your view


CREATE OR REPLACE myview
AS
select rownum,to_char(absdate,'dd/mm/yyyy fmDay') absdate,
 case when days_ago = 0 then 'Today'
      when days_ago = 1 then 'Yesterday'
      else days_ago ||' days ago' end total_days_ago,
      nvl(to_char((lag(absdate, 1) over (order by absdate desc)  - absdate)-1),'Most recent') as days_gaps
 FROM  (SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
        FROM ABSENT
        WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
                          AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
         ORDER BY ABSDATE DESC)
;


--- test it by 
select * from myview where sid = 7
select * from myview where sid = 112

Open in new window

sorry for typo it should be


CREATE OR REPLACE VIEW myview
AS
select rownum,to_char(absdate,'dd/mm/yyyy fmDay') absdate,
 case when days_ago = 0 then 'Today'
      when days_ago = 1 then 'Yesterday'
      else days_ago ||' days ago' end total_days_ago,
      nvl(to_char((lag(absdate, 1) over (order by absdate desc)  - absdate)-1),'Most recent') as days_gaps
 FROM  (SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
        FROM ABSENT
        WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
                          AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
         ORDER BY ABSDATE DESC)
;

Open in new window

Avatar of Muhammad Ahmad Imran

ASKER

not a chocolate as I was thinking as well

I should have mention this in question.
I tried that, look at the results

  1  CREATE OR REPLACE view myview AS
  2  select rownum cnt,sid,to_char(absdate,'dd/mm/yyyy fmDay') absdate,
  3   case when days_ago = 0 then 'Today'
  4        when days_ago = 1 then 'Yesterday'
  5        else days_ago ||' days ago' end total_days_ago,
  6        nvl(to_char((lag(absdate, 1) over (order by absdate desc)  - absdate)-1),'Most recent') as days_gaps
  7   FROM  (SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
  8          FROM ABSENT
  9          WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
 10                            AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
 11*          ORDER BY ABSDATE DESC)
SQL> /

View created.

SQL> select * from myview where sid = 7
  2  
SQL> /

       CNT        SID ABSDATE              TOTAL_DAYS_AGO  DAYS_GAPS
---------- ---------- -------------------- --------------- ---------------
       527          7 10/12/2010 Friday    14 days ago     -1
       714          7 06/12/2010 Monday    18 days ago     -1

SQL>

You see DAYS_GAPS messed up
that's what i am trying to get rid of
assume today is 27-dec-2010
SQL> /

       CNT        SID ABSDATE              TOTAL_DAYS_AGO  DAYS_GAPS
---------- ---------- -------------------- --------------- ---------------
       527          7 10/12/2010 Friday    17 days ago     -1
       714          7 06/12/2010 Monday    21 days ago     -1
How about if you make this as view

SELECT SID, ABSDATE, TO_CHAR (ABSDATE, 'Day') DAY, TRUNC (SYSDATE) - ABSDATE days_ago
        FROM ABSENT
        WHERE ABSDATE BETWEEN (SELECT STARTDATE FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
                          AND (SELECT ENDDATE   FROM TERM_DATES WHERE SYSDATE BETWEEN STARTDATE AND ENDDATE)
         ORDER BY ABSDATE DESC

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@awking00

that's what i was forgetting

thanks