sum of a column 1

[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 RD IS NOT NULL AND DT={?SelectDate} GROUP BY SHIFT_NO ORDER BY SHIFT_NO

now i want to total of the column "MW D" the datatype of RD is varchar2 but there is number in it as its value. how can i add that column?

Thanks

 

[lbass]

You should be able to create a formula:

tonumber({command.MW D})

Place this in the detail section and right click->insert a sum on it.

-LB

 

[21128]

Thank you very much....

I have a next quick question

shift_no has 24 values 1,2,3,4,5,6,7,8,.......24
when i run that in my report it gives the row where there is value of RD. How can i print all shift_no in the report even if there is no value for RD?

Thank You

 

[lbass]

You have to remove this clause from your where clause:

AND RD IS NOT NULL

-LB

 

[21128]

It doesnt even work when i take that clause
AND RD IS NOT NULL

 

[lbass]

Sorry. Is this a command that is your only datasource for the report? If so, maybe you could add a Union All which adds all shift numbers--but then I don't think you can use the Group by and Order by clauses in your initial query. I think you might have to set up the maximums as subqueries within the select. Not sure.

-LB

 

[21128]

i m so lost.......

 

[lbass]

And now you have two threads going on this...

If you don't want to try the union, you could add the table that contains all shift no's and use a left join FROM that table TO the command. Then you could insert a group on the {table.shiftno} and they should all show up.

-LB