topic Re: Select Query in Application Development and Automation Discussions

Select Query

Former Member — Fri, 20 Jun 2008 21:17:01 GMT

Hi All,

i have three columns matnr, color and size.

My data may come as:

10000120, 030, 100 (or)

10000120, 030, null (or)

10000120.

In this scenario I have to get all the possible values.

a) I am currently doing select * from mara where SATNR = matnr and color = wa_datatab-col2 (initially I got the requirement with two columns only). Now here my requirement is changed with another column size = wa_datatab-col3. How do I write the query for the three columns.

b) I think if we add the third value to the query i will get all the values which has the three conditions. Apart from that if I have only matnr and if i dont have color and size, then it should retreive all the articles associated with that matnr color.

c) Also if I have matnr and color it should only retrieve the corresponding sizes, not all the sizes.

Thanks one and all.

Re: Select Query

Former Member — Fri, 20 Jun 2008 21:38:53 GMT

Hi Cheng Wan,

SELECT *
FROM MARA
INTO TABLE IT_MARA
WHERE MATNR = smatnr
  AND COLOR = wa_datatab-col2 
  AND SIZE  = wa_datatab-size.

In this SELECT Query u get the records which statisfies the condition given in WHERE clause.

Output:

MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100

B) If u have only MATNR in WHERE clause and if u dont have COLOR and SIZE then it retrieves all the articles associated to MATNR only.

Output:

MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100
10000120         030               NULL
10000120         020               100
10000120         020               NULL

C) If u have only MATNR and COLOR then it retrieves articles associated to MATERIAL and COLOR. It retrives articles of all SIZES.

Output:

MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100
10000120         030               NULL

Best regards,

raam

Re: Select Query

Former Member — Fri, 20 Jun 2008 22:28:03 GMT

Hi,

If you have MATNR, just pass MATNR to the query. It will retreive all the possible records that matches MATNR, irrespective of the other two columns.

[ OR] if you really want to pass all the values, then convert the other two columns to ranges and pass it in the select query.

Then you can loop or read the table and do your further processing.

Regards,

Subramanian

Edited by: Subramanian PL on Jun 20, 2008 3:28 PM

Re: Select Query

Former Member — Fri, 20 Jun 2008 22:51:17 GMT

Hi Reddy,

I dont want to put in the table. I am already in the looping. So I will have one at a time. Can you please tell me the select statment in the format:

select * from mara where SATNR = matnr and color = wa_datatab-col2 and size1 = wa_datatab-col3.

Also I have this input possiblilities like this:

Input:

MATNR COLOR SIZE1

10000120 030 100

10000120 030 101

10000120 020 100

10000120 020 101

Input:

MATNR COLOR SIZE1

10000120 030 Null

10000120 020 Null

Input:

MATNR COLOR SIZE1

10000120 Null Null

So in all the cases I would like to have my output as:

Output:

10000120 030 100

10000120 030 101

10000120 020 100

10000120 020 101

Let me please know if you guys want to know anything from my end.

Re: Select Query

Former Member — Fri, 20 Jun 2008 22:59:32 GMT

Hi,

ranges: r_col2 like wa_datatab-col2,

r_col3 like wa_datatab-col3.

r_col2-sign = 'I'.

r_col2-option = 'EQ'.

r_col2-low = wa_datatab-col2 .

append r_col2.

r_col3-sign = 'I'.

r_col3-option = 'EQ'.

r_col3-low = wa_datatab-col3 .

append r_col3.

select * from mara where SATNR = matnr

and color in r_col2

and size1 in r_col3.

This should fetch you all combinations.

Regards,

Subramanian

Re: Select Query

Former Member — Sat, 21 Jun 2008 01:48:24 GMT

Hi Cheng Wan,

SELECT SINGLE *
FROM MARA
INTO WA_MARA
WHERE SATNR = matnr
  AND COLOR = wa_datatab-col2 
  AND SIZE  = wa_datatab-col3.

In this SELECT Query u get the records which statisfies the condition given in WHERE clause.

Output:
 
MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100
10000120         030               101
10000120         020               100
10000120         020               101

B) If u have only MATNR in WHERE clause and if u dont have COLOR and SIZE then it retrieves all the articles associated to MATNR only.

Output:
 
MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100
10000120         030               101
10000120         020               100
10000120         020               101

C) If u have only MATNR and COLOR then it retrieves articles associated to MATERIAL and COLOR. It retrives articles of all SIZES.

Output:
 
MATNR           COLOR              SIZE
----------------------------------------
10000120         030               100
10000120         030               101
10000120         020               100
10000120         020               101

Best regards,

raam