|
一、有区别的函数及解决方案
以下所示的解决方案中的函数定义在untDataBase单元中TAdoConn类的方法中。
|
序号 |
简述 |
Access语法 |
SqlServer语法 |
Oracle语法 |
DB2语法 |
解决方案 | |
01 |
系统时间 |
Date() |
GETDATE() |
SYSDATE |
|
GetSysTimeStr | |
02 |
连接字符串 |
& |
+ |
|| |
+ |
GetConcatStr | |
03 |
截取字符串 |
SubString |
SubStr |
SubString |
SubString |
GetSubStr | |
04 |
小写字符串 |
LCase |
Lower |
Lower |
Lower |
GetLowerStr | |
05 |
大写字符串 |
UCase |
Upper |
Upper |
Upper |
GetUpperStr | |
06 |
查找字符串 |
InStr |
InStr |
CharIndex |
InStr |
GetFindStr | |
07 |
替换空值 |
IIF+IsNull |
Coalesce |
Nvl |
Coalesce |
GetNullStr | |
08 |
条件取值 |
IIF |
Case+When+Else |
DeCode或Case |
IIF |
GetCaseStr | |
09 |
字段类型转换 |
Str、var、…. |
Convert或cast |
To_Char,To_Number. |
GetConvertStr |
GetConvertStr | |
10 |
日期字符串 |
‘2004-10-9’ |
#2004-10-19# |
‘2004-10-9’ |
|
GetDateStr | |
11 |
最大值加1 |
|
|
|
|
GetNextNumStr | |
12 |
Like语句函数 |
Like ‘101* |
Like ‘101%’ |
Like ‘101%’ |
|
GetLikeStr | |
|
|
|
|
|
|
|
二、Access与SQLSERVER部分相同数据库函数及关键字列表
1、 函数
|
序号 |
简述 |
| |
01 |
记数函数 |
Count | |
02 |
最大值 |
Max | |
|
|
|
2、 关键字
|
序号 |
简述 |
| |
01 | |
Like | |
02 |
连接 |
Join | |
03 |
判断空 |
Is Null | |
|
|
|
三、Access与语句SqlServer的语句语法区别
1、 Inser Into …..Select …From 语句：
在ACCESS中以下语句
Insert INTO
PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) (Select 200201,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03')
中后面"(select 200201******.co_Type='03')"中的小括号("(",")")必须去掉才能执行，如下：
Insert INTO
PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) Select 200201,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03'
在SQL SERVER 中都可以
2、 Inner Join 语句1
StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=c.copy_id) on a.user_id=c.user_id where
a.user_opcode=''' +EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +'''';
应该改为
StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=d.copy_id) on a.user_id=c.user_id where
a.user_opcode=''' +EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +'''';
该行代码的检索条件错误：应该把C.copy_id=C.Copy_id 改为c.copy_id=d.copy_id
注：两种写法都能在SQL-SERVER中运行，但c.copy_id=C.copy_id在ACCESS中不能运行
3、 Inner Join 语句2
StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.curcopy_flag=1 and a.copy_id=b.copy_id where b.user_id=' + '''' +TmpPubUserID+ '''';
该为
StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.copy_id=b.copy_id where a.curcopy_flag=''1'' and b.user_id=' + '''' +TmpPubUserID+ '''';
注：两种写法都能在SQL-SERVER中运行，但第一种在ACCESS中不能运行
4、 Inner Join语句3
SQl server 中可以执行以下语句
'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id AND sysroleoption.role_id=:roleid'
但ACCESS中不能，只能
'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id Where sysroleoption.role_id=:roleid'
5、 Update语句
Sql SerVer 中能执行但Access 中不能
'Update sysuserrole SET sysuserrole.role_sort = (Select sysrole.role_sort FROM sysrole Where sysuserrole.role_id = sysrole.role_id and sysuserrole.user_id='01')'
6、 日期比较
SQL SERVER 中用
StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date '
+'From SysCopys '
+'where copy_id='''+LoginCopyID+''' '
+'and start_date<='''+datetostr(LoginDate)+''' '
+'and end_date>='''+datetostr(LoginDate)+'''';
ACCESS中用
StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date '
+'From SysCopys '
+'where copy_id='''+LoginCopyID+''' '
+'and start_date<=#'+datetostr(LoginDate)+'# '
+'and end_date>=#'+datetostr(LoginDate)+'#'
参考以上的第10个函数“GetDateStr”
7、 最大数值获取语句
StrSql:='insert into sysRoleOption '
+'select '''+fidRoleId+''' as Role_ID,opti_id,'
+'convert(numeric,opti_id)-(convert(numeric,opti_parentid)*100)+'+ MaxOptiSort
+' as opti_Sort from sysoption where opti_parentid='''
+PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID
+''' and opti_bottom=''1'+'''';
改为
StrSql:='insert into sysRoleOption '
+'select '''+fidRoleId+''' as Role_ID,opti_id,'
+'opti_id-opti_parentid*100+'+ MaxOptiSort
+' as opti_sort from sysoption where opti_parentid='''
+PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID
+''' and opti_bottom=''1'+''''
注：两种写法都能在SQL-SERVER中运行，但第一种在ACCESS中不能运行
但是考虑会出现Null值以及语句的通用性，可以使用以上的第07个函数“GetNullStr”和第09个函数“GetConvertStr”来完成字符串向数字，空值和0数字的转换：参考GetNextNumStr代码。
1、Sql中不能省略as
2、一次只能执行一条Sql
3、没有substring、cast等函数
4、sql中严格区分整形和字符型
5、没有存储过程、触发器
6、！= 替换为<>
7、时间字符串两边加#号
8、带参数的sql语句中 @用?号替换
附1：用于access的sql
|
SELECT [a.ny] as 年月, sum(iif([A.cykjs]=null,0,[A.cykjs])) AS 油井开井数,ROUND(AVG(A.CCCD),2) AS '采出程度%',Sum(iif([A.ZSKJS]=null,0,[A.ZSKJS])) AS 注水井开井数,Sum(iif([A.RCYNL]=null,0,[A.RCYNL])) AS 日产油T,Cdbl(round(iif(Sum(iif([a.HSYCYL]=null,0,[a.HSYCYL])+iif([a.HSYCSL]=null,0,[a.HSYCSL]))=0, 0,round(Sum(a.YZSL))/Sum(A.HSYCYL+A.HSYCSL)),2)) AS '月注采比%',Cdbl(iif(Sum(iif([A.YCYL]=null,0,[A.YCYL])+iif([A.YCSL]=null,0,[A.YCSL]))=0,null,Cdbl(round(Sum(iif([A.YCSL]=null,0,[A.YCSL]))*100/Sum((iif([A.YCYL]=null,0,[A.YCYL])+iif([A.YCSL]=null,0,[A.YCSL]))),2)))) AS '综合含水%',Sum(iif([A.TCZJS]=null,0,[A.TCZJS])) AS 油井总井数,ROUND(AVG(A.CYSD),2) AS 采油速度,Sum(iif([A.RZSNL]=null,0,[A.RZSNL])) AS '日注水量T',Sum(iif([A.HSDYRCYNL]=null,0,[A.HSDYRCYNL])) AS '日产液T',iif(SUM(iif([A.PJDYM]=NULL,0,1))=0,0,ROUND(SUM([A.PJDYM])/SUM(iif([A.PJDYM]=NULL,0,1)),2)) AS 平均动液面M FROM dbb01 A,Sys_Fla_yqtdm B WHERE A.YTDM=B.YTDM AND B.YTMC=[:YT] GROUP BY NY ORDER BY NY
|
类似功能oracle sql
|
SELECT TO_CHAR(TO_DATE(A.NY,'YYYYMM'),'YYYY-MM') "年月",Sum(NVL(A.CYKJS,0)) "油井开井数" ,ROUND(AVG(A.CCCD),2) "采出程度%",Sum(NVLA.ZSKJS,0)) "注水井开井数" ,Sum(NVL(A.RCYNL,0)) "日产油T" ,TO_NUMBER(DECODE(Sum(NvL(A.HSYCYL,0)+NVL(A.HSYCSL,0)),0,NULL,ROUND(Sum(A.YZSL)/Sum(A.HSYCYL+A.HSYCSL)),2)) "月注采比%" ,TO_NUMBER(DECODE(Sum(Nvl(A.YCYL,0)+Nvl(A.YCSL,0)),0,null,TO_NUMBER(round(Sum(Nvl(A.YCSL,0))*100/Sum((Nvl(A.YCYL,0)+Nvl(A.YCSL,0))),2)))) "综合含水%" ,Sum(NVL(A.TCZJS,0)) "油井总井数" ,ROUND(AVG(A.CYSD),2) "采油速度" ,Sum(NVL(A.RZSNL,0)) "日注水量T",Sum(NVL(A.HSDYRCYNL,0)) "日产液T",to_Number(DECODE(SUM(DECODE(A.PJDYM,NULL,0,1)),0,0,ROUND(SUM(A.PJDYM)/SUM(DECODE(A.PJDYM,NULL,0,1)),2))) "平均动液面M" FROM Dbb01 A ,Sys_Fla_yqtdm B WHERE A.DYDM=B.DDM AND A.LBDM='3' AND Ny>=[:QSRQ] AND Ny<=[:ZZRQ] AND B.YTDM =[:YT] GROUP BY A.NY ORDER BY NY
|
|
