Solved: Using a wild Card (*) in select statement

Piyushmatrixs · ‎2013 Sep 20

Hi,

I want to use wild card character (*) in select statement (in where clause). Is it possible? I used SELECT-OPTIONS S_VBELN FOR LIKP-VBELN.

I want to use S_VBELN in where clause of SELECT statement. But before SELECT query I want to check the value entered in S_VBELN to pass for where clause.

Conditions for S_VBELN (multiple entry)

a). if value entered only in S_VBELN-Low

then what would be passed in where clause to get data only regarding S_VBELN-LOW entered?

b). if value entered in both S_VBELN-LOW adn S_VBELN-HIGH

then what would be passed in where clause to get data regarding S_VBELN entered between LOW and HIGH including?

c). if value not entered in S_VBELN

then what would be passed in where clause for all entry? should I use '*' in where clause for S_VBELN?

I want to do all above 3 points before executing SELECT statement to pass in where clause for S_VBELN.

How to manage all in one select query.

This is my code of Report.

When I pass value in S_VBELN-Low only, then it is not working correctly, it fetch all data including and excluding value entered in S_VBELN-LOW.

Thanks for your help in advance, please help as soon as possible.

*&---------------------------------------------------------------------*

REPORT ZSD_DELIVERIES.

TABLES: LIKP, LIPS, MSEG.

TYPE-POOLS: SLIS.

* DATA DECLARATION FOR ALV REPORT

DATA : IT_LAYOUT TYPE SLIS_LAYOUT_ALV,

IT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV,

WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV,

IT_EVENTEXIT TYPE SLIS_T_EVENT_EXIT,

WA_EVENTEXIT TYPE SLIS_EVENT_EXIT.

* DECLARATION OF INTERNAL TABLE FOR FINAL DISPLAY.

DATA: BEGIN OF IT_DISPLAY OCCURS 0,

VBELN LIKE LIKP-VBELN, " DELIVERY NUMBER

POSNR LIKE LIPS-POSNR, " ITEM NUMBER

MATNR LIKE LIPS-MATNR, " MATERIAL NUMBER

ARKTX LIKE LIPS-ARKTX, " DESCRIPTION

LGMNG LIKE LIPS-LGMNG, " QUANTITY

END OF IT_DISPLAY,

IT_DISPLAY_TMP LIKE IT_DISPLAY OCCURS 0 WITH HEADER LINE,

IT_DISPLAY_TMP1 LIKE IT_DISPLAY OCCURS 0 WITH HEADER LINE.

SELECTION-SCREEN BEGIN OF BLOCK 1.

PARAMETERS P_VKORG LIKE LIKP-VKORG OBLIGATORY.

SELECT-OPTIONS: S_WERKS FOR LIKP-WERKS OBLIGATORY.

PARAMETERS P_LFART LIKE LIKP-LFART OBLIGATORY.

SELECT-OPTIONS: S_KUNNR FOR LIKP-KUNNR,

KUNNR_N FOR LIKP-KUNNR NO-DISPLAY,

S_VBELN FOR LIKP-VBELN,

VBELN_N FOR LIKP-VBELN NO-DISPLAY,

S_WADAT FOR LIKP-WADAT_IST OBLIGATORY.

SELECTION-SCREEN END OF BLOCK 1.

* GET DATA BY SALES ORGANISATION (P_VKORG)

PERFORM GET_DATA.

END-OF-SELECTION.

*CREATION OF ALV FIELD CATALOG

PERFORM BUILD_FIELDCAT.

* LAYOUT FOR ALV

PERFORM BUILD_LAYOUT.

* ALV OUTPUT

PERFORM ALV_OUTPUT.

*&---------------------------------------------------------------------*

*& Form GET_DATA

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* --> p1 text

* <-- p2 text

*----------------------------------------------------------------------*

FORM GET_DATA .

IF NOT S_KUNNR IS INITIAL.

KUNNR_N = S_KUNNR.

ELSE.

KUNNR_N = '*' .

ENDIF.

IF NOT S_VBELN IS INITIAL.

VBELN_N = S_VBELN.

ELSE.

VBELN_N = '*' .

ENDIF.

CLEAR: IT_DISPLAY_TMP1, IT_DISPLAY_TMP1[].

* FETCHING DATA FROM TABLES LIKP AND LIPS BY JOINING THEM

SELECT K~VBELN P~POSNR P~MATNR P~ARKTX P~LGMNG INTO CORRESPONDING FIELDS OF TABLE IT_DISPLAY_TMP1

FROM ( LIKP AS K INNER JOIN LIPS AS P ON K~VBELN = P~VBELN )

WHERE K~VKORG = P_VKORG

AND K~WERKS IN S_WERKS

AND K~LFART = P_LFART

AND K~KUNNR IN KUNNR_N

AND K~VBELN IN VBELN_N

AND K~WADAT_IST IN S_WADAT.

IF NOT IT_DISPLAY_TMP1[] IS INITIAL.

CLEAR IT_DISPLAY_TMP[].

LOOP AT IT_DISPLAY_TMP1.

CLEAR: IT_DISPLAY_TMP.

IT_DISPLAY_TMP-VBELN = IT_DISPLAY_TMP1-VBELN.

