topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 13 Aug 2009 11:53:33 GMT

Please use meaningfull subject titles in future

Basically Iu2019m into BW but currently Iu2019ve to write a report:

Iu2019ve a report in which a field CUSTOMER is defined as follows.

Tables: /BIO/PCUST_SALES, /BIO/HCUST_SALES.

SELECT-OPTIONS: S_CUST for /BIO/PCUST_SALES-cust_sales.

Select nodename datefrom dateto into table h_customer where nodename in S_CUST. can be written to fetch validity dates from /BIO/HCUST_SALES.

But my issue is S_CUST is normal 8 digit sold_toparty where as nodename is concatenation of division distr_chan salesorg and sol_toparty.

EG: S_CUST(Cust_sales) value is 03600789, where as nodename is in the format 0101201003600789 (Where the first 01 is for division, 2nd 01 for distr_chan, 2010 for salesorg n the cust_sales)

Can some please suggest how I can get the validity dates from database table /BIO/HCUST_SALES in this situation.

Regards

Madavi

Edited by: Julius Bussche on Aug 13, 2009 4:07 PM

Re: select query

Former Member — Thu, 13 Aug 2009 11:59:32 GMT

Hi,

One way is to write the select using wild card entries like this,



SELECT-OPTIONS: S_CUST for /BIO/PCUST_SALES-cust_sales.

concatenate '%' s_cust-low into s_cust-low.

Select nodename datefrom dateto 
into table h_customer 
where nodename like S_CUST-low.

Regards,

Vik

Re: select query

Former Member — Thu, 13 Aug 2009 12:02:18 GMT

Hi,

You can segregate all these into separate fields as the length is fixed for all of them.

Like for division & D.channel you have only two characters, for sale org. you have 4 characters.

So declare 4 fields for these values as shown below:

data : m_division(2) type c,

m_channel(2) type c,

m_org(4) type c,

m_cust(10) type c.

now you can segragate as

say s_cust is having value as '0101201003600789'.

m_cust = s_cust+8(10).

now it will pick only characters starting after 8th positions.

Regds,

Anil

Re: select query

Former Member — Fri, 14 Aug 2009 10:27:26 GMT

Thanks both,

For the quick turn around..

My exact requirement is :

Iu2019ve 2 tables /bi0/hcust_sales & /bi0/pcust_sales

Table /bi0/Pcust_sales contains fields division, distr_chan, salesorg & sold_to

Table /bi0/hcust_sales contains fields nodename, datefrom & dateto

Nodename = concatenation of division, distr_chan, salesorg & soldto.

My aim is to move the fields from both of these tables(may be inner join concept will work out)

Division, distr_chan, sales_org, sold_to from /bi0/pcust_sales and dateto datefrom from table /bi0/hcust_sales for all the (sold_to)values entered by the user in the selection screen.

Can any now now please let me know in detail how can I achieved this, as I'm new to ABAP

Regards

Madhavi

Re: select query

former_member404244 — Fri, 14 Aug 2009 10:44:16 GMT

HI,

U cannot use join as there was no common field in both the tables...try this way

types : begin of ty_tab,

division type <give the dataelement>,

distr_chan type <give the dataelement>,

salesorg type <give the dataelement>,

sold_to type <give the dataelement>,

nodname like /bi0/hcust_sales-nodname,

end of ty_tab.

data : itab type table of ty_tab,

itab1 type table of /bi0/hcust_sales .

select division distr_chan salesorg sold_to from /bi0/Pcust_sales into corresponding field of table itab

where division in s_division (selection-screen field)

and distr_chan in s_distr_chan

and salesorg in s_salesorg

and sold_to in s_sold_to.

if sy-subrc eq 0.

LOOP at ITAB.

CONCATENATE ITAB-DIVISION ITAB-DISTR_CHAN ITAB-SALESORG ITAB-SOLD_TO INTO ITAB-NODENAME.

MODIFY ITAB.

ENDLOOP.

ENDIF.

now ur itab has the nodename values

if not ITAB[] IS INITIAL.

select nodename datefrom dateto from Table /bi0/hcust_sales into table itab1

for all entries in ITAB

where Nodename = ITAB-NODENAME

and datefrom = s_date_from

and dateto = s_date_to.

endif.

Now ITAB1 will have the required data.

Regards,

Nagaraj

Re: select query

Former Member — Fri, 14 Aug 2009 10:51:55 GMT

Hi Madhavi,

as you have said that you need to fetch data form tables /bi0/hcust_sales and /bi0/pcust_sales.

what you can do is.

1. create an internal table (first_itab) with fields Division, distr_chan, sales_org, sold_to and then fetch all these data form table /bi0/pcust_sales.

2. Create second internal table (sec_itab) with fields sold_to and nodename.

Put a loop on the first internal table (first itab) and then append second internal table (sec_itab) using below logic.

