topic Re: To fetch data based on materials or company code in Application Development and Automation Discussions

To fetch data based on materials or company code

Former Member — Tue, 24 Sep 2019 03:14:28 GMT

I have kept materials and company code as parameters on selection screen.

Now I want to fetch data based on materials or company code.

If material is given on selection screen then that material should be displayed for all available company codes.

If company code is given then all materials under that company code should be displayed.

If both are given only that material and company code should be displayed.

Please guide me through this.

Re: To fetch data based on materials or company code

FredericGirod — Tue, 24 Sep 2019 05:16:40 GMT

The tables readed is not the good one.

Plant --> T001W-WERKS --> T001W-BWKEY --> T001k-BWKEY --> T001K-BUKRS <-- company code

Plant + Material --> MARC-WERKS & MATNR

Re: To fetch data based on materials or company code

former_member1716 — Tue, 24 Sep 2019 06:49:45 GMT

You Are right, i understood it other way.

Thanks!

Re: To fetch data based on materials or company code

SimoneMilesi — Tue, 24 Sep 2019 11:20:16 GMT

I find it over complex without a real reason, to be honest, and it lacks the information material - company code.

A simple join (T001K - T001W-MARC) will work better, cleaner, without the big IF chunk and would avoid the FOR ALL ENTRIES which can result into a longer execution time.

Plus you are considering only plant level, not sales organization, for instance.

The OP should explain better what he means with "for the company", but it's a simple join, nothing so hard: he just has to check the external keys of Material main tables and he'll be fine.

Re: To fetch data based on materials or company code

mohit_dev — Tue, 24 Sep 2019 12:59:35 GMT

Well Simone Milesi, Not always the requirements are real in nature. I think you did not went through the maintenance view V_T001K_ASSIGN, which is eventually getting data from the tables T001K - T001W etc. Moreover, regarding the IF chunk, which you mentioned above to set the narrative you find it difficult to understand, is mainly because of his question in the first place, i.e. in case he just gives the material on the selection screen, then material should be displayed for all available company codes. and getting data from MARC for either condition will require that clauses.In case you have a good solution, paste your code here rather than downvoting and demotivating the individuals. Cheers.

Re: To fetch data based on materials or company code

SimoneMilesi — Tue, 24 Sep 2019 13:22:26 GMT

No, sorry, I perfectly understand both the FM and the IF, for this reason I'm saying a single JOIN will do the work, perfectly and accessing the DB less times than your solution.
I find odd and redundant to call a FM to read a view without filters and then filter the results after the selection with a loop logically wrong: if in your select option for Company Code I use a partial search it with '*', it will not work at all. Try it and tell me the result 🙂

So, I am sorry if you find my remarks demotivating, I gave all the reasons for them, I exploited an error in your code and added the hints needed to complete such task in a better way, with less access to DB, less code lines, more cleaner way. And working.
Is this demotivating? Again, I'm sorry, I am just trying to help everyone to improve for the little I can.

But I'm not used to spoonfeed anyone: I prefer to give people the tools and then let them to use and try and find the solution because I think it's the best way to improve and, personally, I find it way more satisfactory finding myself the solution.
So, no, i'm sorry, I'll not post my code, but if Akshay needs more hints I'll be happy to help and if you want the code to compare, sure, add me here and i'll send the 20 lines via PM.
If i can suggest, try to change your solution to what I'm suggesting and do a performance comparison and you'll see the results.

Re: To fetch data based on materials or company code

FredericGirod — Tue, 24 Sep 2019 13:29:34 GMT

Development is a tipical kind of job where everybody learn something from someone (and a lot of time from begginer).

Mohit: Make a little test of your code with the value E EQ (something) in your company code select-option for example.

(Exclude Equal (value from your system) )

Simone: Several SELECT does not means several access to the database, especialy with the customizing table. (I do not say, I am agree with the code)

Re: To fetch data based on materials or company code

SimoneMilesi — Tue, 24 Sep 2019 13:33:33 GMT

frdric.girod but for all entries yes since it works in chunks 🙂

Re: To fetch data based on materials or company code

mohit_dev — Tue, 24 Sep 2019 13:36:13 GMT

Thanks Frederic Girod, I agree with you that we do learn something from someone.

Regarding the code, I will check again. No offense.

Regarding the several SELECT statement of yours, I humbly request you to please see my code again. Those selects are in some condition and only one of them actually executes, based on some condition.

Re: To fetch data based on materials or company code

SimoneMilesi — Tue, 24 Sep 2019 13:43:12 GMT

It's false, sorry, the FM VIEW_GET_DATA access the db with a select otherwise it wouldn't able to extract the values, right?
Why did you use

 LOOP AT s_bukrs.
    READ TABLE lt_view INTO ls_view WITH KEY bukrs = s_bukrs-low BINARY SEARCH.
    IF sy-subrc = 0.
      lv_index = sy-tabix.
      LOOP AT lt_view INTO ls_view FROM lv_index.
        IF ls_view-bukrs <> s_bukrs-low.
          EXIT.
        ELSE.
          APPEND ls_view TO lt_view_tmp.
        ENDIF.
      ENDLOOP.
    ENDIF.
  ENDLOOP.
