Q

Using a derived table (query for data and year tables)

I have two tables called 'data' and 'year'. The table 'data' has two columns namely 'year' and 'value'. And table 'year' contains only one column 'year'. Table 'year' contains six rows always - they are current year, current year + 1, ... current year + 5. For example, if the current year is 2002, the table 'year' will have six rows as: 2002 2003 2004 2005 2006 2007 Table 'data' may or may not have all these years. In case one of these...

years is missing, I would like to see a row with a value 0 for that row. So I wrote a outer join query as follows:

SELECT
        nvl(sum(value),0) value,
        YEAR.year year
FROM
        DATA,YEAR
WHERE
        DATA.year(+)= YEAR.year
GROUP BY
        YEAR.year
I want to know, if there is any other way by which I can write a query to produce the same result, but can avoid using 'year' table. Since I will always know the six years for which I want data from 'data' table. If one of them is missing, I want that year with a value 0 in the result.


You can write the query like this:

SELECT  nvl(sum(data.value),0) value,
        Y.year year
FROM
        DATA ,(select 2002 as year from dual
              union all
              select 2003 as year from dual
              union all
              select 2004 as year from dual
              union all
              select 2005 as year from dual
              union all
              select 2006 as year from dual
              union all
              select 2007 as year from dual)  y

WHERE
        DATA.year(+)= Y.year
GROUP BY
        Y.year
;
Usage of year table is eliminated if you know all values you can use a derived table that "builds" the in-line result set online and use it in the query.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in April 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close