not existing lead time values in a crosstabel

[johangel]

I made a query that calculate lead time of samples .
the result is given as  days ,whit a maximum of 28 days (when( lead time) > ('28') then ('28') else (lead time).
The problem is that when a lead time is not exists f.i. 2days,7days,23 days these lead time is not visible in the colums of my crosstab.
I want to see in this case 0 for 2days,0 for 7 days, 0 for 23 days.(i want to see the colums who's lead time is not exist)
For this purpose I also made a second sql query  [SELECT rownum - 1 dag  FROM DUAL CONNECT BY ROWNUM <= extract(DAY from LAST_DAY(sysdate))+1]. The result of this query is:1,2,3,4,5,6,7.....28 or 29 or 30 or 31.
I need a link between lead time from query 1 with days from the sql query, but how???
Tnx,Johan

[colt]

As I understand you: Your problem is that your query does not contain data for every lead time range you want to display in crosstab. I see 2 possible solutions for your problem:
1. Do a left join of your second query with your first query. (relation 1 <--> 0..n).
2. Using multiple dataitems like
    1day=CASE WHEN [lead time] = 1
                       THEN 1
                       ELSE 0
               END
    2days=CASE WHEN [lead time]=2
                       THEN 2
                       ELSE 0
              END
    28days=CASE WHEN [lead time] >= 28
                           THEN 28
                           ELSE 0
                  END
    If you use those dataitems as columns, then you'll always get 28 columns.

[johangel]

The first solution works perfect.
Is it possible to see zero values in the crosstab instead of empty fields?
Thanx

[blom0344]

Check the format data option. You can set missing or zero values to a certain value  (like missing to zero)