Selection Creiterial for a selection query

[Former Member]

Hi all,

i have 7 parameters on my selection screen, my requirement is i need to fetch the records based on only fields for which the values are provided,only 2 are mandatory fields out of 7.

I have wrieen following code but it is giving a dump ' SAPSQL_WHERE_PARENTHESES' .

  DATA : wa_options    TYPE rfc_db_opt,
         it_options  TYPE STANDARD TABLE OF  rfc_db_opt,
         lv_weight(20) type c.

  CONSTANTS :c_and(3) type c value 'AND',
             c_eq(2) type c value 'EQ',
             c_sy(2) type c value '<='.

  CLEAR : wa_options.
  FREE  : it_options.

  CONCATENATE 'FROM_POSTAL_CODE' c_sy p_shipto into wa_options-text SEPARATED BY space.
  APPEND wa_options to it_options.
  clear wa_options.

  CONCATENATE 'TO_POSTAL_CODE >=' p_shipto into wa_options-text SEPARATED BY space.
  APPEND wa_options to it_options.
  clear wa_options.

 lv_weight = p_weight.
  CONDENSE lv_weight.
  CONCATENATE 'WEIGHT_LIMIT >='  lv_weight into wa_options-text SEPARATED BY space.
   APPEND wa_options to it_options.
    clear wa_options.

  if p_plant is not INITIAL.
   CONCATENATE 'PLANT' c_eq p_plant c_and into wa_options-text SEPARATED BY space.
   APPEND wa_options to it_options.
    clear wa_options.
  endif.

  if p_shipc is not INITIAL.
   CONCATENATE 'SHIPING_COND' c_eq p_shipc c_and into wa_options-text SEPARATED BY space.
   APPEND wa_options to it_options.
    clear wa_options.
  endif.

  if p_carri is not INITIAL.
   CONCATENATE 'CARRIER_CODE' c_eq p_carri c_and into wa_options-text SEPARATED BY space.
   APPEND wa_options to it_options.
    clear wa_options.
  endif.

   if p_samed  is not INITIAL.
   CONCATENATE 'SAME_DAY_FLAG' c_eq 'N' into wa_options-text SEPARATED BY space.
   APPEND wa_options to it_options.
    clear wa_options.
  endif.



  select * from ZOTC_FREIGHT_AUS into table i_final
    where (it_options).

Thanks & Regards,

Anjana Rao

Edited by: Matt on Jul 8, 2010 1:52 PM - Added tags for formatting

[Former Member]

Hi,

uses the ranges for your 7 inputs and then write a select query like below

RANGES are  r_xblnr,r_refnr etc.....
      SELECT *
        FROM zfix_t03
        INTO TABLE i_zfix_t03_sub
         FOR ALL ENTRIES IN  i_zfix_t02
       WHERE refnr   EQ i_zfix_t02-refnr
         AND xblnr   IN r_xblnr
         AND refnr   IN r_refnr
         AND budat   IN r_date
         AND bstnr   IN r_po_no
         AND mmbelnr IN r_belnr
         AND mmbelnr IN r_belnr_1

In this code if any of the field is empty, it will not consider the field in the SELECT query.....

Thanks and Regards,

Senthil Kumar Anantham

[Former Member]

Are they parameters? Doesn't sound like it...sounds like they're select options. If so, read ABAP Help on select options. What you're doing is completely unnecessary for select-options....SAP handles blank select-options automatically, returning all data rows when the select-option (ranges) table has not been populated, filtering rows only when it is populated.

[Former Member]

Hi,

Use AND in the select as shown below

CONCATENATE 'FROM_POSTAL_CODE' c_sy p_shipto ' AND' into wa_options-text SEPARATED BY space.

APPEND wa_options to it_options.

clear wa_options.

CONCATENATE 'TO_POSTAL_CODE >=' p_shipto into wa_options-text SEPARATED BY space.

APPEND wa_options to it_options.

clear wa_options.

