topic Re: Select statement (new to ABAP) in Application Development and Automation Discussions

Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:37:17 GMT

Hi, I just get to know ABAP and have been studying about it. I'm practising an exercise in which i use select statement to retrieve data from many tables with the same field. The tables i use are COSS, AFVC, PRPS and IMZO with the same field OBJNR. The data i need to retrieve is POSNR from table IMZO. This is the code that i've just written. Howvever, when i execute it, it said 'Unable to interpret t_imobj-posnr'. I think the code is not right somehow. Very appreciate any guides.


REPORT  Z_IMPOS_TEST.

TABLES: coss,       "CO Object: Cost Totals for Internal Postings
        afvc,       "Operation within an order
        prps,       "WBS (Work Breakdown Structure) Element Master Data
        imzo.       "Table: CO Object - Capital Investment Prog.Pos.

TYPES: BEGIN OF st_impos,
  objnr       TYPE coss-objnr,
  gjahr       TYPE coss-gjahr,
  pspnr       TYPE prps-pspnr,
  posnr       TYPE imzo-posnr,
END OF st_impos.

DATA : st_imobj    TYPE st_impos,
       t_imobj     TYPE STANDARD TABLE OF st_impos.

*PARAMETERS: p_year TYPE coss-gjahr OBLIGATORY.

SELECT objnr gjahr
      INTO CORRESPONDING FIELDS OF st_imobj
      FROM  coss
      WHERE objnr = st_imobj-objnr
      AND  gjahr = '2007'.
   SELECT objnr
      INTO st_imobj-objnr
      FROM afvc
      WHERE projn = st_imobj-pspnr.
         SELECT objnr
         INTO st_imobj-objnr
         FROM imzo
         WHERE posnr = st_imobj-posnr.
         st_imobj-posnr = st_imobj-posnr.
         st_imobj-gjahr = '2007'.
         APPEND st_imobj TO t_imobj.
         ENDSELECT.
    ENDSELECT.
  ENDSELECT.
  
  WRITE: 'InvProgPosition is : '  t_imobj-posnr.

Re: Select statement (new to ABAP)

ThomasZloch — Wed, 22 Apr 2009 16:39:40 GMT

Insert a colon before t_imobj-posnr in the last line.

WRITE: 'InvProgPosition is : ' , t_imobj-posnr.

That might not be the only problem though, st_imobj-objnr is empty when you are doing the first select, so that will not return anything.

Thomas

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:41:37 GMT

You cannot do write <table name>-<field name>

write: t_imobj-posnr " This line is in error

Use this way

loop at t_imobj into st_imobj.
write: st_imobj-posnr.
endloop.

Regards,

Lalit Mohan Gupta.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:43:30 GMT

Oh i see. That's fixed. Now i can excute it, but in the write statement, there's no value.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:44:32 GMT

The table t_imobj don´t have header line.

loop at t_imobj into st_imobj.
write: st_imobj-posnr.
endloop.

Edited by: Alvaro Achin on Apr 22, 2009 6:45 PM

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:46:46 GMT

so possibly you are not getting any values from select statement for your condition.

Check for sy-subrc after select. You this after your select.

If sy-subrc ne 0.
" Error message saying no data available.
Endif.

Regards,

Lalit Mohan Gupta.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:46:47 GMT

In the first place, you never get the posnr from the table in the select, so of course is going to be empty.

Second, using select... endselect is practically obsolete.

There are several things wrong in your code. Which is fine since you are starting.

Good luck with ABAP

Re: Select statement (new to ABAP)

Jay71 — Wed, 22 Apr 2009 16:50:07 GMT

Hi,

SELECT objnr gjahr

INTO CORRESPONDING FIELDS OF st_imobj

FROM coss

WHERE objnr = st_imobj-objnr

AND gjahr = '2007'.

this is worng.......

and dont use select----endselect...just an advice.

and i see that the way u structured ur select query ur bound to get no data!

explain the flow based on which u need to get the data sum1 will guide u.

gudluck and have a gud time coding in abap.

Edited by: JAYARAM MAGANTI on Apr 22, 2009 6:51 PM

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:54:44 GMT

Thanks everyone. One problem right now it keeps saying t_imoject without header line. I change its declaration to

t_imobj TYPE st_impos OCCURS 0 WITH HEADER LINE.

but it still doesn't work.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:58:20 GMT

Well his select doesn't make any sense at all.

1. He is starting a where with an empty condition (st_imobj-objnr)

2. In afvc he is getting again the objnr and placing it in the same variable. Doesn't make sense what he is trying to do, is wasting resources getting another time the objnr.

3. The same select again has an empty condition in st_imobj-pspnr because he never selected the project number in the select before.

4. He is getting the objnr again (is it different from the last two? then why select them in the first place if you are never going to use them?).

5. Again another empty where in st_imobj-posnr.

6. appends an empty posnr.

And forget about the three nested select endselect. I'm pretty sure what he is trying to do can be reduced to one select only getting one column.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 16:58:23 GMT

DATA : t_imobj     TYPE STANDARD TABLE OF st_impos WITH HEADER LINE.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 17:03:04 GMT