ENDIF.

Instead of a simple

DELETE lt_view WHERE bukrs NOT IN s_bukrs.

if you really really really want to use that FM?

Which is the most readable option between the 2 in your opinion?

Re: To fetch data based on materials or company code

FredericGirod — Tue, 24 Sep 2019 13:43:23 GMT

the comment about the value N EQ ... is regarding these lines:

LOOPAT s_bukrs.

READ TABLE lt_view INTO ls_view WITHKEY bukrs = s_bukrs-low BINARYSEARCH.

You assume the user will only specify a list of expected values, but he could specify nothing, or everything except ... or between value A & value B.

making a big inner join is not beautiful, but avoid playing with table & for all entries (always very dangerous & time consuming)

The remark concerning SELECT statement was not for you 🙂

but, if you want to check it, just make a sql trace on your code (ST05), not sure you will see all the select

Re: To fetch data based on materials or company code

SimoneMilesi — Tue, 24 Sep 2019 13:46:56 GMT

Since we are speaking about improving, I take this a step further: with your solution, you have to maintain 3 selects if in the future you need to add or remove a field, with the risk to forget a piece here and there. Is it a good design?

Yes, it's quite possible this isn't a real requirement, but as I said, we are here to improve (I'm the first one opening questions when I do not know something), so let's think as this is productive code, so we do not fall in the bad habits 🙂

Re: To fetch data based on materials or company code

FredericGirod — Tue, 24 Sep 2019 13:50:54 GMT

Akshay the tables are

field BUKRS --> table T001K --> field BWKEY --> table T001W --> field WERKS --> table MARC --> field MATNR

but it is a point of view. because material is not directly linked to company code.

you need to choose by witch organiazational data you want to make the link plant, com. org ....

(and please, don't remove answer, the objective is to help requestor, not to have the perfect answer)

Re: To fetch data based on materials or company code

Former Member — Fri, 27 Sep 2019 02:20:05 GMT

How can I write a code for this ?

Re: To fetch data based on materials or company code

Former Member — Fri, 27 Sep 2019 02:20:26 GMT

How can I write a code for this ?

Re: To fetch data based on materials or company code

Nag_05 — Fri, 27 Sep 2019 03:46:23 GMT

User Inner joins on tables mentioned, single SELECT will not work

Re: To fetch data based on materials or company code

former_member634045 — Wed, 09 Oct 2019 08:16:15 GMT

Hi Akshay, You can use this code below;

TABLES: marc, t001k.
TYPES: 
BEGIN OF tt_bukrs,
  bukrs TYPE t001k-bukrs,
  END OF tt_bukrs,
BEGIN OF tt_matnr,
    matnr TYPE marc-matnr,
END OF tt_matnr.

DATA: lt_burks TYPE STANDARD TABLE OF tt_bukrs,
      lt_matnr TYPE STANDARD TABLE OF tt_matnr.


SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (10) text-002 FOR FIELD p_mat.
SELECT-OPTIONS: p_mat FOR marc-matnr NO INTERVALS.
SELECTION-SCREEN COMMENT 50(15) text-003 FOR FIELD p_code.
SELECT-OPTIONS: p_code FOR t001k-bukrs NO INTERVALS.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b1.


START-OF-SELECTION.
IF p_matnr IS NOT INITIAL AND p_code IS INITIAL.
SELECT DISTINCT a~bukrs
      INTO TABLE lt_bukrs
      FROM t001k AS a
      INNER JOIN t001w AS b ON a~bwkey = b~bwkey
      INNER JOIN matnr AS c ON b~werks = c~werks
      WHERE c~matnr IN p_matnr.
ELSEIF p_code-low IS NOT INITIAL AND p_mat-low IS INITIAL.
SELECT a~matnr
      INTO TABLE lt_matnr
      FROM marc AS a
      INNER JOIN t001w AS b ON a~werks = b~werks
      INNER JOIN t001k AScON b~bwkey = c~bwkey
      WHERE c~bukrs IN p_code.
ELSEIF p_mat-low IS NOT INITIAL AND p_code-low IS NOT INITIAL.
"print both code and material into a table
ENDIF.

Re: To fetch data based on materials or company code

FredericGirod — Wed, 09 Oct 2019 08:22:28 GMT

You loose the advantage of using SELECT-OPTIONS instead of PARAMETER by trying to read LOW. It is really dangerous, take the example of the value E EQ 12345678 for material, that means I want all the material but not the 12346578, what will be the result of your code ?

SELECT-OPTIONS --> IN operator so only one SELECT statement

Re: To fetch data based on materials or company code

former_member634045 — Wed, 09 Oct 2019 08:29:16 GMT

Hi Frederic,

Can you please elaborate your question? It would be more helpful for me to answer your question.

Re: To fetch data based on materials or company code

former_member634045 — Wed, 09 Oct 2019 10:04:46 GMT

frdric.girod As I understood if I write E EQ 12345678 then why it will fetch all the materials?

In select option statement we pass single or multiple value of a table. If I pass single value like 12345678 then it will fetch only particular value of this 12345678. Please point it out if my understanding is wrong.