topic Re: SELECT querie in Application Development and Automation Discussions

SELECT querie

Former Member — Mon, 14 Nov 2005 14:24:20 GMT

Hi Everybody,

I would like know the select querie to be passed when we have more then one option.

That is i have 5 database table fields displayed on my screen and based on the values passed in any of the five fields, i need to pass the select querie,

I have used AND operator but did not work as it requiries all the five field data ,

I have also tried OR but it takes all the entries in the dbase table.

Thanks in advance,

Aima

Re: SELECT querie

RichHeilman — Mon, 14 Nov 2005 14:25:59 GMT

Please post the code for the select statement and your selection screen. Are you using an inner join?

Regards,

RIch Heilman

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:30:46 GMT

Assuming you have fields 1, 2 ,3 ,4,5 in ur selection screen.

If they are select options,

select fd1 fd2 fd3 fd4 fd5 from mara into itab

where fd1 IN mara-fd1 and

fd2 IN mara-fd2 and

.....

fd5 IN mara-fd5.

If they are Parameters replace the 'IN' in where clause with '='.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:30:48 GMT

hi,

your problem is that you want a different condition in the select statment?

if yes you can use a dynamical condition.

you can use something like this

select * from mara into t_mara

where (tab_condition).

where you have build tab_condition in this way:

tab_condition-line 'matnr = 00000012'.

append tab_condition.

bye

enzo

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:31:12 GMT

Hi Rich,

SELECT * FROM zx INTO TABLE itab_x_302 WHERE pernr EQ

zx-pernr AND orgeh EQ zx-orgeh AND pnr EQ

zpetsy_interflex-pnr AND ksl EQ zx-ksl AND lktl

EQ zx-lktl AND lfr EQ zx-lfr AND sa1 EQ zx-sa1 AND sa2 EQ zx-sa2.

i am fetching only from single table.

Regards.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:31:46 GMT

Hello Amia,

you should use them all as ranges.

ranges: r1 for field1,
        r2 for field2,
        r3 for field3,
        r4 for field4,
        r5 for field5.

if not field1 is initial.
  r1-sign = 'I'.
  r1-option = 'EQ'.
  r1-low = field1.
endif.

if not field2 is initial.
  r2-sign = 'I'.
  r2-option = 'EQ'.
  r2-low = field2.
endif.

and so on...

SELECT * from DBTAB where field1 in r1
                      and field2 in r2
                      and field3 in r3
                      and field4 in r4
                      and field5 in r5.

Regards,

Anand Mandalika.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:33:14 GMT

Hi Aima, I get you clearly then you need that all the time you use where clause for a select are not fixed and it depends on the screen fields populated with value ..right...

In that case you have to use DYNAMIC SELECT WITH DYNAMIC WHERE CLAUSE.

Like as you write the whereh clause , you have to form a string e.g.

ITAB-DATA = 'BUKRS = v_bukrs' .

APPEND ITAB.

YOU WILL FILL THIS STRING ONLY IF V_BUKRS HAS VALUE..ok.

THEN USE SELECT LIKE...

SELECT *

FROM xxx

WHERE (ITAB).

***where this ITAB is the internal table which will hold the dynamic where clause

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:34:15 GMT

Hi Raju,

i am working with screens and as i have pasted the code below i have used AND but does'nt work as i requires all the field data .

if i am using OR then it is selecting all the data.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:34:55 GMT

U are doing good job. My understanding is U are entering table name and fields and operator also. When U are using AND or OR operator U need to check it which operator exiting in filed content and accordingly execute separate block of code. I think it may give an idea to proceed further. If not pls. paste your code and write your objective very clearly then I will reply with code.

Enjoy SAP.

K.R.Reddy

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:35:02 GMT

Hi Aima,

You can use dynamic where clasue.

To specify a condition dynamically, use:

SELECT ... WHERE (<itab>) ...

where <itab> is an internal table with line type C and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written into the lines of <itab>. However, you may only use literals, and not the names of data objects. The internal table can also be left empty.

If you only want to specify a part of the condition dynamically, use:

SELECT ... WHERE <cond> AND (<itab>) ...

You cannot link a static and a dynamic condition using OR.

You may only use dynamic conditions in the WHERE clause of the SELECT statement.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:35:11 GMT

