高分求——存储过程中子查询错误定位

我在网页中查询，调用到此存储过程后就出错了，显示tomcat出错页面。myeclipse控制台显示：子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
但是我在查询分析器中手动调用该存储过程exec show_voyages_state_voyages '2004-02-01','2004-02-29','001','0'可以得到结果集。请问到底是怎么回事？如果是子查询错了那如何定位错误？
该存储过程如下CREATE procedure show_voyages_state_voyages
(
  @starttime char(10),
  @endtime char(10),
  @shipcode char(3),
  @voyageskind char(1)
)
as
begin
if @shipcode='000'
   set @shipcode = null
if @voyageskind='0'
   set @voyageskind = null
SELECT voyages_port_arrive_port_time AS ArriveTime, 
       voyages_port_off_berth_time AS LeaveTime,voyages_kind,
       voyages_port_type_code,a.ship_code, a.voyages_code, 
       ship_name, a.voyages_port_SN, voyages_start_time AS StartTime,
       AMile = ISNULL(b.Line_average_mileage,0) ,
       AMileLoad = ISNULL(b.Line_average_mileage,0),
       GoodsName=ISNULL (goods_big_class_name,''),port_name,
       MaxSN =(Select Max(e.voyages_port_SN) From HY_voyages_port e WHERE (e.ship_code=a.ship_code  AND e.voyages_code=a.voyages_code)),
       Quantity=IsNull(voyages_unLoad_actuality_quantity,0),
       QuantityLoad=IsNull(voyages_Load_actuality_quantity,0),
       NotBusinessTime=IsNull(( Select Sum(IsNull(voyages_not_business_time,0)) From HY_voyages_not_business    WHERE voyages_code=a.voyages_code AND ship_code=a.ship_code   ),0),
       EndTime=(Select Top 1 voyages_unload_end_time From HY_voyages_unload    WHERE voyages_code=a.voyages_code AND ship_code=a.ship_code   ORDER BY voyages_unload_end_time DESC ) ,
       UnloadPort=(Select Top 1 port_name From HY_voyages_unLoad am INNER JOIN HY_port bm On am.port_code=bm.port_code    WHERE voyages_code=a.voyages_code AND ship_code=a.ship_code  ),
       CJState=( Select voyages_income_expend_CJ_state From HY_voyages_income_expend    WHERE voyages_code=a.voyages_code AND ship_code=a.ship_code   ),
       NotOnVoyagesTime=IsNull((IsNull((Select Sum(Datediff(minute,voyages_port_arrive_port_time,voyages_port_off_berth_time))  From HY_voyages_port ABC INNER JOIN HY_voyages_not_business ABCD  ON ABC.ship_code=ABCD.ship_code AND ABC.voyages_code=ABCD.voyages_code   WHERE ABC.ship_code=f.ship_code AND ABC.voyages_code=f.voyages_code AND Datediff(minute,voyages_not_business_event_start_time,voyages_port_arrive_port_time)>0  AND Datediff(minute,voyages_port_off_berth_time,voyages_not_business_event_end_time)>0  AND NOT voyages_port_off_berth_time Is Null AND voyages_port_off_berth_time<>'1900-1-1'),0)+IsNull( (Select Sum(DateDiff(minute,voyages_not_business_event_start_time,voyages_port_off_berth_time))  From HY_voyages_port ABC1 INNER JOIN HY_voyages_not_business ABCD1  ON ABC1.ship_code=ABCD1.ship_code AND ABC1.voyages_code=ABCD1.voyages_code   WHERE ABC1.ship_code=f.ship_code AND ABC1.voyages_code=f.voyages_code AND DateDiff(minute,voyages_not_business_event_start_time,voyages_port_arrive_port_time)<=0  AND DateDiff(minute,voyages_not_business_event_start_time,voyages_port_off_berth_time)>0  AND Datediff(minute,voyages_port_off_berth_time,voyages_not_business_event_end_time)>0),0)+IsNull( (Select Sum(DateDiff(minute,voyages_port_arrive_port_time,voyages_not_business_event_end_time))  From HY_voyages_port ABC2 INNER JOIN HY_voyages_not_business ABCD2  ON ABC2.ship_code=ABCD2.ship_code AND ABC2.voyages_code=ABCD2.voyages_code  WHERE ABC2.ship_code=f.ship_code AND ABC2.voyages_code=f.voyages_code AND DateDiff(minute,voyages_not_business_event_end_time,voyages_port_arrive_port_time)<0  AND DateDiff(minute,voyages_not_business_event_start_time,voyages_port_arrive_port_time)>0  AND DateDiff(minute,voyages_not_business_event_end_time,voyages_port_off_berth_time)>=0),0)+IsNull((Select Sum(voyages_not_business_time*60)  From HY_voyages_port ABC INNER JOIN HY_voyages_not_business ABCD  ON ABC.ship_code=ABCD.ship_code AND ABC.voyages_code=ABCD.voyages_code   WHERE ABC.ship_code=f.ship_code AND ABC.voyages_code=f.voyages_code AND Datediff(minute,voyages_port_arrive_port_time,voyages_not_business_event_start_time)>=0  AND Datediff(minute,voyages_not_business_event_end_time,voyages_port_off_berth_time)>=0),0)),0),
       NotVoyagesNotBusinessTime=IsNull((Select Top 1 voyages_have_not_business_state_code  From HY_voyages  Where ship_code=a.ship_code AND voyages_code<a.voyages_code ORDER BY voyages_code DESC),0)  
