SQL for all months in range, data or not

Hi,

Need to get column headers in SQL for months regardless of whether data exists.

DB is public school data system for large city - 55 schools. Columns are Sep through Jun, taken from transaction date (using Oracle function) for a student. Rows are various categories. Body of table is student count for that school and category by month but there is not necessarily a student count for every category for every school every month. However, school board wants all tables to line up visually vertically, even if no data. Result table being used by another tool (Business Objects) for display.

Thought of using in line table, e.g.
SELECT
   data1,
   data2,
   etc.
FROM
   table1,
   table2,
   (Select 'Jan','Feb','Mar', etc from SYS.DUAL) etc. WHERE
   table1.school_year = 'YY', -- where YY is school year,

• e.g. Sep 2000 - Jun 2001, YY = '00' ???

What do I need here? I understand outer joins but how do I outer join an in line table?

Note that the selection for school year is NOT based on the transaction date. school_year is column in source table. All that is necessary is that the months taken from transaction date can be matched to the in line table, or whatever is used.

Any ideas? I am not SQL guru, so this may seem simple to some.

Thanks.

Tom Eischeid Received on Thu Jul 26 2001 - 01:54:59 CEST