Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Selection Creiterial for a selection query

Former Member
0 Kudos

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

11 REPLIES 11

former_member182387
Active Participant
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

They are parameters.

Thanks & Regards,

Anjana Rao

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

0 Kudos

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

0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

Now mark your question as answered...

Former Member
0 Kudos

Thanks