FROM HY_voyages_port a 
INNER JOIN HY_voyages f 
      ON (a.ship_code=f.ship_code  AND a.voyages_code=f.voyages_code) 
INNER JOIN   HY_ship    AA  
      ON a.ship_code=AA.ship_code  
INNER JOIN   HY_port     dd  
      ON a.port_code=dd.port_code  
LEFT  JOIN   HY_voyages_load  c   
      ON (a.ship_code=c.ship_code AND a.voyages_code=c.voyages_code AND a.port_code=c.port_code)  
LEFT  JOIN   HY_voyages_unload  e 
      ON (a.ship_code=e.ship_code AND a.voyages_code=e.voyages_code AND a.port_code=e.port_code)  
LEFT  JOIN   HY_goods_big_class d 
      ON e.goods_big_class_code=d.goods_big_class_code  
LEFT  JOIN   HY_line     b 
      ON (Substring(b.line_code,1,3)=a.port_code AND Substring(b.line_code,4,3)=(SELECT TOP 1 c.port_code  FROM HY_voyages_load c Where c.ship_code = a.ship_code And c.voyages_code = a.voyages_code ORDER BY voyages_load_OK_time ASC ))   
WHERE   (  (Datediff(month,@endtime,DateAdd(month,Convert(INT,voyages_statistic_kind),(SubString(voyages_month_code,1,4)+'-'+SubString(voyages_month_code,5,2)+'-01')))<=0 
        AND Datediff(month,@starttime,DateAdd(month,Convert(INT,voyages_statistic_kind),(SubString(voyages_month_code,1,4)+'-'+SubString(voyages_month_code,5,2)+'-01')))>=0)  )   
        AND (@shipcode is null or a.ship_code=@shipcode) 
        AND (@voyageskind is null or voyages_kind=@voyageskind)
ORDER BY a.ship_code,a.voyages_code,voyages_port_SN 
end
GO

