select应用区分

一般在从数据库读取数据的时候，一般的select语法已经足够用了，但在应用的时候要注意各种写法之间的区别，做了个小例子，分析一下：

例程概要：

从凭证头表bkpf读取符合条件的凭证号码。

抽取条件：

1。记账日期符合画面输入条件；

2。记账凭证类型不等于10，40，91。

[@more@]

selset写法：

1。for all entries的应用

* for all entries
select belnr
from bkpf
into table it_bkpf
for all entries in it_blart
where budat >= s_budat-low
and budat <= s_budat-high
and blart <> it_blart-blart.

分析：这种写法是错误的。因为blart的限制条件无效。

2。where table的应用

* where table
select belnr
from bkpf
into table it_bkpf
where (it_where).

分析：it_where的内容如下

* where budat >= s_budat-low
* and budat <= s_budat-high
* and blart <> '40'
* and blart <> '91'
* and blart <> '10'.

3。not in range的应用

* not in range
select belnr
from bkpf
into table it_bkpf
where budat >= s_budat-low
and budat <= s_budat-high
and blart not in r_blart.

分析：把10，40，91编辑到range表里。

总结：以上三种写法1错误，2，3正确。所以应注意，在使用for all entries的时候，不要进行<>的判断。对于2，3的写法，可能结果正确，但是在select之前要进行一些必要的编辑，也就是会增加一些代码。

当然实现方法很多，各有利弊，使用中应该因地制宜。

附：测试程序代码。

tables: bkpf.

SELECT-OPTIONS: s_budat for bkpf-budat.

types: begin of typ_where,
data1(10) type c,
where1(10) type c,
data2(15) type c,
where2(10) type c,
end of typ_where.

types: begin of typ_bkpf,
belnr type bkpf-belnr,
end of typ_bkpf.

types: begin of typ_blart,
blart type bkpf-blart,
end of typ_blart.

data: it_bkpf type standard table of typ_bkpf,
wa_bkpf type typ_bkpf.

data: it_blart type standard table of typ_blart,
wa_blart type typ_blart.

data: it_where type standard table of typ_where,
wa_where type typ_where.

ranges: r_blart for bkpf-blart.

data: v_line type i.

start-of-selection.
*edit blart
wa_blart-blart = '40'.
append wa_blart to it_blart.

wa_blart-blart = '91'.
append wa_blart to it_blart.

* wa_blart-blart = '10'.
* append wa_blart to it_blart.

*edit where
wa_where-data1 = 'BUDAT'.
wa_where-where1 = ' >='.
wa_where-data2 = ' S_BUDAT-LOW'.
wa_where-where2 = ' AND'.
append wa_where to it_where.

wa_where-data1 = 'BUDAT'.
wa_where-where1 = ' <='.
wa_where-data2 = ' S_BUDAT-HIGH'.
wa_where-where2 = ' AND'.
append wa_where to it_where.

loop at it_blart into wa_blart.
wa_where-data1 = 'BLART'.
wa_where-where1 = ' <>'.
* wa_where-data2 = ' WA_BLART-BLART'.

wa_where-data2 = WA_BLART-BLART.

wa_where-where2 = ' AND'.

at last.
wa_where-where2 = space.
endat.

append wa_where to it_where.
clear wa_where.
endloop.

*edit range
loop at it_blart into wa_blart.
r_blart-sign = 'I'.
r_blart-option = 'EQ'.
r_blart-low = wa_blart-blart.
r_blart-high = space.

append r_blart.
clear r_blart.
endloop.

* for all entries
select belnr
from bkpf
into table it_bkpf
for all entries in it_blart
where budat >= s_budat-low
and budat <= s_budat-high
and blart <> it_blart-blart.

v_line = lines( it_bkpf ).
write: /1 'for all entries:',
20 v_line.

* where table
select belnr
from bkpf
into table it_bkpf
where (it_where).
* where budat >= s_budat-low
* and budat <= s_budat-high
* and blart <> '40'
* and blart <> '91'
* and blart <> '10'.

v_line = lines( it_bkpf ).
write: /1 'where table:',
20 v_line.

* not in range
select belnr
from bkpf
into table it_bkpf
where budat >= s_budat-low
and budat <= s_budat-high
and blart not in r_blart.

v_line = lines( it_bkpf ).
write: /1 'not in range:',
20 v_line.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/547380/viewspace-970413/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/547380/viewspace-970413/