Select query problem

[Former Member]

Experts please guide

In selection screen of the report we have three fields one field is mandatory and 2 fields are not mandatory.

s_vbeln is mandatory

p_vkorg is not mandatory

p_audat is not mandatory,

User can give input only one or all the three fileds. So it should work like se16 .

so how we need to write this query,we need to fetch that from a single table .

I have tried like this

select

VBELN

ERDAT

ERZET

ERNAM

ANGDT

BNDDT

from vbak

into table itab

where vbeln in s_vbeln

and auart = p_auart

and vkorg = p_vkorg.

select 

VBELN

ERDAT

ERZET

ERNAM

ANGDT

BNDDT

from vbak

into table itab

where vbeln in s_vbeln

or auart = p_auart

or vkorg = p_vkorg.

Both the select statements are not working. If user gives only mandatory field s_vbeln it is not working in first select , but in second select it is working, But in second select select if user gives all the three s_vbeln , p_auart and p_vkorg  , it is bringing all the reocrds becuase we are using OR condition,

I want it to work like SE16, when we give vbeln it should bring only that , when they give vbeln and vkorg then it should bring only that combination, if they give three it should bring only that 3 satisifed combination.

I tried but not getting idea , so please help.

Moderator message - basic question locked and points unassigned.

Message was edited by: Rob Burbank

[Former Member]

use dynamic where clause.

DATA: cond(72) TYPE c,

          itab LIKE TABLE OF cond.

CONCATENATE 'vbeln in s_vbeln' INTO cond.

APPEND cond TO itab.

if p_auart is not initial.

CONCATENATE 'AND auart = p_auart' INTO cond.

else.

concatenate a period at the end.

endif.

APPEND cond TO itab.

if p_vkorg is not initial.

CONCATENATE 'AND vkorg = p_vkorg' INTO cond.

else.

concatenate a period at the end.

endif.

APPEND cond TO itab.

select 

VBELN

ERDAT

ERZET

ERNAM

ANGDT

BNDDT

from vbak

into table itab

where ( itab[] ).

.

sometihing like this

Message was edited by: Pratik Mallick

[SharathYaralkattimath]

Hi Shiva,

Try this,

You can declare the parameters as select options with NO-EXTENSION & NO INTERVAL screen options & using IN operator for all 3 selections,

DATA spfli_wa TYPE spfli.

SELECT-OPTIONS s_carrid FOR spfli_wa-carrid NO-EXTENSION
                                            NO INTERVALS.

[Former Member]

thank you for your response. But in my case vbeln is select options and rest two are parameters. So generally how we will wirte select queries for this kind of scenarios, pls help.

[SharathYaralkattimath]

Hi Shiva,

Declare the two parameters as select options with no extension & no inteval additions

execute this piece of code, you will understand.

DATA spfli_wa TYPE spfli.

data t_spfli TYPE TABLE OF spfli.

SELECT-OPTIONS s_carrid FOR spfli_wa-carrid NO-EXTENSION

                                             NO INTERVALS.

SELECT *

   FROM spfli

   INTO TABLE t_spfli

   WHERE carrid in s_carrid.

DESCRIBE TABLE t_spfli.

WRITE sy-tfill.

In your case,

do like this,

SELECT-OPTIONS: s_vbeln for vbak-vbeln,

                            s_vkorg for vbak-vkorg NO-EXTENSION NO INTERVAL,

                            s_auart for vbak-auart NO-EXTENSION NO INTERVAL

select

VBELN

ERDAT

ERZET

ERNAM

ANGDT

BNDDT

from vbak

into table itab

where vbeln in s_vbeln

and auart IN s_auart

and vkorg IN s_vkorg.

It will meet your requirements.

[former_member196490]

If the parameter value is blank the SELECT statement tries to fetch data from vbak where auart and vkorg are blank.

You can convert vkorg and auart parameter fields on selection screen to Select-options with no interval no extention instead

[Former Member]

the replys on converting the select option with no-intervals no extension are the easiest answer.  (and most likely the best!).  If you really want to use a parameter on the selection screen, simply create an internal range in your program and populate it based on the parameters.  For instance:

data:  r_vkorg type range of vkorg,

         ll_vkorg like line of r_vkorg.

if P_VKORG is initial.

  L_VKORG = 'ICP*''

  append l_vkorg  to r_vkorg.

else.

  l_vkorg(3) = 'IEQ'.

  l_vkorg-low = p_vkorg.

  append l_vkorg to r_vkorg.

