严重求救~~~~一个存储过程在SQL SERVER里能通过却在SYBASE里行不通的问题，在线等，送高分

存储过程如下：
create proc p_order_receive @startdate    datetime = null
as
create table #temp_order_receive
(s_orderno       char(12)           not null,
s_orderqty      numeric(11,3)  not null,
s_n_order       numeric(12,0)              not null,
s_receiveno    char(12)           null,
s_execdate     datetime          null,
s_purqty          numeric(12,0)              null,
s_n_receive    numeric(11,3)  null)
insert #temp_order_receive
       select a.orderno,sum(b.orderqty),count(b.barcode),null,null,0,0
              from  t_order_h a,t_order_d b
              where a.orderno=b.orderno and a.status<>'0'
              and convert(char(8),DATEADD(day, 7, a.execdate),112)=convert(char(8),@startdate,112)
              group by a.orderno
update #temp_order_receive
       set s_receiveno =t_receive_h.receiveno from t_receive_h  where #temp_order_receive.s_orderno=t_receive_h.orderno and t_receive_h.status='1'
update #temp_order_receive
       set s_execdate=t_receive_h.execdate from t_receive_h where #temp_order_receive.s_receiveno=t_receive_h.receiveno
update #temp_order_receive
       set s_purqty =(select sum(t_receive_d.purqty) from t_receive_d where #temp_order_receive.s_receiveno=t_receive_d.receiveno
       group by t_receive_d.receiveno)
       from #temp_order_receive,t_receive_d
       where #temp_order_receive.s_receiveno=t_receive_d.receiveno
update #temp_order_receive
       set  s_n_receive=(select count(barcode) from t_receive_d where #temp_order_receive.s_receiveno=t_receive_d.receiveno)
       from #temp_order_receive,t_receive_d
       where #temp_order_receive.s_receiveno=t_receive_d.receiveno
select s_orderno,s_receiveno,s_execdate,s_orderqty,s_purqty,s_n_order,s_n_receive,
       (s_purqty/s_orderqty + s_n_receive/s_n_order)/2  as stat
        from #temp_order_receive
        order by s_orderno
drop table  #temp_order_receive
return (0)

原来是在SQL SERVER里写的，后来要改到SYBASE里就无法创建，报错如下：
Server Message:  Number  257, Severity  16，
Server 'dzhzb', Procedure 'p_order_receive', Line 11:（还有17）
Implicit conversion from datatype 'VARCHAR' to 'TINYINT' is not allowed.  Use the CONVERT function to run this query.
(return status = -6)
我对SYBASE不太熟，还以为SQL和SYBASE的语法是基本相同的。可没想到会这样，郁闷
因为是公司里的事，比较急。请帮帮忙，100分送上~

7 个解决方案

#1

从报错上看是因为数据转换上的问题，你看一下是否存在VARCHAR to TINYINT

#2

DATEADD(day, 7, a.execdate)应该是:DATEADD(dd, 7, a.execdate)或者DATEADD(dy, 7, a.execdate),
其他地方你看看还有什么类型不匹配

#3

按理说不应该存在VARCHAR to TINYINT,因为我所用到的字段全部没有VARCHAR或TINYINT.
orderno,receiveno在表里也是CHAR,就一个COUNT()出来的是INT......但我把数据类型该为INT后报错更多,我一直怀疑是COUNT的问题,可报VARCHAR to TINYINT我实在想不通.

#4

问题找到了一个，不知道是不是真正的原因。我发现在我的SYBASE里
select a.orderno,sum(b.orderqty),count(b.barcode),null,null,0,0
              from  t_order_h a,t_order_d b
              where a.orderno=b.orderno and a.status<>'0'
              and convert(char(8),DATEADD(dd, 7, a.execdate),112)=convert(char(8),@startdate,112)
              group by a.orderno
这个SELECT语句跟本无法查询!而且报的错就是"Implicit conversion from datatype 'VARCHAR' to 'TINYINT' is not allowed.  Use the CONVERT function to run this query."
为什么会这样呢?望高手能告诉我原因何在,如何更正.我的SYBASE版本是11.5的.谢谢~~~~

#5

会不会是这里:a.status<>'0',你的a.status是不是定义为整型的?

#6

sybase对于sql server中的很对强制转换都是不支持的，你的肯定是你其他表的类型是永不正确没有你的其他标的定义，没有办法找到，但是你自己可以仔细核对

#7

谢谢difall(difall)，的确是a.status<>'0'的问题。在SQL SERVER里习惯了用引号。没想到SYBASE里整型居然不用引号。对SYBASE真的真的很恼火........

#1