ORDER PROBLEM

[21128]

My query looks like this

(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,4,RD,NULL)) "MV PR",
max(DECODE(STA,5,RD,NULL)) "MV",
max(DECODE(STA,6,RD,NULL)) "MV D",
max(DECODE(STA,7,RD,NULL)) "P2300-3 PR",
max(DECODE(STA,8,RD,NULL)) "P2300-3",
max(DECODE(STA,9,RD,NULL)) "P2300-3 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 asc)
UNION
( select NULL, NULL, NULL, shift_no, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL from tour_approval where shift_no NOT IN(select distinct(shift_no) from history where DT={?SelectDate}))

My output gives LIKE

SHIFT_NO
1 THIS ROW HAS RD VALUES
2 THIS ROW HAS RD VALUES
3 THIS ROW HAS RD VALUES
0 THIS ROW DOESNT HAVE RD VALUES
4 THIS ROW DOESNT HAVE RD VALUES
5 THIS ROW DOESNT HAVE RD VALUES
6 THIS ROW DOESNT HAVE RD VALUES
7 THIS ROW DOESNT HAVE RD VALUES
8 THIS ROW DOESNT HAVE RD VALUES
9 THIS ROW DOESNT HAVE RD VALUES
. THIS ROW DOESNT HAVE RD VALUES
. THIS ROW DOESNT HAVE RD VALUES
. THIS ROW DOESNT HAVE RD VALUES
24 THIS ROW DOESNT HAVE RD VALUES

NOW HOW CAN I KEEP THE SHIFT_NO IN ASCENDING ORDER?

 

[lbass]

Try removing the Order By clause in the first part of the union and then adding a line at the very end:

Order by 4 //where the 4 represents the position of the field in the select clause

-LB

 

[21128]

(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,4,RD,NULL)) "MV PR",
max(DECODE(STA,5,RD,NULL)) "MV",
max(DECODE(STA,6,RD,NULL)) "MV D",
max(DECODE(STA,7,RD,NULL)) "P2300-3 PR",
max(DECODE(STA,8,RD,NULL)) "P2300-3",
max(DECODE(STA,9,RD,NULL)) "P2300-3 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)
UNION
( select NULL, NULL, NULL, shift_no, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL from tour_approval where shift_no NOT IN(select distinct(shift_no) from history where DT={?SelectDate}))
order by 4


it gives me an error when saying this field is not known tonumber({command.MW D})

 

[lbass]

Please identify the version of Crystal Reports you are using and explain where you creating this query.

-LB

 

[21128]

its crystal reports xi and oracle10g
i am using that query in add commmand

 

[lbass]

Okay, so when you go into the field explorer->formula->new, how is the "MW D" field listed in the field list? Just double click on the MW D field to put it into the tonumber() function. You must have renamed the command or something.

-LB

 

[21128]

i figured out that bracket in the first line was creating that problem.
it works now

thank you very much