Put group header even though no group info

[peterswan]

select
adm_lu_code,
adm_lu_description,
upper(substr(adm_lu_code, 1, 1)) as first_letter
From
csq_admin_lookup
where
adm_lu_system = 'CRM'
and adm_lu_type = 'GLOSSARY'
order by
adm_lu_code

Is there any way I can get the "first_letter" field to appear in the query result set, with nulls as the other two values, even if there are items in the data base that don't start with this letter? I want the output to look like this:

Code Description first_letter
null null A
null null B
DE Germany D
FR France F
USA United States U

Of course there would be other values in the database. What I want to ensure is that the null values show up in the resultset if there is no such code that starts with this letter. This way I will be able to put the letter as a header on the report with nothing beneath it, denoting this group as having no values.

I know I can do this with another table named "Alphabet", and do a left join on this table, however, I'd like to do this another way, without building a new table.

Thanks,

Peter

 

[mrdenny]

You'll need the second table and the left outer join.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com