Blank row problem

[21128]

this is my query

SELECT max(rev_no), max(tour), max(DT), max(SHIFT_NO), max(DECODE(STA,1,RD,NULL)) "MW PR",
max(DECODE(STA,2,RD,NULL)) "MW",
max(DECODE(STA,3,RD,NULL)) "MW D",
max(DECODE(STA,10,RD,NULL)) "P2300-4 PR",
max(DECODE(STA,11,RD,NULL)) "P2300-4",
max(DECODE(STA,12,RD,NULL)) "P2300-4 D"
FROM history WHERE tour=18 AND rev_no=1 AND DT={?SelectDate} GROUP BY SHIFT_NO ORDER BY SHIFT_NO

On database shift_no has 24 values 1,2,3,4,5,6,7,8,9,10,....24
It gives me all the data i want but gives only the rows where there is value of RD in differnt STA. For example there are values in RD when shift_no is 1,2,3,4,5. it gives me only five rows. What if i want to display all 24 rows and display values in only shift_no 1,2,3,4,5 and rest of the shift_no blank. how can i do that?

Thanks

 

[CoSpringsGuy]

Im not an SQL expert but is it possible that your WHERE clause is the problem? In other words.... Could there be only five shift_no's that tour=18 AND rev_no=1 AND DT={?SelectDate} are true?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[21128]

Im not an SQL expert but is it possible that your WHERE clause is the problem? In other words.... Could there be only five shift_no's that tour=18 AND rev_no=1 AND DT={?SelectDate} are true?

the value of shift_no is 24 ranging from 1 to 24.
for a selected date it might be 5 shift_no or 10 shift_no or 24 shift_no . so even if there is no entry of RD value in database for other shift_no, i want to display all 24 shift_no in my output. It must be like this

Selected Date 8/20/2009

shift MW MWD MV MVD
1
2
3
4
5
6
7
8
9
10
11 769 390
12 780 11 385 5
13 785 5 395 10
14
.
.
.
24
-----------------------------------
16 15