[Former Member]

They are parameters.

Thanks & Regards,

Anjana Rao

Edited by: anju_rao on Jul 8, 2010 1:38 PM

[Former Member]

Yes but ur selection will be like this right

'FROM_POSTAL_CODE' <= p_shipto

and 'TO_POSTAL_CODE >=' p_shipto

and 'WEIGHT_LIMIT >=' lv_weight

and so on

if p_plant is not INITIAL.

and 'PLANT' = p_plant

endif.

if p_shipc is not INITIAL.

and 'SHIPING_COND' = p_shipc

endif.

if p_carri is not INITIAL.

and 'CARRIER_CODE' = p_carri

endif.

if p_samed is not INITIAL.

and 'SAME_DAY_FLAG' = 'N'

endif.

so i think think tere is a AND missing

[Former Member]

Hi,

I gave you the sample program for your requirement using Ranges for PARAMETERS

DATA : i_final TYPE STANDARD TABLE OF rseg.

PARAMETER : p_ebeln TYPE ebeln
          , p_ebelp TYPE ebelp
          , p_belnr TYPE belnr_d
          , p_gjahr TYPE gjahr
          .

RANGES : r_ebeln FOR p_ebeln
       , r_ebelp FOR p_ebelp
       , r_belnr FOR p_belnr
       , r_gjahr FOR p_gjahr
       .

IF p_gjahr IS NOT INITIAL.

  r_gjahr-sign = 'I'.
  r_gjahr-option = 'EQ'.
  r_gjahr-low = p_gjahr.

  APPEND r_gjahr.

ENDIF.

IF p_ebeln IS NOT INITIAL.

  r_ebeln-sign = 'I'.
  r_ebeln-option = 'EQ'.   " If you want <= use LE , >= use GE , similiarly GT, LT,EQ based on your requirement
  r_ebeln-low = p_ebeln.

  APPEND r_ebeln.

ENDIF.

IF p_ebelp IS NOT INITIAL.

  r_ebelp-sign = 'I'.
  r_ebelp-option = 'EQ'.
  r_ebelp-low = p_ebelp.

  APPEND r_ebelp.

ENDIF.

IF p_belnr IS NOT INITIAL.

  r_belnr-sign = 'I'.
  r_belnr-option = 'EQ'.
  r_belnr-low = p_belnr.

  APPEND r_belnr.

ENDIF.

 IF    r_belnr[] is not initial 
OR  r_gjahr[] is not initial 
OR  r_ebeln[] is not initial 
OR  r_ebelp] is not initial .

SELECT * FROM rseg
  INTO TABLE i_final
 WHERE belnr IN r_belnr
   AND gjahr IN r_gjahr
   AND ebeln IN r_ebeln
   AND ebelp IN r_ebelp.

IF sy-subrc EQ 0..

ENDIF.

  ENDIF

In this case if the parameter is empty, it will not consider that in the select query

[matt]

To start with, why not make things easy on yourself and write a test program - replacing the SQL with:

LOOP AT it_options INTO wa_options.
  WRITE: / wa_options.
ENDLOOP.

Then you'll be able to see what the WHERE clause looks like, rather than guessing.

As it stands your WHERE clause looks like this:

FROM_POSTAL_CODE <= A
TO_POSTAL_CODE >= A
WEIGHT_LIMIT >= B
PLANT EQ C AND
SHIPING_COND EQ D AND
CARRIER_CODE EQ E AND
SAME_DAY_FLAG EQ N

Where A,B,C,D,E were the parameter values. For some reason F doesn't appear. Oh, I see. It will have your P_SAMED value.

