topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Fri, 19 Oct 2007 13:57:58 GMT

Hello Friends,

I am having some problem in this select statement and

I dont want to use Loop..Endloop.

I have to extract from COBRB table based on OBJNR and am using for all entries on T_COSS table.

I want to extract everything from COBRB where the first 12 characters of OBJNR are in same as T_COSS-PAROB.

Here is the select statement I am using and its not working.

      SELECT * FROM COBRB
   APPENDING TABLE T_COBRB
         FOR ALL ENTRIES IN T_COSS
       WHERE OBJNR = T_COSS-PAROB+0(12)%.

ANy Suggestions,

Ster

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:01:01 GMT

Ster,

Good!

whats the message its giving?

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:02:25 GMT

Hi,

do like this

SELECT * FROM COBRB

INTO TABLE T_COBRB

FOR ALL ENTRIES IN T_COSS

WHERE OBJNR = T_COSS-PAROB+0(12).

for performane provide field names instead of *.

<b>Reward for helpful answers</b>

Satish

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:02:35 GMT

do like this.

if T_COSS[] is not initial.

SELECT * FROM COBRB

into TABLE T_COBRB

FOR ALL ENTRIES IN T_COSS

WHERE OBJNR = T_COSS-PAROB+0(12).

endif.

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:03:15 GMT

Its not selecting the data. However it selecting only one row.

Ster

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:04:01 GMT

here you are using both Offsetting of a field as well as % parameter which is separately used with LIKE word in where clause of the select.

use any one of them

use Objnr = T_COSS-PAROB+0(12). or

objnr like 'ORD000%'.

generally we use KOSTL cost center for fetching data and for all entries of that CORB entries we fetch data from COEP or COSP

see sample code

clear cobrb_tab.

refresh cobrb_tab.

select objnr " Object No

rec_objnr1 " Ref Object No

bureg " Dostribution Rule

lfdnr " Sequence No

perbz " Settlement Rule

konty " Acct Assign Category

bukrs " Company Code

kostl " Cost Center

into table cobrb_tab

from cobrb

where kostl in rn_kostl.

sort cobrb_tab by objnr rec_objnr1.

delete adjacent duplicates from cobrb_tab comparing objnr.

Get the Settlement Costs from COEP Table

clear it_set_tab.

refresh it_set_tab.

if not cobrb_tab[] is initial.

select kokrs " Controlling Area

belnr " Acc Document

buzei " Line Item

perio " Period Block

wkgbtr " Value in CO Curr

lednr " Ledger No

objnr " Object No

gjahr " Fiscal Year

wrttp " Actuals

versn " Version

kstar " Cost Element

beknz " Dr/Cr Indicator

parob1 " Partner Object

into table it_set_tab

from coep

for all entries in cobrb_tab

where lednr = c_lednr and

wrttp = c_wrttp2 and

versn = c_versn and

gjahr = p_gjahr and

objnr = cobrb_tab-objnr and

parob1 = cobrb_tab-rec_objnr1 and

beknz in (c_o, c_a).

endif.

Regards

Anji

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:05:27 GMT

Satish, Sreejith.

Objnr is not a 12 character field. I need to do a comparison only for the first 12 fields in both the tables.

Ster

Re: Select Statement

ferry_lianto — Fri, 19 Oct 2007 14:05:57 GMT

Hi,

Please try this.


SELECT * FROM COBRB
FOR ALL ENTRIES IN T_COSS
WHERE OBJNR = T_COSS-PAROB.

  IF COBRB-OBJNR(12) = T_COSS-PAROB(12). 
    MOVE COBRB TO T_COBRB.
    APPEND T_COBRB.
  ENDIF.

ENDSELECT.

Regards,

Ferry Lianto

Re: Select Statement

former_member187255 — Fri, 19 Oct 2007 14:08:07 GMT

Ster,

Can you try this......

    
CONCATENATE T_COSS-PAROB+0(12) '%' INTO L_OBJNR.

SELECT * FROM COBRB
   APPENDING TABLE T_COBRB
         FOR ALL ENTRIES IN T_COSS
       WHERE OBJNR LIKE L_OBJNR.

Hope this helps..

Chandra.

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:09:40 GMT

Thanks Anji,

use Objnr = T_COSS-PAROB+0(12). I cant use the above statement as OBJNR is not a 12 char field.

Ster

Re: Select Statement

Former Member — Fri, 19 Oct 2007 14:39:53 GMT

Thanks Everyone.

This is how i got it resolved.

LOOP AT T_COSS.

CONCATENATE T_COSS-PAROB+0(12) '%' INTO L_OBJNR.

        SELECT * FROM COBRB
     APPENDING TABLE T_COBRB
         WHERE OBJNR LIKE L_OBJNR
           AND ( ( ( ERSJA GE V_YEAR_2 AND ERSJA LE V_YEAR_0 )
                OR ( LETJA GE V_YEAR_2 AND LETJA LE V_YEAR_0 ) )
            OR      ( ERSJA LE V_YEAR_2 AND LETJA GE V_YEAR_0 ) )
           AND KOSTL IN S_KOSTL
           AND KONTY = 'KS'.

      ENDLOOP.

Ster

Re: Select Statement

ferry_lianto — Fri, 19 Oct 2007 14:46:30 GMT

Hi Ster,

You said earlier you don't want to use Loop .. Endloop.

But your final solution is using loop

Regards,

Ferry Lianto