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

LVL 14
Muhammad Ahmad ImranDatabase DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajexpertCommented:
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

ajexpertCommented:
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

Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
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
ajexpertCommented:
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

awking00Information Technology SpecialistCommented:
I think you need a partition by sid clause for you lag statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Muhammad Ahmad ImranDatabase DeveloperAuthor Commented:
@awking00

that's what i was forgetting

thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.