As you can see, the "AND"s are missing. So change your code to deal with that. However, I prefer this approach when dealing with dynamic WHEREs. (also I've tidied your code, used more meaningful variable names, and made it properly modularised)

DATA :it_where TYPE STANDARD TABLE OF  rfc_db_opt,
      v_weight TYPE c LENGTH 20.

CONSTANTS: c_where1 TYPE rfc_db_opt VALUE 'FROM_POSTAL_CODE LE &',
           c_where2 TYPE rfc_db_opt VALUE 'AND TO_POSTAL_CODE GE &',
           c_where3 TYPE rfc_db_opt VALUE 'AND WEIGHT_LIMIT GE &',
           c_where4 TYPE rfc_db_opt VALUE 'AND PLANT EQ &',
           c_where5 TYPE rfc_db_opt VALUE 'AND SHIPING_COND EQ &',
           c_where6 TYPE rfc_db_opt VALUE 'AND CARRIER_CODE EQ &',
           c_where7 TYPE rfc_db_opt VALUE 'AND SAME_DAY_FLAG EQ &'.

PERFORM build_where  USING c_where1 p_shipto CHANGING it_where.
PERFORM build_where  USING c_where2 p_shipto CHANGING it_where.

v_weight = p_weight.
CONDENSE v_weight.
PERFORM build_where USING c_where3 v_weight CHANGING it_where.

IF p_plant IS NOT INITIAL.
  PERFORM build_where USING c_where4 p_plant CHANGING it_where.
ENDIF.

IF p_shipc IS NOT INITIAL.
  PERFORM build_where USING c_where5 p_shipc CHANGING it_where.
ENDIF.

IF p_carri IS NOT INITIAL.
  PERFORM build_where USING c_where6 p_carri CHANGING it_where.
ENDIF.

IF p_samed IS NOT INITIAL.
  PERFORM build_where USING c_where7 p_samed CHANGING it_where.
ENDIF.

SELECT * FROM zotc_freight_aus INTO TABLE i_final
    WHERE (it_options).

*&---------------------------------------------------------------------*
*&      Form  build_where
*&---------------------------------------------------------------------*
FORM build_where    USING i_where_line TYPE rfc_db_opt
                          i_var        TYPE clike
                 CHANGING xt_where   TYPE STANDARD TABLE.

  DATA: lw_where TYPE rfc_db_opt.

  lw_where = i_where_line.
  REPLACE ALL OCCURRENCES OF '&' IN lw_where WITH i_var.
  APPEND lw_where TO xt_where.

ENDFORM.                    "build_where

[Former Member]

Hi matt,

Thanks soo much for modularising code,But even now sy-subrc of the query is giving me an 4..

Thanks & Regards,

Anjna Rao

[Former Member]

Thanks madukar and matt for your help...my problem is solved.

i changed the parameters to select options as breakpoint:-) has mentioned and made the change in the query it works,insted of writnig the whole big code of appending and all.

SELECT-OPTIONS: p_plant FOR ZOTC_FREIGHT_AUS-plant NO INTERVALS

NO-EXTENSION .

*PARAMETERS p_plant TYPE ZZWERKS_D.

SELECT-OPTIONS: p_shipc FOR ZOTC_FREIGHT_AUS-SHIPING_COND NO INTERVALS

NO-EXTENSION .

*PARAMETERS p_shipc TYPE VSBED.

SELECT-OPTIONS: p_carri FOR ZOTC_FREIGHT_AUS-CARRIER_CODE NO INTERVALS

NO-EXTENSION .

*PARAMETERS p_carri TYPE ZZLIFNR .

SELECT-OPTIONS: p_samed FOR ZOTC_FREIGHT_AUS-SAME_DAY_FLAG NO INTERVALS

NO-EXTENSION .

"no-extension necessary to avoid multiple selection.

select * from ZOTC_FREIGHT_AUS into table i_final

where FROM_POSTAL_CODE <= p_shipto and TO_POSTAL_CODE >= p_shipto and

WEIGHT_LIMIT >= p_weight and PLANT in p_plant and

SHIPING_COND in p_shipc and

CARRIER_CODE in p_carri and

SAME_DAY_FLAG in p_samed .

Thanks alll

[matt]

Now mark your question as answered...

[Former Member]

Thanks