endif.

Then use the range in your select.

[Former Member]

Hi Shiva Kumar,

Please try this code .

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

*& Report  ZTESTPRO9                                                   *

*&                                                                     *

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

*&                                                                     *

*&                                                                     *

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

REPORT  ztestpro9 .

TABLES:vbak.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS:s_vbeln FOR vbak-vbeln OBLIGATORY,

               p_vkorg FOR vbak-vkorg,

               p_audat FOR vbak-audat.

SELECTION-SCREEN END OF BLOCK b1.

TYPES:BEGIN OF ty_vbak,

      vbeln TYPE vbak-vbeln,

      erdat TYPE vbak-erdat,

      erzet TYPE vbak-erzet,

      ernam TYPE vbak-ernam,

      angdt TYPE vbak-angdt,

      bnddt TYPE vbak-bnddt,

      END OF ty_vbak.

DATA:itab_vbak TYPE STANDARD TABLE OF ty_vbak,

      wa_vbak TYPE ty_vbak.

START-OF-SELECTION.

  PERFORM get_data.

END-OF-SELECTION.

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

*&      Form  get_data

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

*       text

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

*  -->  p1        text

*  <--  p2        text

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

FORM get_data .

  SELECT vbeln

         erdat

         erzet

         ernam

         angdt

         bnddt

         FROM vbak

         INTO TABLE itab_vbak

         WHERE vbeln IN s_vbeln

         OR vkorg = p_vkorg

         OR auart = p_audat.

  IF sy-subrc EQ 0.

    WRITE:(110) sy-uline.

    FORMAT COLOR COL_HEADING ON.

    WRITE:/1 sy-vline,

           5 'Sales Doc.',

           20 sy-vline,

           25 'Date',

           34 sy-vline,

           36 'Entry Time',

           48 sy-vline,

           53 'Name',

           63 sy-vline,

           64 'Quatation valid from',

           85 sy-vline,

           87 'Quatation validity date',

           110 sy-vline.

    WRITE:(110) sy-uline.

    LOOP AT itab_vbak INTO wa_vbak.

      FORMAT RESET.

      FORMAT COLOR COL_NORMAL.

      WRITE:/1 sy-vline,

             5 wa_vbak-vbeln,

             20 sy-vline,

             22 wa_vbak-erdat,

             34 sy-vline,

             36 wa_vbak-erzet,

             48 sy-vline,

             52 wa_vbak-ernam,

             63 sy-vline,

             69 wa_vbak-angdt,

             85 sy-vline,

             92 wa_vbak-bnddt,

             110 sy-vline.

    ENDLOOP.

    WRITE:(110) sy-uline.

  ENDIF.

ENDFORM.                    " get_data

[Clemenss]

Hi shiva,

generally you can put parameters in to ranges, ie.

Why can't this f.. advanced editor remember my cursor position???
How to use fixed space font here???
PARAMETERS:
  p_vkorg TYPE vbak-vkorg,  
  p_vbeln TYPE vbak-vbeln,
...
FORM SELECT.
  DATA:
    lt_r_vkorg TYPE RANGE OF vbak-vkorg,  
    lt_r_vbeln TYPE RANGE OF vbak-vbeln.
  FIELD-SYMBOLS:
     <r_vkorg> LIKE LINE OF lt_r_vkorg,   
     <r_vbeln> LIKE LINE OF lt_r_vbeln.
  IF p_vkorg IS NOT INITIAL. 
    APPEND INITIAL LINE TO lt_r_vkorg ASSIGNING <r_vkorg>.   
    <r_vkorg>-sign = 'I'.
    <r_vkorg>-option = 'EQ'.
* or quicker <r_vkorg> = 'IEQ'.
    <r_vkorg>-low = p_vkorg.
  ENDIF.      
  IF p_vbeln IS NOT INITIAL. 
    APPEND INITIAL LINE TO lt_r_vbeln ASSIGNING <r_vbeln>.   
    <r_vbeln>-sign = 'I'.
    <r_vbeln>-option = 'EQ'.
* or quicker <r_vbeln> = 'IEQ'.
    <r_vbeln>-low = p_vbeln.
  ENDIF.      
  SELECT ...
    INTO ...
    FROM ...
    WHERE vkorg IN lt_r_vkorg
      AND vbeln IN lt_r_vbeln
    ...
ENDFORM  
What is the difference between syntax highlighting types (easy to use as apple fast as google :-)

Regards,

Clemens