topic Re: Reg.write query for report in Application Development and Automation Discussions

Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:32:34 GMT

Dear Experts

I am MM consultant and I am in need of generate reports.I don't know how to get the data from two table. Pls help me

with some codings

Thanks

Rajakumar.K

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:34:13 GMT

Very descriptive Question. Good Job.

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:34:34 GMT

Hi,

The basic syntax that we need to extract the data from database table is :

SELECT field1
             field2...
FROM databasetable 
INTO internaltable                   
WHERE condition1
              condition2.

fields: fields or columns that we want finally to satisfy our need.

internal table : table that is used in query to show the result its a area that store the records selected.

Example :

if wee nedd to select materials from mara table based on material number we can write :

internal table declaartion :

data: begin of itab .

matnr type mara-matnr,

meins type mara-meins,

end of itab.

parameters: p_matnr type mara-matnr. "parameter that user will enter while runtime.

select matnr

meins

....

from mara

into itab " internal table.

where matnr = p_matnr. " p_matnr is the parameter that user supplies.

to display data that we have selected in internal table itab :

loop at itab.

write: itab-matnr, itab-meins.

endloop.

offcourse we can learn it from sdn only, lot of stuff available.

hope this helps.

thanx.

Edited by: Dhanashri Pawar on Aug 28, 2008 6:34 AM

Edited by: Dhanashri Pawar on Aug 28, 2008 6:41 AM

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:35:07 GMT

Loop at that table and use write statement and then endloop.

Ex: data:itab like mara occurs 10 with header line.

select * from mara into table itab.

loop at itab.

write: itab-matnr.

skip.

endloop.

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:35:17 GMT

Hi,

You can use SQVI tcode & create quick view which join two or more table without any coding.

Regards,

Smit

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:35:32 GMT

Dear Sir

You need to start up with scratch say how to write select queries n all.

~hitesh

Re: Reg.write query for report

naveen_inuganti2 — Thu, 28 Aug 2008 04:37:50 GMT

Hi.....

Check this... Here Iam joining MARA and MAKT tables.....

data: begin of itab occurs 0,
      matnr like mara-matnr,             <----like this you can select no.of fields from MARA
      spras like makt-spras,             <----like this you declare ur internal table with no.of fields of MAKT table
      end of itab.     
 
parameters: p_matnr like mara-matnr.      <------ Later according to this material number you can display details
 
select p~matnr q~spras          <----with p~ you can declare no.of from MARA, but those should me mention in above ITAB , also MAKT with q~
         from 
         mara as p                      <---- p is for first table fileds
         inner join                         <----- key word
         makt as q                          <----- q is for second table fields
          on p~matnr = q~matnr       <----this is for link
          into corresponding fields of table itab
          where p~matnr = p_matnr.       <------checking where condition with parameter
 
loop at itab.
write:/ itab-matnr,            <--------here you can display the fields which you defined in ITAB
          itab-spras.
endloop.

Also check this query....

Here you can display all the details of that tables...

data: begin of itab occurs 0,
      matnr like mara-matnr,
      spras like makt-spras,
      end of itab.

data: p_matnr like mara-matnr.
select-options: g_matnr for p_matnr.              <------First change

select p~matnr q~spras from mara as p inner join makt as q
          on p~matnr = q~matnr
          into corresponding fields of table itab
          where p~matnr in g_matnr.                   <----second change

loop at itab.
write:/ itab-matnr,
          itab-spras.
endloop.

Also check this code and get an idea about for all entries method and joining of more than two tables...

data: begin of itab occurs 0,
      matnr like mara-matnr,
      spras like makt-spras,
      end of itab.

data: begin of jtab occurs 0,
      matnr like marc-matnr,
      werks like marc-werks,
      end of jtab.

data: begin of t_out occurs 0,
      matnr like mara-matnr,
      spras like makt-spras,
      werks like marc-werks,
      end of t_out.

data: p_matnr like mara-matnr.
select-options: g_matnr for p_matnr.

select p~matnr q~spras from mara as p inner join makt as q
          on p~matnr = q~matnr
          into corresponding fields of table itab
          where p~matnr in g_matnr.

if itab[] is not initial.
  select * from marc into corresponding fields of table jtab
            for all entries in itab
            where matnr = itab-matnr.
endif.

loop at itab.
  t_out-matnr = itab-matnr.
  t_out-spras = itab-spras.

  read table jtab with key matnr = itab-matnr.
  if sy-subrc = 0.
    t_out-werks = jtab-werks.
  endif.

  append t_out.
  clear t_out.
endloop.

loop at t_out.
  write:/ t_out-matnr,
          t_out-spras,
          t_out-werks.
endloop.

All the best!!

Thanks,

Naveen.I

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 04:41:10 GMT

Hi,

Use inner join between those tables. If those two tables have atleast one key in common then u can join them .

for example,

SELECT a~PERNR

a~SUBTYP

b~burks

b~kostl

FROM PA0000

INNER JOIN PA0001

ON a_{PERNR EQ b}PERNR

INTO corresponding fields of table itab

WHERE PERNR = s_gin.

Regards,

Kusuma.

Edited by: kusuma kurapati on Aug 28, 2008 6:42 AM

Re: Reg.write query for report

Former Member — Thu, 28 Aug 2008 06:06:58 GMT

All of you Thanks a lot.