ITPub博客

首页 > 数据库 > 数据库开发技术 > select应用区分

select应用区分

原创 数据库开发技术 作者:zhoufeng1982 时间:2007-09-20 14:53:31 0 删除 编辑

一般在从数据库读取数据的时候,一般的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博客 ” ,链接:https://blog.itpub.net/547380/viewspace-970413/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: 基础知识回顾
请登录后发表评论 登录
全部评论
  • 博文量
    39
  • 访问量
    970515