我在plsqldev里从SQLSERVER向ORACLE一个表导数据(导很多字段,为了描述简单点,把其它无关的去掉了)
d_sll,d_sellsub为sqlserver 表,getsure_date为datetime类型select to_char(a."getsure_date",'yyyymmdd')
from d_sell@mssql a inner join (select "sellid" as sellid,"clothingid" as clothingid, Max("styleid")as styleid, Max("colorid")as colorid, Max("sizeid")as sizeid,
avg("j_cost")as j_cost, avg("j_price")as j_price, avg("x_price")as x_price, avg("discount")as discount,
avg("a_discount")as a_discount, avg("s_price")as s_price,
sum("set_nums")as set_nums, sum("get_nums")as get_nums, sum("chknums")as chknums, Max("comment")as commentsub,
avg("backrate")as backrate, Max("backmaxday")as backmaxday , avg("numrate")as numrate , avg("styrate")as styratz
from d_sellsub@mssql Group by "sellid" ,"clothingid" )b on a."sellid"=b.sellid
where a."get_sure" > 0 and a."sell_no"=1
报错:数据库MSSQL不支持上下文中的 TO_CHAR
但这样却没问题:
select to_char(a."getsure_date",'yyyymmdd') from d_sell@mssql a where a."sell_no"=1 这样也没问题:
select a."getsure_date"
from d_sell@mssql a inner join (select "sellid" as sellid,"clothingid" as clothingid, Max("styleid")as styleid, Max("colorid")as colorid, Max("sizeid")as sizeid,
avg("j_cost")as j_cost, avg("j_price")as j_price, avg("x_price")as x_price, avg("discount")as discount,
avg("a_discount")as a_discount, avg("s_price")as s_price,
sum("set_nums")as set_nums, sum("get_nums")as get_nums, sum("chknums")as chknums, Max("comment")as commentsub,
avg("backrate")as backrate, Max("backmaxday")as backmaxday , avg("numrate")as numrate , avg("styrate")as styratz
from d_sellsub@mssql Group by "sellid" ,"clothingid" )b on a."sellid"=b.sellid
where a."get_sure" > 0 and a."sell_no"=1
不明白为什么会这样呢?
d_sll,d_sellsub为sqlserver 表,getsure_date为datetime类型select to_char(a."getsure_date",'yyyymmdd')
from d_sell@mssql a inner join (select "sellid" as sellid,"clothingid" as clothingid, Max("styleid")as styleid, Max("colorid")as colorid, Max("sizeid")as sizeid,
avg("j_cost")as j_cost, avg("j_price")as j_price, avg("x_price")as x_price, avg("discount")as discount,
avg("a_discount")as a_discount, avg("s_price")as s_price,
sum("set_nums")as set_nums, sum("get_nums")as get_nums, sum("chknums")as chknums, Max("comment")as commentsub,
avg("backrate")as backrate, Max("backmaxday")as backmaxday , avg("numrate")as numrate , avg("styrate")as styratz
from d_sellsub@mssql Group by "sellid" ,"clothingid" )b on a."sellid"=b.sellid
where a."get_sure" > 0 and a."sell_no"=1
报错:数据库MSSQL不支持上下文中的 TO_CHAR
但这样却没问题:
select to_char(a."getsure_date",'yyyymmdd') from d_sell@mssql a where a."sell_no"=1 这样也没问题:
select a."getsure_date"
from d_sell@mssql a inner join (select "sellid" as sellid,"clothingid" as clothingid, Max("styleid")as styleid, Max("colorid")as colorid, Max("sizeid")as sizeid,
avg("j_cost")as j_cost, avg("j_price")as j_price, avg("x_price")as x_price, avg("discount")as discount,
avg("a_discount")as a_discount, avg("s_price")as s_price,
sum("set_nums")as set_nums, sum("get_nums")as get_nums, sum("chknums")as chknums, Max("comment")as commentsub,
avg("backrate")as backrate, Max("backmaxday")as backmaxday , avg("numrate")as numrate , avg("styrate")as styratz
from d_sellsub@mssql Group by "sellid" ,"clothingid" )b on a."sellid"=b.sellid
where a."get_sure" > 0 and a."sell_no"=1
不明白为什么会这样呢?
看看有没有帮助Oracle的TO_CHAR函数可以把n位NUMBER数据类型转换为VARCHAR2 数据类型,同时采用可选的数字格式。SQL Server则通过STR函数返回数字转换之后的字符数据。不过,该函数不具方便的Format参数。Oracle查询如下:
SELECT to_char(123.45 ,99999999999999) from tab
SELECT to_char(EXPIRY_DATE,'DDMONYYYY') from tab以下是SQL Server版本的查询:
SELECT STR(123.45, 14)
SELECT STR(round(123.455 , 2),12,2)
SELECT CAST(REPLACE((CONVERT(varchar(12) , EXPIRYDATE, 106 )),' ' , '') as varchar(9))
select to_char("getsure_date",'yyyymmdd')
from
(
select a."getsure_date"
from d_sell@mssql a inner join (select "sellid" as sellid,"clothingid" as clothingid, Max("styleid")as styleid, Max("colorid")as colorid, Max("sizeid")as sizeid,
avg("j_cost")as j_cost, avg("j_price")as j_price, avg("x_price")as x_price, avg("discount")as discount,
avg("a_discount")as a_discount, avg("s_price")as s_price,
sum("set_nums")as set_nums, sum("get_nums")as get_nums, sum("chknums")as chknums, Max("comment")as commentsub,
avg("backrate")as backrate, Max("backmaxday")as backmaxday , avg("numrate")as numrate , avg("styrate")as styratz
from d_sellsub@mssql Group by "sellid" ,"clothingid" )b on a."sellid"=b.sellid
where a."get_sure" > 0 and a."sell_no"=1
)