-1

This is an extract sql that gets days

AND S.Date          IN

  (
    SELECT Date
    FROM
      (
      SELECT Date,
        ROW_NUMBER() OVER (ORDER BY Date DESC )-1 Day
      FROM CALENDAR_DIM
      WHERE TYPE               = 'ABC'
      )
    WHERE BUS_DAY BETWEEN 0 AND 2
  )

I want to run this code twice in two parts of my sql. How can i do that without pasting the same code. Also .. how could i rewrite the above code?. I am having some issues with performance.

user648244
  • 1,240
  • 7
  • 26
  • 41
  • think you have a typo : shouldn't it be `ROW_NUMBER() OVER (ORDER BY Date DESC )-1 BUS_DAY`, or I don't know where your BUS_DAY comes from... – Raphaël Althaus Nov 01 '12 at 09:50

2 Answers2

3

Solution 1 : use a WITH statement

WITH dateQuery  AS (
    SELECT Date
    FROM
      (
      SELECT Date,
        ROW_NUMBER() OVER (ORDER BY Date DESC )-1 BUS_DAY
      FROM CALENDAR_DIM
      WHERE TYPE = 'ABC'
      )
    WHERE BUS_DAY BETWEEN 0 AND 2)



SELECT xxx
FROM yyy
WHERE zzz
AND s.Date IN (SELECT Date FROM dateQuery)

The only part to repeat will then be

SELECT Date FROM dateQuery

Solution 2 : create a View

CREATE OR REPLACE VIEW V_DATE_QUERY AS
(   SELECT Date
    FROM
      (
      SELECT Date,
        ROW_NUMBER() OVER (ORDER BY Date DESC )-1 BUS_DAY
      FROM CALENDAR_DIM
      WHERE TYPE = 'ABC'
      )
    WHERE BUS_DAY BETWEEN 0 AND 2)

and use it the same way

AND s.Date IN (Select Date FROM V_DATE_QUERY);
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
  • @user648244 because it's a "materialized" subquery (Oracle transforms it as an inline view or as a temporary table.), and not just a subquery. http://www.dba-oracle.com/t_with_clause.htm – Raphaël Althaus Nov 01 '12 at 10:00
1

You would make this a view in your database like so:

CREATE VIEW view_date
AS
SELECT Date,
  ROW_NUMBER() OVER (ORDER BY Date DESC )-1 Day
FROM CALENDAR_DIM
WHERE TYPE               = 'ABC'

Now you can use:

AND S.Date          IN

  (
    SELECT Date
    FROM view_date
    WHERE BUS_DAY BETWEEN 0 AND 2
  )
Community
  • 1
  • 1
Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139