Thanks for pointing out my mistakes. But you mention about one select statement only, is it a join select statement?

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 17:21:21 GMT

You know, I spend a little time trying to get an idea what you are looking for, but I couldn't.

I think is better if you can post the description you are using so we can give you an example.

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 17:43:36 GMT

Sorry for giving confusion. This is what i want for the program. When i pick any Object number, i will get the Invesment Prohram Pos (POSNR). OBJNR can be retrieved in table COSS. So for example, a data in OBJNR is PR00113384 (that i get from COSS). I go to table AFVC, i enter this OBJNR, I get a corresponding PROJN (WBS Element), provided that afvc-objnr = coss-objnr. I go to PRPS, i enter this OBJNR, u will get the same WBS Element ( provided that PRPS-PSPNR = AFVC-PROJN). Finally, i go to table IMZO, i enter the OBJNR again, i will get POSNR (provided that imzo-objnr = prps-objnr).

Re: Select statement (new to ABAP)

Former Member — Wed, 22 Apr 2009 21:53:49 GMT

any help? I get stuck now since i'm trying to do the nested select statement but it still gives me errors.

Re: Select statement (new to ABAP)

Former Member — Thu, 23 Apr 2009 03:55:25 GMT

Hi,

You are not passing any value to OBJNR , before select so you will not get any record for the select.

Before 1st select you need to assign values as follows:

st_imobj-objnr = XXXXXX.

Also after you are able to get data you can using

select single or

Select .... into table ....

to optimize the code.

Regards,

komal

Re: Select statement (new to ABAP)

Former Member — Thu, 23 Apr 2009 05:00:28 GMT

Hi,

the first select statement is selecting values for blanks or null..so no values are selected into tat..the 2nd select is based on the first so obviously no values flow into tat & tats why you are getting no values in the output..always declare internal tables with an header line or an explicit workarea.. While writing statements in an internal table always use loop.

loop at t_imobj into st_imobj.

write: st_imobj-posnr.

endloop.

You had given t_imobj-posnr which has no headerline..tat was the error in the first case..hope its solved now..

Any more queries feel free to get back..

Regards

Jai..

Re: Select statement (new to ABAP)

Former Member — Thu, 23 Apr 2009 06:59:05 GMT

after every selct chk whether the condition is satisfied if no data is selected then below select will have all the values of the table into ur internal table.

so go for if sy-subrc eq 0 .

then go for select.

Re: Select statement (new to ABAP)

Former Member — Thu, 23 Apr 2009 20:52:37 GMT

thank you everyone. I fixed most of the lines in the code. The syntac is correct now, but since i used nested select statements, it takes like FOREVER to give the output when i execute it!. Are there any other ways to make the code more effecient?


REPORT  z_impos_test.

TABLES: coss,       "CO Object: Cost Totals for Internal Postings
        afvc,       "Operation within an order
        prps,       "WBS (Work Breakdown Structure) Element Master Data
        imzo.       "Table: CO Object - Capital Investment Prog.Pos.

TYPES: BEGIN OF st_impos,
  objnr       TYPE coss-objnr,
  gjahr       TYPE coss-gjahr,
  kstar       type coss-kstar,
  projn       type afvc-projn,
  pspnr       type prps-pspnr,
  posnr       type imzo-posnr,
END OF st_impos.

data: year TYPE coss-gjahr value '2007'.

DATA: t_output  TYPE STANDARD TABLE OF st_impos WITH HEADER LINE,
      st_output TYPE st_impos,
      t_output2 TYPE STANDARD TABLE OF st_impos WITH HEADER LINE,
      st_output2 TYPE st_impos.

SELECT objnr gjahr kstar FROM coss
INTO CORRESPONDING FIELDS OF st_output
WHERE ( objnr LIKE 'NV%' OR
      objnr LIKE 'PR%' ) AND
       gjahr = year.
   SELECT SINGLE projn from afvc into CORRESPONDING FIELDS OF st_output
       WHERE objnr = st_output-objnr.
 APPEND st_output to t_output.
 ENDSELECT.

SORT t_output BY objnr.
DELETE ADJACENT DUPLICATES FROM t_output.

LOOP AT t_output into st_output.
SELECT objnr pspnr
       INTO CORRESPONDING FIELDS OF st_output2
       FROM prps
       WHERE objnr = st_output-objnr
       AND   pspnr = st_output-pspnr.
  SELECT SINGLE posnr from imzo into CORRESPONDING FIELDS OF st_output2
       WHERE objnr = st_output2-objnr.
APPEND st_output2 to t_output2.
ENDSELECT.
ENDLOOP.


LOOP AT t_output2 to st_output2.
WRITE:   st_output2-posnr.
ENDLOOP.

Re: Select statement (new to ABAP)

Former Member — Thu, 23 Apr 2009 21:04:44 GMT

Jacie - I think this is a new, unrelated problem. Would you please close this thread by assigning points to everyone who helped you and open a new thread in with just the last bit of code and a description of your problem.

(I mean all the code in your last post)

Rob

Edited by: Rob Burbank on Apr 23, 2009 5:10 PM