LOOP AT first itab.
    sec_itab-sold_to = first_itab-sold_to.

     CONCATENATE first_itab-division,
                               first_itab-distr_chan, 
                               first_itab-salesorg,
                               first_itab-soldto
                     INTO  sec_itab-nodename.
     APPEND sec_itab. 
   ENDLOOP.

Now you have got the relevant nodename for sold_to values.

3. Create another internal table with fields nodename, dateto and datefrom.

and fetch all the data from /bi0/hcust_sales for all the entries in sec_itab.

Hope this will sort out your problem.

Regards,

Raj Gupta

Re: select query

Former Member — Fri, 14 Aug 2009 11:54:11 GMT

Hi thanks both for your valuable suggestions,

Getting data from my first table is very easy:

select division distr_chan salesorg cust_sales into corresponding fields of table itab1

from /bi0/pcust_sales where cust_sales in s_cust.

if sy-subrc eq 0.

LOOP at ITAB.

CONCATENATE ITAB-DIVISION ITAB-DISTR_CHAN ITAB-SALESORG ITAB-SOLD_TO INTO ITAB-NODENAME.

MODIFY ITAB.

ENDLOOP.

ENDIF.

if not ITAB[] IS INITIAL.

select nodename datefrom dateto from Table /bi0/hcust_sales into table itab2

for all entries in ITAB1

where Nodename = ITAB1-NODENAME.

endif.

Now table Itab1 has fields division distr_chan salesorg cust_sales and

Itab2 has nodename datefrom dateto and now I would like to move few fileds from these 2 tables in one more internal table as follows:-

Select a_{division a}distr_chan a_{salesorg a}cust_sales

B_{datefrom B}dateto into table itab3.

Please correct me with this third internal table itab3u2026

Regards

Madhavi

Re: select query

former_member404244 — Fri, 14 Aug 2009 12:08:40 GMT

HI,

In this case u can use the below offset values .... in R/3 the distribution channel has 2 chars, divison 2 cahrs. sales org 4 chars,

customer 10 chars.

LOOP at ITAB2.

ITAB3-DIVISION = ITAB2+0(2).

ITAB3-DISTR_CHAN = ITAB2+2(2).

ITAB3-SALESORG = ITAB2+4(4).

ITAB3-SOLD_TO = ITAB2+8(10).

ITAB3-DATEFROM = ITAB2-DATEFROM

ITAB3-DATETO = ITAB2-DATETO.

APPEND ITAB3.

ENDLOOP.

Now u will have all the values..

Regards,

Nagaraj

Re: select query

Former Member — Fri, 14 Aug 2009 15:15:12 GMT

Hi,

Write Code like this:

parameters: p_VTWEG type VTWEG obligatory,

p_SPART type SPART obligatory,

p_vkorg type vkorg obligatory.

Tables: /BIO/PCUST_SALES, /BIO/HCUST_SALES.

SELECT-OPTIONS: S_CUST for /BIO/PCUST_SALES-cust_sales obligatory.

data: begin of wa_node,

nodename type nodename " use proper data element here

end of wa_node,

t_nodes like standard table of wa_node.

at selection-screen.

if if s_cust[] is initial.

else.

" write select query here first to get custmers into one internal table Ex: t_custmer, for S_CUST.

refresh: t_nodes.

loop at custmer into w_custmer.

wa_node-nodename+0(2) = p_SPART.

wa_node-nodename+2(2) = p_VTWEG.

wa_node-nodename+4(4) = p_vkorg.

wa_node-nodename+8 = w_custmer-cust.

append wa_node to t_nodes.

endloop.

Select nodename datefrom dateto into table h_customer

for all entries in t_nodes

where nodename - t_nodes-nodename.

endif.

Regards,

Balaji Peethani.

Re: select query

Former Member — Mon, 17 Aug 2009 06:05:08 GMT

Hi Nagaraj,

Thank you..

I felt this is a very good option to move data from both tables ITAB1 & ITAB2 into ITAB3 but unfortunately Iu2019ve few more Z fields in ITAB1 which are not available in ITAB2,so in this step if I loop at ITAB2 and fetch data for all fields Iu2019m unable to fetch data for those z fieldsu2026

Please suggest if there is any other option to move all these fields into ITAB 3 from ITAB1 & Itab2.

Regards

Madhavi

Re: select query

former_member404244 — Mon, 17 Aug 2009 06:31:39 GMT

HI,

U can use Read statement for ITAB1 inside ITAB2....as u have nodename as common in both the internal tables..

Loop at itab2.

read table itab1 with key nodename = itab2-nodename.

if sy-subrc eq 0.

Now move ur itab1-zfields to itab3.

endif.

Endloop.

Regards,

Nagaraj

Re: select query

Former Member — Tue, 18 Aug 2009 06:18:03 GMT

Thanks 4 all ur support,

My issue is now resolved but time consuming so have to check vth the performance...

Thanks

Madhavi