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

2006-10-30 08:14:04 457 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

zdm1999 墨衍会员 · AI 创作全网分发关注 + 加入社区

Windows打印体系结构之Windows内置打印驱动程序时间是一种很残酷的东西，它只会冲淡能够冲淡的，但也会洗尽铅华帮你留下该留下的。1.3.4、Windows内置打印驱动程序现在的Windows下的打印机驱动程序通常分为V4打印机驱动程序（V4 Printer Driver）、XPS打印机驱动程序（XPSDrv）、微软通用打印机驱动程序（Microsoft UniversalPrinter Driver）、微软PostScri... 阅读详情

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了，没有正常返回

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

...全文

JavaScript China城市联动中国全城市的地址省,市,县 <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title></title> <script src="jquery.js"></script> <script src="json.js"></... 阅读详情

zdm1999

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

21ithorse 2006-12-09

打赏
举报

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

dongdong715 2006-11-07

打赏
举报

yun 晕

jdsnhan 2006-11-04

打赏
举报

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

rolandzhang 2006-11-04

打赏
举报

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

多壮志 2006-11-02

打赏
举报

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

LinZhongBao 2006-11-02

打赏
举报

看的真有点晕阿！

dreamreality 2006-11-01

打赏
举报

看着真晕

zdm1999 2006-10-30

打赏
举报

=========================================================================
函数 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;

zdm1999 2006-10-30

打赏
举报

===============================================================================
函数 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;

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

相关推荐