Aima,

If u have 5 Selection Screen like z1, z2, z3, z4 and z5.

Then the SELECT stmt would like :

SELECT * FROM zx INTO TABLE itab_x_302

WHERE pernr EQ z1

AND orgeh EQ z2

AND pnr EQ z3

AND ksl EQ z4

AND lktl EQ z5

AND lfr EQ z6

AND sa1 EQ z7

AND sa2 EQ z8.

U cannot have the Where Condition Like this!!!!!!

WHERE pernr EQ

zx-pernr AND orgeh EQ zx-orgeh AND pnr EQ

zpetsy_interflex-pnr AND ksl EQ zx-ksl AND lktl

EQ zx-lktl AND lfr EQ zx-lfr AND sa1 EQ zx-sa1 AND sa2 EQ zx-sa2.

Thanks

Kam

Message was edited by: Kam

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:35:23 GMT

SELECT * FROM zx INTO TABLE itab_x_302

WHERE pernr EQ zx-pernr AND

orgeh EQ zx-orgeh AND

pnr EQ zpetsy_interflex-pnr AND

ksl EQ zx-ksl AND

lktl EQ zx-lktl AND

lfr EQ zx-lfr AND

sa1 EQ zx-sa1 AND

sa2 EQ zx-sa2.

Is zpetsy_interflex-pnr a selection screen field ?? If so then everything should work fine. Let us know whetehr all these fields are parameters or select options.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:36:51 GMT

If you want data based on all those fields in the selection screen then you have to use AND only and see below example for joining 5 tasbles.

select vbak_{vbeln vbak}erdat

from vbak

inner join vbap on vbap_{vbeln = vbak}vbeln

inner join vbrk on vbrk_{vbeln = vbrk}reference field

inner join vbrp on vbrp_{vbeln = vbrk}vbeln

inner join mara on mara_{matnr = vbap}matnr

inner join marm on marm_{matnr = vbap}matnr

where vbak~vbeln in s_vbeln and

mata~matnr in s_matnr and

...

,,,,

Cheers,

Satya

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:39:16 GMT

Hi aima,

Like already mentioned use dynamic where conditions.

First check whenther the field empty or not insert to dynmic tab.

if not zx-pernr is initial.

concatnate 'pernr' '=' zx-pernr into

tab_condition-line separated by space.

append tab_condition.

endif.

Here zx-pernr is the name of your screen field.

like that check all the screen fields and populate to tab_condition.

select * from zx into itab

where (tab_condition).

Hope this will help you.

Thanks & Regards,

Siri.

Message was edited by: Srilatha T

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:39:16 GMT

Is zpetsy_interflex-pnr a selection screen field ??

yes it is a selection screen field

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:39:42 GMT

ALso check sy-subrc after ur select statement !! There might be data for your condition . I think ur selct works fine since you say data is fetched for OR condition. I think there is no data for ur AND condition.

Check the table.

Since you have given EQ i assume they are parameters, change them to select options and then chnage the EQ codition to IN condition in the where clause.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:49:18 GMT

Hi Reddy,

Firstly i would like to make it clear that i am not working with Select-options or Parameters.

These fields are taken from dictionary as i am working in se51 getting the fields from dic .

secondly i have more than 1 fields value to pass in select querie taking from zx-sa1 and so on

for example of i wanna get data based on one field

i pass

select * from xtab into itab where fld = scr-fld.

if i have more flds based on which i have to select than wat is my select querie?

hope i am clear with my objective

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 14:55:26 GMT

Hello,

have you tried out the code snippet given by Srilatha? I think that's the best way to do it.

Regards,

Anand Mandalika.

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 15:18:52 GMT

Hi,

i have tried the sol given by srilatha but i find more complex , for example

if not zx-pernr is initial "here i need to add my four fields which are intial.concatnate 'pernr' '=' zx-pernr into

tab_condition-line separated by space.

append tab_condition.

endif.

if work like this then i need check 15 or more conditions IS'nt it ?

if i am wrong the just guide me ....

Re: SELECT querie

Former Member — Mon, 14 Nov 2005 15:23:35 GMT

check this condition for all the screen fields( say u have 5 selection screen fields, then check this condition for those 5 fields along ) and populate to tab_condition.