select with multiple AND condition

[Former Member]

How do i use a select query with multiple and conditions. None of the parameters are optional. If one of the parameters is empty, the query fetches no records.

[Former Member]

Hi...

if all are mandatory...then it will stop u at the screen ..if a field left blank..

so ...no prob with that..

select * from <dbtab>

into table <itab>

where <f1> = 'xyz'

and <f2> = 'abc'

and <f3> = '123'.

[Former Member]

sorry....dere was a small mistake all the parameters are optional.

How do i use a select query with multiple and conditions. None of the parameters are mandatory. If one of the parameters is empty, the query fetches no records.

[Former Member]

Hi...

if your parameter is empty..it will not fetch any record from dbase..untill and unless u use select-options..so...you can use the above sent query...

if you want to use ANDs with all the parameters

if u have parameter empty u cannot retreive the data

unti you make select query..without that parameter...ok..then use case stmt..

ram

Message was edited by:

Rammohan Nagam

[Former Member]

hi,

but i cannot use select options in a function module.

[Former Member]

hi hd...

can you tell me thst FM name ...

i'll try..

[Former Member]

i m creating a fm...

[Former Member]

Hi,

See the statement below for multiple and conditions.

SELECT ebeln

lifnr

ekgrp

FROM ekko

INTO TABLE i_ekko

WHERE ebeln IN r_ebeln AND

lifnr IN r_lifnr AND

ekgrp IN r_ekgrp AND

bstyp = 'F'.

Regards,

Ram

[former_member404244]

Hi,

If u r using parameters then they should be mandatory,otherwise the select query will fail.I f u want to fetch by making optional then go for select-options instead of parameters...If u r using parameters then u need to enter the data ....u can't make it empty..

Regards,

Nagaraj

[Former Member]

I am using it in a function module in which these are as import parameters all optional. select query fails incase any field is empty. I cannot use select-options in function module. Is there any other alternative??

[Former Member]

one thing you can do..

if p_f1 ne '' and p_f2 ne ''.

select * from dbtab where f1 = p_f1 and f2 = p_f2.

elseif p_f1 ne '' and p_f2 eq ''.

select * from dbtab where f1 = p_f1 .

elseif p_f1 eq '' and p_f2 ne ''.

select * from dbtab where f2 = p_f2 .

else.

select * from dbtab.

endif.

regards

shiba dutta

[Former Member]

hi there

PARAMETERS P1_WERKS LIKE MSEG-WERKS OBLIGATORY.

SELECT-OPTIONS : S1_LGORT FOR QALS-LAGORTVORG,

S1_MATNR FOR MSEG-MATNR,

S1_BUDAT FOR MKPF-BUDAT OBLIGATORY.

SELECT A_{MBLNR A}BUDAT A~CPUTM

B_{ZEILE B}MENGE B_{GRUND B}LGORT B~BWART

INTO CORRESPONDING FIELDS OF TABLE ITAB_MSEG

FROM MKPF AS A INNER JOIN MSEG AS B

ON A_{MBLNR EQ B}MBLNR

WHERE B~WERKS EQ P1_WERKS AND

A~BUDAT IN S1_BUDAT AND

LGORT IN S1_LGORT AND

B~BWART IN ('321', '350', '122').

this is how we use AND condition in a select query...

>>>If one of the parameters is empty, the query fetches no records.

in the above query P1_WERKS is the parameters...if it is left blank, the select will search the database for a record that satisfies teh select with an empty value for werks (plant), which doesn't exist...so it will not fetch any record...so, it's always good programming practice to make parameters obligatory...

if helpful, reward

Sathish. R

[Former Member]

if you are using parameters then all the parameters should be filled make them obligatory other wise you can not get the result...

select * from dbtab where f1 = p_f1 and f2 = p_f2 and f3 = p_f3...like that

if you dont want to make the parameter obligatory but still want user should enter one value then use

select-options : s_op for mara-matnr no intervals no-extension.

your select query will be like that

select * from dbtab where f1 in s_f1 and f2 in s_f2 and f3 in s_f3

it will fetch the value if any field is blank also.

regards

shiba dutta

[former_member404244]

hi,

is the FM created by you..??if yes then make the import parametrs mandatory.just remove the tick mark for the check box under the optional.if u r using standard one then no need to pass data to it...plz chek the same...

Regards,

nagaraj