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
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.
The first solution works perfect.
Is it possible to see zero values in the crosstab instead of empty fields?
Thanx
Check the format data option. You can set missing or zero values to a certain value (like missing to zero)