问题有了些许转机,变通的办法解决了这个问题,但是这个语句为何出错仍然不得而知,路过的大侠们给看看吧。
WITH t0 AS(
SELECT h.hotel_id,
h.hotel_name,
h.address,
h.hotel_grade_type_id,
hg.hotel_grade_pic,
hl.reco_image,
f_ho_getcommtodonum(h.hotel_id,
to_date('2008-6-19', 'yyyy-mm-dd')) AS commtodonum,
h.reco_level_id,
h.hotel_status_type_id,
h.hotel_reserv_status_id,
f_gethouseconfig(h.hotel_id,
to_date('2008-6-19', 'yyyy-mm-dd')) AS houseconfig,
f_getbaseprice(h.hotel_id, to_date('2008-6-19', 'yyyy-mm-dd')) AS baseprice,
f_gethotelmark(h.hotel_id, -1) AS hotelmark,
nvl(hrs.num_buy, 0) num_buy,
nvl(hrs.num_pre_occupt, 0) num_pre_occupt,
nvl(hrs.room_status, 2) AS room_status,
nvl(hrs.num_occupt, 0) num_occupt
FROM t_hp_hotel h
LEFT JOIN t_hd_hotel_status_type hst ON hst.hotel_status_type_id =
h.hotel_status_type_id
LEFT JOIN t_hd_hotel_grade_type hg ON hg.hotel_grade_type_id =
h.hotel_grade_type_id
LEFT JOIN t_hd_reco_level hl ON hl.reco_level_id = h.reco_level_id
LEFT JOIN t_sd_city city ON h.city_id = city.city_id
LEFT JOIN t_hp_room_status_stat hrs ON hrs.hotel_id = h.hotel_id
AND hrs.room_date =
to_date('2008-6-19',
'yyyy-mm-dd')
WHERE f_getbaseprice(h.hotel_id, to_date('2008-6-19', 'yyyy-mm-dd')) BETWEEN 1000 AND 1200
AND hg.hotel_grade_num = 5
AND (h.county_id = 110000 AND
(city.city_type IS NULL OR city.city_type = 1))
ORDER BY commtodonum,
CASE
WHEN hrs.num_buy > 0 THEN
1
ELSE
0
END DESC,
CASE
WHEN hrs.num_occupt > 0 THEN
1
ELSE
0
END DESC,
CASE
WHEN hrs.num_pre_occupt > 0 THEN
1
ELSE
0
END DESC,
hl.reco_sort,
hst.sort DESC,
h.person_reservs DESC,
h.person_querys DESC)
SELECT t0.*, rownum FROM t0;