topic Re: Can I use open SQL to internal table like static table? in Application Development and Automation Discussions

Can I use open SQL to internal table like static table?

Former Member — Tue, 20 Mar 2007 06:45:23 GMT

HI expert.

I know that I can't use open SQL with internal table.

so If I want use SQL like 'count' ,'distinct','sum' I used to put data to static table first and get result whatever I want .

but if I-table is dynamic table that way is not possible.

is it possible use SQL with I-table itself ?

or is there any other ways can use SQL with I-table freely?

thanks

Regards

Kim

Re: Can I use open SQL to internal table like static table?

ashok_kumar24116 — Tue, 20 Mar 2007 06:51:08 GMT

Hi inchul kim ,

Good Check out the following cases.

Eg: 1

----

SELECT DISTINCT kunnr from <database> into table itab2.

describe table itab2 lines v_lines.....

Eg: 2

----

It's always better to use the same field names inside your internal tables so that you can execute these selects with a faster and easier responses.

TYPES: BEGIN OF t_materialstruct,

matnr TYPE marc-matnr,

werks TYPE marc-werks,

verpr TYPE mbew-verpr,

END OF t_materialstruct.

DATA: i_materialtab TYPE TABLE OF t_materialstruct WITH HEADER LINE.

SELECT verpr

APPENDING CORRESPONDING FIELDS OF TABLE i_materialtab

FROM mbew

WHERE bwkey = '3100'.

Eg: 3

----

If the internal table already has, let us say, some 10 records, you can certainly add new records to the internal table using the syntax :

SELECT VERPR

FROM MBEW

APPENDING CORRESPONDING FIELDS OF i_MaterialTab

WHERE BWKEY = 3100.

Good Luck and thanks

Re: Can I use open SQL to internal table like static table?

Former Member — Tue, 20 Mar 2007 06:52:50 GMT

Hi,

You can use the SQL statements like INSERT,MODIFY,DELETE,READ but can't use

SELECT,UPDATE,ALTER statements.

regards,

Anji

Re: Can I use open SQL to internal table like static table?

Former Member — Tue, 20 Mar 2007 07:13:14 GMT

Dear Kim,

You may check this out ....using AT commands for sum .

DATA: BEGIN OF line,

col1(1) TYPE c,

col2 TYPE i,

col3 TYPE i,

END OF line.

DATA itab LIKE HASHED TABLE OF line

WITH UNIQUE KEY col1 col2.

line-col1 = 'A'.

DO 3 TIMES.

line-col2 = sy-index.

line-col3 = sy-index ** 2.

INSERT line INTO TABLE itab.

ENDDO.

line-col1 = 'B'.

DO 3 TIMES.

line-col2 = 2 * sy-index.

line-col3 = ( 2 * sy-index ) ** 2.

INSERT line INTO TABLE itab.

ENDDO.

SORT itab.

LOOP AT itab INTO line.

WRITE: / line-col1, line-col2, line-col3.

AT END OF col1.

SUM.

ULINE.

WRITE: / line-col1, line-col2, line-col3.

SKIP.

ENDAT.

AT LAST.

SUM.

ULINE.

WRITE: / line-col1, line-col2, line-col3.

ENDAT.

ENDLOOP.

The list output is:

A 1 1

A 2 4

A 3 9

________________________________

A 6 14

B 2 4

B 4 16

B 6 36

________________________________

B 12 56

________________________________

18 70

The program creates a hashed table itab, fills it with six lines, and sorts it. In the LOOP - ENDLOOP block, the work area LINE is output for each loop pass. The first field of the table key, col1, is used for control level processing. The total for all numeric fields is always calculated when the contents of col1 change and when the system is in the last loop pass.

<b>If you find this useful , you may check this</b>

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb381a358411d1829f0000e829fbfe/content.htm

Pls Reward if useful.

Regards,

Shweta

Re: Can I use open SQL to internal table like static table?

Former Member — Tue, 20 Mar 2007 07:26:38 GMT

thank you all of you

I tried to find easy way to use SQL with I-table

but seems to be more resonable if I just get result by making loop I-table.

Regards.

Kim