PLSQL 条件
AND (M.CONTRACT_ID = :P_CONTRACT OR :P_CONTRACT IS NULL)
AND (M.REQ_NO = :P_REQ_NO OR :P_REQ_NO IS NULL)
AND (TO_CHAR(C.IN_DATE, 'YYYY/MM/DD') >= :P_FROM_DATE OR
:P_FROM_DATE IS NULL)
AND (TO_CHAR(C.IN_DATE, 'YYYY/MM/DD') <= :P_TO_DATE OR
:P_TO_DATE IS NULL)
请转换为SQL
查询出来的结果不变！
------解决思路----------------------
按我的理解应该是这样：
CREATE PROCEDURE ni_contract_in_query
@contract_id VARCHAR(50) ,
@req_no VARCHAR(50) ,
@from_date VARCHAR(20) ,
@end_date VARCHAR(20)
AS
BEGIN
CREATE TABLE #contract_in_query
(
in_date DATETIME ,
item_no VARCHAR(50) ,
xh_no INT ,
ciq_no VARCHAR(20) ,
item_name NVARCHAR(200) ,
gross_weight DECIMAL(18, 4) ,
in_qty DECIMAL(18, 2) ,
in_qty_main DECIMAL(18, 4) ,
in_qty_child DECIMAL(18, 4) ,
main_money DECIMAL(18, 4) ,
child_money DECIMAL(18, 4) ,
in_price DECIMAL(18, 4) ,
type NVARCHAR(15) ,
contract_id VARCHAR(50) ,
bak NVARCHAR(500)
)
INSERT INTO #contract_in_query
SELECT c.in_date ,
c.item_no ,
m.xh_no seq ,
c.ciq_no ,
i.item_name ,
c.gross_weight ,
c.in_qty ,
ISNULL(( CASE WHEN i.type1 = 1 THEN c.in_qty
ELSE 0
END ), 0) in_qty_main ,
ISNULL(( CASE WHEN i.type1 = 2 THEN c.in_qty
ELSE 0
END ), 0) in_qty_child ,
ISNULL(( CASE WHEN i.type1 = 1 THEN c.in_qty