topic Re: Subqueries with internal table in Application Development and Automation Discussions

Subqueries with internal table

Former Member — Thu, 28 Feb 2008 11:08:29 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 11:12:05 GMT

Hi,

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

Re: Subqueries with internal table

dhruv_shah3 — Thu, 28 Feb 2008 11:12:49 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 12:38:13 GMT

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 ???

Re: Subqueries with internal table

dhruv_shah3 — Thu, 28 Feb 2008 12:50:16 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 13:03:47 GMT

dear friend select will not work on internal tables

Regards,

Ajay

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 13:08:10 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 13:13:24 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Thu, 28 Feb 2008 13:22:21 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 13:37:07 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Thu, 28 Feb 2008 13:54:32 GMT

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...

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 14:15:50 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Thu, 28 Feb 2008 14:19:17 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 14:28:00 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Thu, 28 Feb 2008 14:52:47 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 18:33:40 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Thu, 28 Feb 2008 22:34:15 GMT

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

Re: Subqueries with internal table

Former Member — Thu, 28 Feb 2008 23:03:58 GMT

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

Re: Subqueries with internal table

JozsefSzikszai — Fri, 29 Feb 2008 07:39:33 GMT

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.

Re: Subqueries with internal table

Former Member — Fri, 29 Feb 2008 14:18:12 GMT

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