Subqueries with internal table

[Former Member]

hi all,

is it possible to use subqueries with internal table,

my query is

SELECT BUKRS FROM T001

INTO CORRESPONDING FIELDS OF TABLE ITAB_T001

WHERE BUKRS IN ( SELECT BUKRS FROM ITAB_T001_BUKRS )

here , ITAB_T001_BUKRS is an internal table.??

regards

Jose

[Former Member]

hi all,

is it possible to use subqueries with internal table,

my query is

SELECT BUKRS FROM T001

INTO CORRESPONDING FIELDS OF TABLE ITAB_T001

WHERE BUKRS IN ( SELECT BUKRS FROM ITAB_T001_BUKRS )

here , ITAB_T001_BUKRS is an internal table.??

regards

Jose

[Former Member]

Hi,

to my knowledge....SELECT queries always works on DB tables and not on internal tables. READ can work on internal tables.

[dhruv_shah3]

Hi,

Obviously you can use this.

But if you use For all entries then it s good.

SELECT BUKRS FROM <TABLE> INTO CORRESPONDING FIELDS OF TABLE ITAB_T001_BUKRS.

SELECT BUKRS FROM T001

INTO CORRESPONDING FIELDS OF TABLE ITAB_T001

WHERE BUKRS = ITAB_T001_BUKRS-BUKRS.

HTH

Regards,

Dhruv Shah

[Former Member]

HI

thanks...

i used "FOR ALL ENTRIES IN "

but i want to check with 2 internal tables ... like

SELECT T001_{BUKRS T001}BUTXT SKB1~SAKNR

SKAT~TXT20

INTO CORRESPONDING FIELDS OF TABLE ITAB_ZSTMGLRACC

FROM T001 AS T001

LEFT OUTER JOIN SKB1 AS SKB1 ON SKB1_{BUKRS = T001}BUKRS

JOIN SKAT AS SKAT ON SKAT_{SAKNR = SKB1}SAKNR

AND SKAT_{SPRAS = SY-langu AND SKAT}KTOPL = 'EICA'

FOR ALL ENTRIES IN ITAB_T001

WHERE ( T001~BUKRS EQ ITAB_T001-BUKRS

AND T001~SPRAS EQ sy-langu )

FOR ALL ENTRIES IN ITAB_SKAT WHERE SKAT~SAKNR EQ

ITAB_SKAT-SAKNR

i want to select the rows 1 T001 related to 2 internal tables

ITAB_T001 ( key field is BUKRS) and

ITAB_SKAT (key field is sankr)

but it's not possible to check with 2 tables...... any other options ???

[dhruv_shah3]

Hi,

Theres definately option, the thing you have to do is

first take data of two table keep it in third empty internal table.

Now compare the Data of the Third Table with the Data of your remaining table...

HTH

Regards,

Dhruv Shah

[Former Member]

dear friend select will not work on internal tables

Regards,

Ajay

[Former Member]

Hi,

You cannot use sub-query with Internal tables. It can only be used with the DB tables. Instead what you can do is:

Data: WA like line of ITAB_T001_BUKRS.

Loop at ITAB_T001_BUKRS into WA.

Select BUKRS from T001 into corresponding fields of ITAB_T001 where BUKRS = WA-BUKRS.

EndLoop.

This code will solve your purpose.

<REMOVED BY MODERATOR>

Regards

Roshan.

Edited by: Alvaro Tejada Galindo on Feb 28, 2008 12:50 PM

[Former Member]

HI,

select wont work with internal table so combine t001 and skat is not possible here.

select will work with only one internal table ..using for all entries...

but select wont work with 2 internal tables.......

i have to use loop endloop to do this, but it affects performance for large no of records.

anyone have suggestions ??

thax

Jose

[JozsefSzikszai]

hi Jose,

If I understand right you have two internal tables (with company codes and G/L accounts). How are these two internal tables filled?

thanks

ec

[Former Member]

hi Eric,

itabs are filled like,

SELECT BUKRS FROM T001

INTO CORRESPONDING FIELDS OF TABLE ITAB_T001

WHERE BUKRS IN T001_BUKRS

SELECT SAKNR FROM SKAT

INTO CORRESPONDING FIELDS OF TABLE ITAB_SKAT

