topic Re: Select statement question in Application Development and Automation Discussions

Select statement question

Former Member — Tue, 24 Oct 2006 22:20:39 GMT

Hi,

I have 5 select options on the screen. I want to build a select statment on a z table. user may enter values in atleast one select option. the problem is if I use and in where clause, it is resulting no rows. if I use 'or' for all it returns all rows in the table. suppose user enter a vendor #, then only rows related to that vendor need to be retrieved.

Your help is appriciated.

thanks

surya.

Re: Select statement question

Former Member — Tue, 24 Oct 2006 22:27:05 GMT

Hi,

For select-options if you use IN it will automatically filter the records..

EXAMPLE.

TABLES: MARA.

DATA: ITAB LIKE MARA OCCURS 0 WITH HEADER LINE.

SELECT-OPTIONS: SO_MATNR FOR MARA-MATNR.

SELECT-OPTIONS: SO_ERSDA FOR MARA-ERSDA.

AT SELECTION-SCREEN.

IF SO_MATNR[] IS INITIAL.

MESSAGE E000.

ENDIF.

START-OF-SELECTION.

SELECT * FROM MARA

INTO TABLE ITAB

WHERE MATNR IN SO_MATNR

AND ERSDA IN SO_ERSDA.

LOOP AT ITAB.

WRITE: / ITAB-MATNR.

ENDLOOP.

Thanks,

Naren

Re: Select statement question

Former Member — Wed, 25 Oct 2006 02:35:56 GMT

Hi Surya

Check the below code to get some idea.

  tables: ztab.
  select-options: p_fld1 for ztab-fld1,
                  p_fld2 for ztab-fld2,
                  p_fld3 for ztab-fld3,
                  p_fld4 for ztab-fld4,
                  p_fld5 for ztab-fld5.
  .....
  start-of-selection.

    select <fields> into table itab
           from ztab
           where fld1 in p_fld1
           and   fld2 in p_fld2
           and   fld3 in p_fld3
           and   fld4 in p_fld4
           and   fld5 in p_fld5.

   .....

Kind Regards

Eswar

Re: Select statement question

seshatalpasai_madala — Wed, 25 Oct 2006 04:56:31 GMT

Hi,

For your case best thing is to use a CHAR type variable to pass the where clause string. That is you can build a string which contains your where clause and the pass it to the select statement.

Ex.

DATA: where_string type stirng.

if sel_opt1 is not initial.

concatenate where_string 'filed1 IN sel_opt1' to where_string separated by space.

endif.

go on building this string and then call. Do not forget to use AND or OR when you are building this string.

select * from table where ( where_string ).

Regards,

Sesh

Message was edited by: Seshatalpasai Madala

Re: Select statement question

Former Member — Wed, 25 Oct 2006 14:33:18 GMT

Naren/ Eswar / Madala,

thanks for your help.

This is working fine. but failing due to the difference of internal and external formats of vendor # and po #. I tried to use CONVERSION_EXIT_ALPHA_RANGE_I/O'. I can not able to get the desired results. basically I want to convert ranges to internal / external format.

your help is appriciated.

thanks,

surya.