您所在位置：主题：[讨论]在SELECT SQL中使用ALLTRIM()函數的怪問題
GEORGECHIN
[专家分：440] 发布于 2009-09-05 10:35:00
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLTRIM(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]ALLT(UPPER(FILELIST.審核狀況))<>"Y";[/color] ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段查出不任何數據"[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLT(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]UPPER(FILELIST.審核狀況)<>"Y";" [/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到兩條符合的記錄[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLTRIM(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]EMPT(FILELIST.審核狀況)=.T.;[/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到兩條符合的記錄[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLT(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]UPPER(FILELIST.審核狀況)="Y";[/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到很多條符合的記錄
真的無比的驚訝,為什麼增加一個ALLT(刪除空字符,我用LTRI加上RTRI替換也是一樣的效果)就沒有記錄產生?在SQL語句中,是如何對空串操作及對運算符<>是如何進行比對的?
PS:數據源的"審核狀況"字段為字符型的空串,EXAC設置的是精確比較[/color]
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLTRIM(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]ALLT(UPPER(FILELIST.審核狀況))<>"Y";[/color] ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段查出不任何數據"[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLT(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]UPPER(FILELIST.審核狀況)<>"Y";" [/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到兩條符合的記錄[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLTRIM(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]EMPT(FILELIST.審核狀況)=.T.;[/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到兩條符合的記錄[/color]
CREATE SQL VIEW V_TEMP_PARTNO1 AS SELE PART_NO.*;
FROM CMADE!PART_NO INNER JOIN CMADE!FILELIST ON UPPER(ALLT(PART_NO.料號))=ALLTRIM(UPPER(FILELIST.關鍵字));
WHERE UPPER(ALLTRIM(FILELIST.文件類別))="工程料號" .AND. [color=FF0000]UPPER(FILELIST.審核狀況)="Y";[/color]
ORDER BY PART_NO.料號
[color=FF0000]注:以上這一段可以得到很多條符合的記錄
真的無比的驚訝,為什麼增加一個ALLT(刪除空字符,我用LTRI加上RTRI替換也是一樣的效果)就沒有記錄產生?在SQL語句中,是如何對空串操作及對運算符<>是如何進行比對的?
PS:數據源的"審核狀況"字段為字符型的空串,EXAC設置的是精確比較[/color]
