SQL语句不能正常返回结果，急急,分不够再加

[zdm1999]

select office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,order_no,sale_amount_notax,
purchase_amount_notax,
income_amount_notax,
sum_request_amount_notax,sum_income_amount_notax ,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'1') one,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') two,
F_GetRemainAmount_ORDERNO('200609','200608','200608',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') three,
F_GetRemainAmount_ORDERNO('200609','200607','200607',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') four,
F_GetRemainAmount_ORDERNO('200609','200604','200606',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') five,
F_GetRemainAmount_ORDERNO('200609','200510','200603',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') six,
F_GetRemainAmount_ORDERNO('200609','200410','200509',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') seven,
F_GetRemainAmount_ORDERNO('200609','198008','200409',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') eight
from test





问题：
1、前100条记录 one、two、three、four、five、six、seven、eight 8列返回值都是正确的，
从100条记录后，返回的数据都为0了，没有正常返回


急，，，，，，，，，，分不够再加

[zdm1999]

===============================================================================
函数 F_GetRemainAmount_ORDERNO

CREATE OR REPLACE FUNCTION F_GetRemainAmount_ORDERNO(
v_date in varchar2,
v_start_date in varchar2,
v_end_date in varchar2,
v_order_no in varchar2,
v_sum_request_amount in number,
v_sum_income_amount in number,
v_office_name in varchar2,
v_sales_incharge_name in varchar2,
v_article_no in varchar2,
v_article_name in varchar2,
v_process_location in varchar2,
v_sale_yearmonth in varchar2,
v_customer_cd in varchar2,
v_customer_info in varchar2,
v_contract_section in varchar2,
v_customer_type in varchar2,
v_flag in varchar2)
RETURN number IS

--v_sum_request_amount number;
--v_sum_income_amount number;
v_curr_date_income number;
v_curr_date_request number;
v_tmp_amount number;

begin

/*
v_sum_request_amount := F_GetMonthAmount(v_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_REQUEST_AMOUNT');

v_sum_income_amount := F_GetMonthAmount(v_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_INCOME_AMOUNT');
*/
IF ROUND(v_sum_request_amount - v_sum_income_amount,2) = 0 THEN
RETURN 0;
END IF;

IF ROUND(v_sum_request_amount - v_sum_income_amount,2) > 0 THEN
if v_flag = '1' then
SELECT NVL(SUM(request_amount_notax),0)
INTO v_curr_date_request
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND pre_income_date > v_date AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no;

if v_curr_date_request <=0 then
return 0;
end if;

IF ROUND(v_curr_date_request - (v_sum_request_amount - v_sum_income_amount),2) < 0 THEN
return v_curr_date_request;
end if;

return ROUND(v_curr_date_request - (v_sum_request_amount - v_sum_income_amount),2);

end if;

v_curr_date_request := F_GetMonthAmount_ORDERNO(v_date,v_end_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_REQUEST_AMOUNT');
if ROUND(v_sum_income_amount - v_curr_date_request,2) >= 0 THEN
RETURN 0;
END IF;

v_tmp_amount := F_GetMonthAmount_ORDERNO(v_date,' ',v_start_date,v_end_date,v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'REQUEST_AMOUNT');
if ROUND(v_sum_income_amount - (v_curr_date_request - v_tmp_amount),2) <= 0 THEN
RETURN ROUND(v_tmp_amount,2);
END IF;

return ROUND(ABS(v_sum_income_amount - v_curr_date_request),2);
END IF;

IF ROUND(v_sum_request_amount - v_sum_income_amount,2) < 0 THEN
if v_flag = '1' then
return 0;
end if;

v_curr_date_income := F_GetMonthAmount_ORDERNO(v_date,v_end_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_INCOME_AMOUNT');
if ROUND(v_sum_request_amount - v_curr_date_income,2) >= 0 THEN
RETURN 0;
END IF;

v_tmp_amount := F_GetMonthAmount_ORDERNO(v_date,' ',v_start_date,v_end_date,v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'INCOME_AMOUNT');
if ROUND(v_sum_request_amount - (v_curr_date_income - v_tmp_amount),2) <= 0 THEN
RETURN ROUND(0 - v_tmp_amount,2);
END IF;

return ROUND(v_sum_request_amount - v_curr_date_income,2);

RETURN 1;
END IF;

EXCEPTION WHEN OTHERS THEN
return 0;


end;

[zdm1999]

=========================================================================
函数 F_GetMonthAmount_ORDERNO

CREATE OR REPLACE FUNCTION F_GetMonthAmount_ORDERNO(
v_date1 in varchar2,
v_date in varchar2,
v_start_date in varchar2,
v_end_date in varchar2,
v_order_no in varchar2,
v_office_name in varchar2,
v_sales_incharge_name in varchar2,
v_article_no in varchar2,
v_article_name in varchar2,
v_process_location in varchar2,
v_sale_yearmonth in varchar2,
v_customer_cd in varchar2,
v_customer_info in varchar2,
v_contract_section in varchar2,
v_customer_type in varchar2,
v_flag in varchar2)
RETURN number IS

v_sum_request_amount number;
v_sum_income_amount number;

begin

if v_flag = 'SUM_INCOME_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(income_amount_notax),0)
INTO v_sum_income_amount
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no;

return v_sum_income_amount;
END IF;

if v_flag = 'SUM_REQUEST_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(request_amount_notax),0)
INTO v_sum_request_amount
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND pre_income_date <= v_date1
AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no;

return v_sum_request_amount;
END IF;

if v_flag = 'INCOME_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(income_amount_notax),0)
INTO v_sum_income_amount
FROM v_month_article_customer_list
WHERE sale_date >= v_start_date and sale_date <= v_end_date AND
office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no;

return v_sum_income_amount;
END IF;

if v_flag = 'REQUEST_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(request_amount_notax),0)
INTO v_sum_request_amount
FROM v_month_article_customer_list
WHERE sale_date >= v_start_date and sale_date <= v_end_date AND
pre_income_date <= v_date1 and
office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no;

return v_sum_request_amount;
END IF;

EXCEPTION WHEN OTHERS THEN
return 0;

end;

[dreamreality]

看着真晕

[LinZhongBao]

看的真有点晕阿！

[多壮志]

这样的问题适合自己调试，用pl/sql developer.对适当的数据进行过滤一下即可得知。

[rolandzhang]

单独测试每个函数，会找到问题的

[jdsnhan]

没办法帮你调试，晕死了.
楼主这样，你不是说只能返回100条记录吗,你把前面等正确返回的记录删除一部分,再看返回结果.看看100是不是个瓶颈,这样，容易判断出数据问题还是函数问题。

[dongdong715]

yun 晕

[21ithorse]

看的真有晕，不知是否用来测试用，如果是开发用，那个这个SQL就有效率问题