topic Re: Select Query.. in Application Development and Automation Discussions

Select Query..

Former Member — Thu, 30 Jul 2009 16:46:30 GMT

Dear expert,

I have to select two field values F2 & F3 from database table TAB1 based on field value of field F1(primary key field value), then i need to select field value P1 from another table TAB2 based on field values(F2 &F3) selected from above table TAB1.

I have written following query, but it the internal table is not gettting populated.

DATA: BEGIN OF i_tab OCCURS 1,

warpl LIKE afih-warpl,

abnum LIKE afih-abnum,

END OF i_tab.

DATA: BEGIN OF i_nplda OCCURS 1,

nplda LIKE mhis-nplda,

END OF i_nplda.

Select warpl abnum from AFIH

into i_tab

where aufnr eq p_aufnr.

Select nplda from MHIS

into i_nplda

where warpl eq i_tab-warpl

And abnum eq i_tab-abnum.

Endselect.

Please advise on the query.

Thanks

Re: Select Query..

Former Member — Thu, 30 Jul 2009 17:00:59 GMT

Hi ,

try like this...



 DATA: BEGIN OF i_afih OCCURS 1,
         warpl  type AFIH-warpl
         abnum  type AFIH-abnum,
       END OF i_aifh.

 DATA: BEGIN OF i_mhis OCCURS 1,
         warpl  type mhis-warpl
         abnum  type mhisH-abnum,
         nplda LIKE mhis-nplda,
       END OF i_mhis.
 
 Select warpl abnum from AFIH
            into table i_aifh.
          where aufnr eq p_aufnr.

select warpl abnum nplda from Mhis
into table i_mhis
         for all entries of i_aifh
         where warpl = i_aifh-warpl 
         and abnum = i_aifh-abnum.

Loop at i_mhis.
  wrtie : i_mhis-nplda.            "display
"below is optional
  read table i_aifh with key warpl = i_mhis-warpl 
         abnum = i_mhis-abnum.
if sy-subrc = 0.
  "do calculation or modify 
endif.
Endloop

prabhudas

Re: Select Query..

Former Member — Thu, 30 Jul 2009 17:06:06 GMT

Use the Below Code...

Try to Avoid Nested SELECT....ENDSELECT.



DATA: BEGIN OF i_tab OCCURS 0,
            aufnr type aufnr,		
            warpl LIKE afih-warpl,
            abnum LIKE afih-abnum,
            END OF i_tab.

DATA: BEGIN OF i_nplda OCCURS 0,
            warpl LIKE afih-warpl,
            abnum LIKE afih-abnum,
            nplda LIKE mhis-nplda,
            END OF i_nplda.

refresh i_tab.
Select aufnr warpl abnum 
       from AFIH
       into table i_tab
       where aufnr eq p_aufnr.

if sy-subrc eq 0.

            refresh i_nplda.
            Select warpl abnum nplda 
            from MHIS
            into table i_nplda
           for all entries in i_tab	
           where warpl eq i_tab-warpl
               And abnum eq i_tab-abnum.

Endif.

Re: Select Query..

Former Member — Thu, 30 Jul 2009 19:28:35 GMT

Hi,

Using for all entries (or) Inner join logic ,we can achieve your reqirement,see Debabrata ,in select queries dont use select....endselect ,try to avoid it,also nested select dont use anywhere...it will give the performance lacking,instead of select.....endselect queries we can use "for all entries ...select single... inner join" ,from this for all entries is better one to use....before that you should give one validation" if 1st internal table is not initial,then goto second select queries process"

In data dictionary level (SE11),you have to known about the link between these tables, we can easily identify these things,if not ,by using quick viewer tool ,we can trace the link...

from your requirement " WARPL , ABNUM " these two fields are the link

DATA: BEGIN OF i_tab OCCURS 0, - ---> change 1 to 0

warpl LIKE afih-warpl,

abnum LIKE afih-abnum,

END OF i_tab.

DATA: BEGIN OF i_nplda OCCURS 0, -

>change 1 to 0

nplda LIKE mhis-nplda,

END OF i_nplda.

Select warpl

abnum from AFIH -

> 1st internal table

into table i_tab

where aufnr eq p_aufnr.

if not i_tab is initial.

Select warpl

abnum

nplda

from MHIS -

> 2nd internal table,here for all entries

into table i_nplda

for all entries in i_tab

where warpl eq i_tab-warpl

and abnum eq i_tab-abnum.

endif.

loop at i_tab into wa_tab.

read table i_nplda into wa_nplda

with key warpl eq wa_tab-warpl

abnum eq wa_tab-abnum.

............

........... ---> here ,you can code your logic

endloop ---> this is for multiple records,

if you want only single record means, use "Select Single from DB table into work area

I hope,it will help you.

<=<< Sharing Knowledge is a way to Innovative >>=>

By,

Yoga

Re: Select Query..

Former Member — Fri, 31 Jul 2009 05:13:37 GMT

Hello,

I don't see APPEND statement in your code which will fill the internal table.

Also, using SELECT...ENDSELECT in nested way is not good ABAP practice.

Try avoiding loop as much as possible to gain the performance.

Hope this helps,

Thanks,

Augustin.

Re: Select Query..

Former Member — Fri, 31 Jul 2009 05:27:19 GMT

hii Debabrata,

U can achieve this through

FOR ALL ENTRIES

in select query ,but before doing that make sure that ur first internal table is not empty.

Use statement ,

If IT1 is not initial, then only fire select query

, bcoz if It1 is initial and u fire select query then all records from datbase

table will get fetched.

Regards,

Apoorv

Re: Select Query..

Former Member — Fri, 31 Jul 2009 06:18:31 GMT

Hi Debabrata,

your code:

DATA: BEGIN OF i_tab OCCURS 1,

warpl LIKE afih-warpl,

abnum LIKE afih-abnum,

END OF i_tab.

DATA: BEGIN OF i_nplda OCCURS 1,

nplda LIKE mhis-nplda,

END OF i_nplda.

Select warpl abnum from AFIH

into i_tab

where aufnr eq p_aufnr.

Select nplda from MHIS

into i_nplda

where warpl eq i_tab-warpl

And abnum eq i_tab-abnum.

Endselect.

performance wise above code is bad.

when you are coding try to follow few points:

1. don't use occurs0, instead of that decleare type

2. don't use select-endselect, use for all entries.

3. declare one final internal table & move values from diff internal tables to final internal table.

like:

types: begin of i_tab,

end of i_tab.

types: begin of i_nplda,

end of i_nplda.

types: begin of i_final,

decleare all the required fields to be displayed as output.

end of i_final

Select warpl abnum from AFIH

into i_tab

where aufnr eq p_aufnr.

Select nplda from MHIS

into i_nplda

for all entries of i_tab

where warpl eq i_tab-warpl

And abnum eq i_tab-abnum.

loop at i_ta inti wa_tab.

wa_final-warpl = wa_tab-warpl.

...........

read table i_nplda into wa_nplda with key........

...................................

append wa_final to i_final.

clear: wa_final.

endloop.

Hope this can solve your problems.

Regards,

Tutun

if i_tab is not initial.