DB2 SQL - 其中 TCODE 为 RI、RP 或 null.如何?

Question

很难想出一个 WHERE 子句，该子句只返回我的 TCODE 列为 RI、RP 或 null 的值。 试：

WHERE TCODE IN ('RI', 'RP', NULL)

并收到SQL0206错误。 试：

WHERE TCODE = 'RI' or TCODE = 'RP' or TCODE IS NULL

并收到SQL0199错误。

IBM i 上的 DB2，操作系统版本 7.1

任何帮助都非常感谢！！

马 特

编辑查询：

SELECT T01.ORDRJI, T02.TCODE, T02.TRNDT, T02.TRQTY, T01.WHIDJI, T01.BUYRJI
FROM SCHRCP T01
LEFT OUTER JOIN IMHIST T02 ON T01.ORDRJI = T02.ORDNO 
AND T01.ITNOJI = T02.ITNBR AND T01.BKSQJI = T02.BLKSQ
LEFT OUTER JOIN ITMRVA T03 ON T01.ITNOJI = T03.ITNBR
LEFT OUTER JOIN VENNAM T04 ON T01.VNDRJI = T04.VNDNR
WHERE T01.DKDTJI BETWEEN 1170401 AND 1170630 AND
T02.TCODE IN ('RI', 'RP')and is null AND
T01.RSSTJI <> '99' AND
T01.WHIDJI <> '1' AND
T03.ITCLS IN ('BA', 'BF', 'BP', 'CA', 'CM', 'DF', 'EA', 'EP', 'FA', 
'FG', 'FM', 'FP', 'HJ', 'HT', 'JG', 'KT', 'LT', 'MA', 'MP', 'NF', 
'PA', 'PP', 'RB', 'RF', 'RP', 'RS', 'RU', 'SA', 'SF', 'RM', 'WA', 
'WP', 'DH', 'HD', 'MC') AND
T01.ITNOJI NOT IN ('CHARGES', 'MISC', 'MYLAR', 'CRATE', 'NRC', 'NRE')

完全错误：

SQL0199 - Keyword NULL not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT

Answer 1

问题就在这里：

And T02.TCODE IN ('RI', 'RP')and is null

这是无效的语法 - 您需要将其更改为

And 
(
T02.TCODE In ( 'RI', 'RP' )
Or T02.TCODE Is Null
)

但除此之外，查询还有其他问题。WHERE子句在LEFT OUTER JOIN之后执行，因此这些过滤器将导致您的OUTER JOIN表现为INNER JOIN。

若要解决此问题，请将涉及外部表的WHERE条件添加到ON子句中：

Select T01.ORDRJI,
T02.TCODE,
T02.TRNDT,
T02.TRQTY,
T01.WHIDJI,
T01.BUYRJI
From   SCHRCP T01
Left Outer Join IMHIST T02
On T01.ORDRJI = T02.ORDNO
And T01.ITNOJI = T02.ITNBR
And T01.BKSQJI = T02.BLKSQ
And (
T02.TCODE In ( 'RI', 'RP' )
Or T02.TCODE Is Null
)
Left Outer Join ITMRVA T03
On T01.ITNOJI = T03.ITNBR
And T03.ITCLS In ( 'BA', 'BF', 'BP', 'CA', 'CM', 'DF', 'EA', 'EP', 'FA', 'FG', 'FM', 'FP', 'HJ', 'HT', 'JG', 'KT', 'LT', 'MA', 'MP', 'NF', 'PA', 'PP',
'RB', 'RF', 'RP', 'RS', 'RU', 'SA', 'SF', 'RM', 'WA', 'WP', 'DH', 'HD', 'MC'
)
Left Outer Join VENNAM T04
On T01.VNDRJI = T04.VNDNR
Where  T01.DKDTJI Between 1170401 And 1170630
And T01.RSSTJI <> '99'
And T01.WHIDJI <> '1'
And T01.ITNOJI Not In ( 'CHARGES', 'MISC', 'MYLAR', 'CRATE', 'NRC', 'NRE' );