IT_DISPLAY_TMP-POSNR = IT_DISPLAY_TMP1-POSNR.

IT_DISPLAY_TMP-MATNR = IT_DISPLAY_TMP1-MATNR.

IT_DISPLAY_TMP-ARKTX = IT_DISPLAY_TMP1-ARKTX.

IT_DISPLAY_TMP-LGMNG = IT_DISPLAY_TMP1-LGMNG.

APPEND IT_DISPLAY_TMP.

ENDLOOP.

ENDIF.

IF NOT IT_DISPLAY_TMP[] IS INITIAL.

LOOP AT IT_DISPLAY_TMP.

CLEAR IT_DISPLAY.

IT_DISPLAY-VBELN = IT_DISPLAY_TMP-VBELN.

IT_DISPLAY-POSNR = IT_DISPLAY_TMP-POSNR.

IT_DISPLAY-MATNR = IT_DISPLAY_TMP-MATNR.

IT_DISPLAY-ARKTX = IT_DISPLAY_TMP-ARKTX.

IT_DISPLAY-LGMNG = IT_DISPLAY_TMP-LGMNG.

APPEND IT_DISPLAY.

ENDLOOP.

ENDIF.

ENDFORM. " GET_DATA

FORM BUILD_FIELDCAT .

CLEAR : WA_FIELDCAT , IT_FIELDCAT[].

CLEAR WA_FIELDCAT.

WA_FIELDCAT-ROW_POS = '1'.

WA_FIELDCAT-COL_POS = '1'.

WA_FIELDCAT-FIELDNAME = 'VBELN'.

WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.

WA_FIELDCAT-SELTEXT_M = 'Delivery Number'.

WA_FIELDCAT-NO_ZERO = 'X'.

WA_FIELDCAT-KEY = 'X'.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCAT-ROW_POS = '1'.

WA_FIELDCAT-COL_POS = '2'.

WA_FIELDCAT-FIELDNAME = 'POSNR'.

WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.

WA_FIELDCAT-SELTEXT_M = 'Item Number'.

WA_FIELDCAT-NO_ZERO = 'X'.

WA_FIELDCAT-KEY = 'X'.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCAT-ROW_POS = '1'.

WA_FIELDCAT-COL_POS = '3'.

WA_FIELDCAT-FIELDNAME = 'MATNR'.

WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.

WA_FIELDCAT-SELTEXT_M = 'material.'.

WA_FIELDCAT-NO_ZERO = 'X'.

WA_FIELDCAT-KEY = 'X'.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCAT-ROW_POS = '1'.

WA_FIELDCAT-COL_POS = '4'.

WA_FIELDCAT-FIELDNAME = 'ARKTX'.

WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.

WA_FIELDCAT-SELTEXT_M = 'Description'.

WA_FIELDCAT-NO_ZERO = 'X'.

WA_FIELDCAT-KEY = 'X'.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCAT-ROW_POS = '1'.

WA_FIELDCAT-COL_POS = '5'.

WA_FIELDCAT-FIELDNAME = 'LGMNG'.

WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.

WA_FIELDCAT-SELTEXT_M = 'Quantity'.

WA_FIELDCAT-NO_ZERO = 'X'.

WA_FIELDCAT-KEY = 'X'.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

ENDFORM. " BUILD_FIELDCAT

FORM BUILD_LAYOUT .

IT_LAYOUT-ZEBRA = 'X'.

IT_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

ENDFORM. " BUILD_LAYOUT

FORM ALV_OUTPUT .

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = sy-REPID

* I_CALLBACK_USER_COMMAND = 'USER_COMMAND'

* I_CALLBACK_PF_STATUS_SET = 'ZSTATUS1'

IT_FIELDCAT = IT_FIELDCAT

IS_LAYOUT = IT_LAYOUT

I_DEFAULT = 'X'

I_SAVE = 'A'

IT_EVENT_EXIT = IT_EVENTEXIT

TABLES

T_OUTTAB = IT_DISPLAY

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

ENDFORM. " ALV_OUTPUT

Message was edited by: Suhas Saha

PeterJonker · ‎2013 Sep 20

What are you trying to achieve here ?

You don't need to move s_vbeln to vbeln_n. Just use s_vbeln in your where query.

My second comment is:

You don't need to replace s_vbeln with * when empty. When empty it will disregard the select options and will select everything anyway.

PeterJonker · ‎2013 Sep 20

What are you trying to achieve here ?

You don't need to move s_vbeln to vbeln_n. Just use s_vbeln in your where query.

My second comment is:

You don't need to replace s_vbeln with * when empty. When empty it will disregard the select options and will select everything anyway.

Piyushmatrixs · ‎2013 Sep 20

Thanks for Your help Peter Jonker.

Former Member · ‎2013 Sep 20

hi,

why you want to give wild card? 'vbeln in s_vbeln' should work for all of your 3 conditions. just take out the if conditions that you have placed for '*' and it should work..

Thanks

Sam

Piyushmatrixs · ‎2013 Sep 20

Thanks for Your help Sam.

RaymondGiuseppi · ‎2013 Sep 20

Would it seem that you want to tire yourself to code the behavior which the selection will have if you do nothing, or did I miss something?

(Read sql_cond - IN seltab for more information.)

Regards,

Raymond

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Using a wild Card (*) in select statement