how do I account for null values 1

[Lhuffst]

In XI, I don't even know where to begin. I hate to be a pest but people keep changing the requirements. I have created both a cross table and manual cross table successfully (again thanks to this site). On the test tables that I was given to work with, we backloaded test data so every employee had at a record for each month so I could format the output correctly. So far good.

On the production tables this is not the case and the user wants every employee as well as the related columns to be printed for each month between the given dates.

When I run this against production (using the dates january 09 to may 09), the output skips january since there isnt any data for that month (in the columns) and in February it lists the 2 employees that have data and skips the others.

Does anyone know how I would print all 7 employees for each month and then all columns with a zero if they didn't have any counts for that column?

Thanks....I am totally stressed at this point. Not sure if this is helpful but here is the code that I use to retrieve the data from oracle.

SELECT 
a.USERID, 
a.LASTNAME, 
a.FIRSTNAME, 
b.PROCESSDATE, 
c.REFID,
c.DESC_COUNT, 
d.DESCRIPTION, 
d.shorttitle,
d.SECTION, 
d.PHASE, 
d.ADMIN
from  TrackREF d ,TRACKDETAIL c, TRACKMASTER b, USERMASTER a
where  d.REFID =  c.REFID 
and  c.TRACKID =  b.TRACKID
and  b.USERID =  a.USERID(+)
ORDER BY  a.USERID,  a.LASTNAME,  b.PROCESSDATE

where usermaster has all of the employee names
Lhuffst

 

[IanWaterman]

Change your joins so that tables are joined with a left outer from your usermaster table.

This will bring in all your users.

With dates the manual cross tab will return zero for missing months columns.

Ian

 

[Lhuffst]

thanks