解决方案 »

  1.   

    重新发一遍存储过程CREATE PROCEDURE show_voyages_state_voyages(@starttime char(10), 
    @endtime char(10), @shipcode char(3), @voyageskind char(1)) 
    AS BEGIN IF @shipcode = '000'
    SET @shipcode = NULL IF @voyageskind = '0'
    SET @voyageskind = NULL
              SELECT voyages_port_arrive_port_time AS ArriveTime, 
                  voyages_port_off_berth_time AS LeaveTime, voyages_kind, 
                  voyages_port_type_code, a.ship_code, a.voyages_code, ship_name, 
                  a.voyages_port_SN, voyages_start_time AS StartTime, 
                  AMile = ISNULL(b.Line_average_mileage, 0), 
                  AMileLoad = ISNULL(b.Line_average_mileage, 0), 
                  GoodsName = ISNULL(goods_big_class_name, ''), port_name, 
                  MaxSN =
                      (SELECT MAX(e.voyages_port_SN)
                     FROM HY_voyages_port e
                     WHERE (e.ship_code = a.ship_code AND 
                           e.voyages_code = a.voyages_code)), 
                  Quantity = IsNull(voyages_unLoad_actuality_quantity, 0), 
                  QuantityLoad = IsNull(voyages_Load_actuality_quantity, 0), 
                  NotBusinessTime = IsNull
                      ((SELECT SUM(IsNull(voyages_not_business_time, 0))
                      FROM HY_voyages_not_business
                      WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  0), EndTime =
                      (SELECT TOP 1 voyages_unload_end_time
                     FROM HY_voyages_unload
                     WHERE voyages_code = a.voyages_code AND 
                           ship_code = a.ship_code
                     ORDER BY voyages_unload_end_time DESC), UnloadPort =
                      (SELECT TOP 1 port_name
                     FROM HY_voyages_unLoad am INNER JOIN
                           HY_port bm ON am.port_code = bm.port_code
                     WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  CJState =
                      (SELECT voyages_income_expend_CJ_state
                     FROM HY_voyages_income_expend
                     WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  NotOnVoyagesTime = IsNull((IsNull
                      ((SELECT SUM(Datediff(minute, voyages_port_arrive_port_time, 
                            voyages_port_off_berth_time))
                      FROM HY_voyages_port ABC INNER JOIN
                            HY_voyages_not_business ABCD ON 
                            ABC.ship_code = ABCD.ship_code AND 
                            ABC.voyages_code = ABCD.voyages_code
                      WHERE ABC.ship_code = f.ship_code AND 
                            ABC.voyages_code = f.voyages_code AND Datediff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) > 0 AND Datediff(minute, 
                            voyages_port_off_berth_time, 
                            voyages_not_business_event_end_time) > 0 AND 
                            NOT voyages_port_off_berth_time IS NULL AND 
                            voyages_port_off_berth_time <> '1900-1-1'), 0) + IsNull
                      ((SELECT SUM(DateDiff(minute, voyages_not_business_event_start_time, 
                            voyages_port_off_berth_time))
                      FROM HY_voyages_port ABC1 INNER JOIN
                            HY_voyages_not_business ABCD1 ON 
                            ABC1.ship_code = ABCD1.ship_code AND 
                            ABC1.voyages_code = ABCD1.voyages_code
                      WHERE ABC1.ship_code = f.ship_code AND 
                            ABC1.voyages_code = f.voyages_code AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) <= 0 AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_off_berth_time) > 0 AND Datediff(minute, 
                            voyages_port_off_berth_time, 
                            voyages_not_business_event_end_time) > 0), 0) + IsNull
                      ((SELECT SUM(DateDiff(minute, voyages_port_arrive_port_time, 
                            voyages_not_business_event_end_time))
                      FROM HY_voyages_port ABC2 INNER JOIN
                            HY_voyages_not_business ABCD2 ON 
                            ABC2.ship_code = ABCD2.ship_code AND 
                            ABC2.voyages_code = ABCD2.voyages_code
                      WHERE ABC2.ship_code = f.ship_code AND 
                            ABC2.voyages_code = f.voyages_code AND DateDiff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_arrive_port_time) < 0 AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) > 0 AND DateDiff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_off_berth_time) >= 0), 0) + IsNull
                      ((SELECT SUM(voyages_not_business_time * 60)
                      FROM HY_voyages_port ABC INNER JOIN
                            HY_voyages_not_business ABCD ON 
                            ABC.ship_code = ABCD.ship_code AND 
                            ABC.voyages_code = ABCD.voyages_code
                      WHERE ABC.ship_code = f.ship_code AND 
                            ABC.voyages_code = f.voyages_code AND Datediff(minute, 
                            voyages_port_arrive_port_time, 
                            voyages_not_business_event_start_time) >= 0 AND Datediff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_off_berth_time) >= 0), 0)), 0), 
                  NotVoyagesNotBusinessTime = IsNull
                      ((SELECT TOP 1 voyages_have_not_business_state_code
                      FROM HY_voyages
                      WHERE ship_code = a.ship_code AND 
                            voyages_code < a.voyages_code
                      ORDER BY voyages_code DESC), 0)
            FROM HY_voyages_port a INNER JOIN
                  HY_voyages f ON (a.ship_code = f.ship_code AND 
                  a.voyages_code = f.voyages_code) INNER JOIN
                  HY_ship AA ON a.ship_code = AA.ship_code INNER JOIN
                  HY_port dd ON a.port_code = dd.port_code LEFT JOIN
                  HY_voyages_load c ON (a.ship_code = c.ship_code AND 
                  a.voyages_code = c.voyages_code AND a.port_code = c.port_code) LEFT JOIN
                  HY_voyages_unload e ON (a.ship_code = e.ship_code AND 
                  a.voyages_code = e.voyages_code AND a.port_code = e.port_code) LEFT JOIN
                  HY_goods_big_class d ON 
                  e.goods_big_class_code = d .goods_big_class_code LEFT JOIN
                  HY_line b ON (Substring(b.line_code, 1, 3) = a.port_code AND 
                  Substring(b.line_code, 4, 3) =
                      (SELECT TOP 1 c.port_code
                     FROM HY_voyages_load c
                     WHERE c.ship_code = a.ship_code AND 
                           c.voyages_code = a.voyages_code
                     ORDER BY voyages_load_OK_time ASC))
            WHERE ((Datediff(month, @endtime, DateAdd(month, CONVERT(INT, 
                  voyages_statistic_kind), (SubString(voyages_month_code, 1, 4) 
                  + '-' + SubString(voyages_month_code, 5, 2) + '-01'))) <= 0 AND Datediff(month, 
                  @starttime, DateAdd(month, CONVERT(INT, voyages_statistic_kind), 
                  (SubString(voyages_month_code, 1, 4) 
                  + '-' + SubString(voyages_month_code, 5, 2) + '-01'))) >= 0)) AND 
                  (@shipcode IS NULL OR
                  a.ship_code = @shipcode) AND (@voyageskind IS NULL OR
                  voyages_kind = @voyageskind)
            ORDER BY a.ship_code, a.voyages_code, voyages_port_SN END GO
      

  2.   

    CREATE PROCEDURE show_voyages_state_voyages(@starttime char(10), 
    @endtime char(10), @shipcode char(3), @voyageskind char(1)) 
    AS BEGIN IF @shipcode = '000'
    SET @shipcode = NULL IF @voyageskind = '0'
    SET @voyageskind = NULL
              SELECT voyages_port_arrive_port_time AS ArriveTime, 
                  voyages_port_off_berth_time AS LeaveTime, voyages_kind, 
                  voyages_port_type_code, a.ship_code, a.voyages_code, ship_name, 
                  a.voyages_port_SN, voyages_start_time AS StartTime, 
                  AMile = ISNULL(b.Line_average_mileage, 0), 
                  AMileLoad = ISNULL(b.Line_average_mileage, 0), 
                  GoodsName = ISNULL(goods_big_class_name, ''), port_name, 
                  MaxSN =
                      (SELECT MAX(e.voyages_port_SN)
                     FROM HY_voyages_port e
                     WHERE (e.ship_code = a.ship_code AND 
                           e.voyages_code = a.voyages_code)), 
                  Quantity = IsNull(voyages_unLoad_actuality_quantity, 0), 
                  QuantityLoad = IsNull(voyages_Load_actuality_quantity, 0), 
                  NotBusinessTime = IsNull
                      ((SELECT SUM(IsNull(voyages_not_business_time, 0))
                      FROM HY_voyages_not_business
                      WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  0), EndTime =
                      (SELECT TOP 1 voyages_unload_end_time
                     FROM HY_voyages_unload
                     WHERE voyages_code = a.voyages_code AND 
                           ship_code = a.ship_code
                     ORDER BY voyages_unload_end_time DESC), UnloadPort =
                      (SELECT TOP 1 port_name
                     FROM HY_voyages_unLoad am INNER JOIN
                           HY_port bm ON am.port_code = bm.port_code
                     WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  CJState =
                      (SELECT TOP 1 voyages_income_expend_CJ_state
                     FROM HY_voyages_income_expend
                     WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code), 
                  NotOnVoyagesTime = IsNull((IsNull
                      ((SELECT SUM(Datediff(minute, voyages_port_arrive_port_time, 
                            voyages_port_off_berth_time))
                      FROM HY_voyages_port ABC INNER JOIN
                            HY_voyages_not_business ABCD ON 
                            ABC.ship_code = ABCD.ship_code AND 
                            ABC.voyages_code = ABCD.voyages_code
                      WHERE ABC.ship_code = f.ship_code AND 
                            ABC.voyages_code = f.voyages_code AND Datediff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) > 0 AND Datediff(minute, 
                            voyages_port_off_berth_time, 
                            voyages_not_business_event_end_time) > 0 AND 
                            NOT voyages_port_off_berth_time IS NULL AND 
                            voyages_port_off_berth_time <> '1900-1-1'), 0) + IsNull
                      ((SELECT SUM(DateDiff(minute, voyages_not_business_event_start_time, 
                            voyages_port_off_berth_time))
                      FROM HY_voyages_port ABC1 INNER JOIN
                            HY_voyages_not_business ABCD1 ON 
                            ABC1.ship_code = ABCD1.ship_code AND 
                            ABC1.voyages_code = ABCD1.voyages_code
                      WHERE ABC1.ship_code = f.ship_code AND 
                            ABC1.voyages_code = f.voyages_code AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) <= 0 AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_off_berth_time) > 0 AND Datediff(minute, 
                            voyages_port_off_berth_time, 
                            voyages_not_business_event_end_time) > 0), 0) + IsNull
                      ((SELECT SUM(DateDiff(minute, voyages_port_arrive_port_time, 
                            voyages_not_business_event_end_time))
                      FROM HY_voyages_port ABC2 INNER JOIN
                            HY_voyages_not_business ABCD2 ON 
                            ABC2.ship_code = ABCD2.ship_code AND 
                            ABC2.voyages_code = ABCD2.voyages_code
                      WHERE ABC2.ship_code = f.ship_code AND 
                            ABC2.voyages_code = f.voyages_code AND DateDiff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_arrive_port_time) < 0 AND DateDiff(minute, 
                            voyages_not_business_event_start_time, 
                            voyages_port_arrive_port_time) > 0 AND DateDiff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_off_berth_time) >= 0), 0) + IsNull
                      ((SELECT SUM(voyages_not_business_time * 60)
                      FROM HY_voyages_port ABC INNER JOIN
                            HY_voyages_not_business ABCD ON 
                            ABC.ship_code = ABCD.ship_code AND 
                            ABC.voyages_code = ABCD.voyages_code
                      WHERE ABC.ship_code = f.ship_code AND 
                            ABC.voyages_code = f.voyages_code AND Datediff(minute, 
                            voyages_port_arrive_port_time, 
                            voyages_not_business_event_start_time) >= 0 AND Datediff(minute, 
                            voyages_not_business_event_end_time, 
                            voyages_port_off_berth_time) >= 0), 0)), 0), 
                  NotVoyagesNotBusinessTime = IsNull
                      ((SELECT TOP 1 voyages_have_not_business_state_code
                      FROM HY_voyages
                      WHERE ship_code = a.ship_code AND 
                            voyages_code < a.voyages_code
                      ORDER BY voyages_code DESC), 0)
            FROM HY_voyages_port a INNER JOIN
                  HY_voyages f ON (a.ship_code = f.ship_code AND 
                  a.voyages_code = f.voyages_code) INNER JOIN
                  HY_ship AA ON a.ship_code = AA.ship_code INNER JOIN
                  HY_port dd ON a.port_code = dd.port_code LEFT JOIN
                  HY_voyages_load c ON (a.ship_code = c.ship_code AND 
                  a.voyages_code = c.voyages_code AND a.port_code = c.port_code) LEFT JOIN
                  HY_voyages_unload e ON (a.ship_code = e.ship_code AND 
                  a.voyages_code = e.voyages_code AND a.port_code = e.port_code) LEFT JOIN
                  HY_goods_big_class d ON 
                  e.goods_big_class_code = d .goods_big_class_code LEFT JOIN
                  HY_line b ON (Substring(b.line_code, 1, 3) = a.port_code AND 
                  Substring(b.line_code, 4, 3) =
                      (SELECT TOP 1 c.port_code
                     FROM HY_voyages_load c
                     WHERE c.ship_code = a.ship_code AND 
                           c.voyages_code = a.voyages_code
                     ORDER BY voyages_load_OK_time ASC))
            WHERE ((Datediff(month, @endtime, DateAdd(month, CONVERT(INT, 
                  voyages_statistic_kind), (SubString(voyages_month_code, 1, 4) 
                  + '-' + SubString(voyages_month_code, 5, 2) + '-01'))) <= 0 AND Datediff(month, 
                  @starttime, DateAdd(month, CONVERT(INT, voyages_statistic_kind), 
                  (SubString(voyages_month_code, 1, 4) 
                  + '-' + SubString(voyages_month_code, 5, 2) + '-01'))) >= 0)) AND 
                  (@shipcode IS NULL OR
                  a.ship_code = @shipcode) AND (@voyageskind IS NULL OR
                  voyages_kind = @voyageskind)
            ORDER BY a.ship_code, a.voyages_code, voyages_port_SN END GO少个TOP 1，改完再试试
      

  3.   

    估计是你有个地方查出来有2条以上的数据而你用的是= 而不是 in
     CJState=( Select top 1 voyages_income_expend_CJ_state From HY_voyages_income_expend    WHERE voyages_code=a.voyages_code AND ship_code=a.ship_code   ),
    你看看是不是
      

  4.   

                 CJState =
                      (SELECT voyages_income_expend_CJ_state
                     FROM HY_voyages_income_expend
                     WHERE voyages_code = a.voyages_code AND ship_code = a.ship_code),     问题出在这一行，返回多个值，可以用TOP 1 或 min、max聚合来解决。
      

  5.   

    怎样找出有错的子查询？sql有调试的工具吗？