WHERE SAKNR IN SKAT_SAKNR AND SPRAS = SY-langu

where T001_BUKRS and SKAT_SAKNR are range itab.

regards

Jose

[JozsefSzikszai]

in this case you don't need these two selects, just do:

SELECT T001~BUKRS

T001~BUTXT

SKB1~SAKNR

SKAT~TXT20

INTO CORRESPONDING FIELDS OF TABLE ITAB_ZSTMGLRACC

FROM T001 AS T001

INNER JOIN ska1 AS ska1

ON t001-ktopl EQ ska1~ktopl

INNER JOIN skb1 AS skb1

ON skb1_{bukrs EQ t001}bukrs

AND skb1_{saknr EQ ska1}saknr

INNER JOIN skat AS skat

ON skat_{ktopl EQ SKA1}ktopl

AND skat_{saknr EQ ska1}saknr

WHERE T001~BUKRS IN T001_BUKRS

AND skat~spras EQ sy-langu

AND SKAT~SAKNR IN SKAT_SAKNR

I am not sure about the performance, but give it a try...

[Former Member]

Thanx Eric,

It will work but,

if there is no records in T001_BUKRS, it should show all the records of t001 .

and if there is no records in SKAT_SAKNR it should show all the records of skat

how do i do this....??

thx

Jose

[JozsefSzikszai]

it will show all records, just give it a try...

[Former Member]

No Eric,

suppose T001_BUKRS contains only one record, and SKAT_SAKNR contains no records,

since it is IN <range>, it will return 0 records as result. !!

thnx

Jose

[JozsefSzikszai]

you are wrong, it works! just give it a try!

[Former Member]

Dear Eric,

if the internal table T001_BUKRS, contains anyone records it will check for the internal table too SKAT_SAKNR .. since it's and condition the query output contains null values only. I used it and

tested it ...

thanks and regards

Jose

[JozsefSzikszai]

no, it works, I also tried in our system. just copy paste my code and see...

[Former Member]

Eric - I copied and pasted your code and tried it, but got several syntax errors, so I don't know how you tried it.

I fixed it up and got it running, but it returns no entries if nothing is selected form T001 or SKAT.

I also tried using FOR ALL ENTRIES in a subquery and got a syntax error, so I think the only alternative is separate SELECTs.

Rob

[JozsefSzikszai]

Rob, the code above refers to the original code of Jose, of course I modified it slighty in our system:

TABLES : t001, skat.

DATA : BEGIN OF itab OCCURS 0,

bukrs TYPE t001-bukrs,

butxt TYPE t001-butxt,

saknr TYPE skb1-saknr,

txt20 TYPE skat-txt20,

END OF itab.

SELECT-OPTIONS : s_bukrs FOR t001-bukrs,

s_saknr FOR skat-saknr.

SELECT t001~bukrs

t001~butxt

skb1~saknr

skat~txt20

INTO TABLE itab

FROM t001 AS t001

INNER JOIN ska1 AS ska1

ON t001_{ktopl EQ ska1}ktopl

INNER JOIN skb1 AS skb1

ON skb1_{bukrs EQ t001}bukrs

AND skb1_{saknr EQ ska1}saknr

INNER JOIN skat AS skat

ON skat_{ktopl EQ ska1}ktopl

AND skat_{saknr EQ ska1}saknr

WHERE t001~bukrs IN s_bukrs

AND skat~spras EQ sy-langu

AND skat~saknr IN s_saknr.

I entered one company code and nothing for G/L account and as result I got all the G/Ls created in the company code.

If I don't enter anything into the select options, than it returns all the G/Ls for all the company codes.

[Former Member]

Eric - I guess there's a misunderstanding. If you leave the select-options empty, you will of course get all entries, but if you put in say an invalid account into the GL select-options, then nothing is returned. It's my understanding that the poster wants the company code data in this case.

Rob

[JozsefSzikszai]

Rob - you could be right easily, but sometimes it is really not easy to understand what is the real reuirement of the poster... Probably those guys are right, who simply copy+paste here the whole specification, they just got

Jose: my undestandig was, that you have two select options (or ranges, does not matter) and based on these you want to select some fields from three (or four) different tables. Please correct my if I am wrong!