Dynamic Where Clause

[Former Member]

Hi,

REPORT  ZDYNAMIC_WHERE.

TABLES: VBAK.
DATA: CONDITION TYPE STRING.
DATA: BEGIN OF ITAB OCCURS 0,
        VBELN LIKE VBAK-VBELN,
        POSNR LIKE VBAP-POSNR,
     END OF ITAB.
DATA: IT_OPTIONS LIKE RFC_DB_OPT OCCURS 0 WITH HEADER LINE.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.
CONCATENATE 'VBELN'  'IN'  'S_VBELN.' INTO CONDITION SEPARATED BY SPACE.

IT_OPTIONS-TEXT = CONDITION.
APPEND IT_OPTIONS.
CLEAR IT_OPTIONS.
SELECT VBELN
       POSNR
       FROM VBAP
       INTO TABLE ITAB
       WHERE (IT_OPTIONS).

LOOP AT ITAB.

  WRITE 'hello'.

ENDLOOP.

the above code i'm unable to use select options in where clause, is there any other way to use select options in dynamic where clause.

Please suggest the possible solution if any.

Regards

vijay

[Former Member]

Hi,

REPORT  ZDYNAMIC_WHERE.

TABLES: VBAK.
DATA: CONDITION TYPE STRING.
DATA: BEGIN OF ITAB OCCURS 0,
        VBELN LIKE VBAK-VBELN,
        POSNR LIKE VBAP-POSNR,
     END OF ITAB.
DATA: IT_OPTIONS LIKE RFC_DB_OPT OCCURS 0 WITH HEADER LINE.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.
CONCATENATE 'VBELN'  'IN'  'S_VBELN.' INTO CONDITION SEPARATED BY SPACE.

IT_OPTIONS-TEXT = CONDITION.
APPEND IT_OPTIONS.
CLEAR IT_OPTIONS.
SELECT VBELN
       POSNR
       FROM VBAP
       INTO TABLE ITAB
       WHERE (IT_OPTIONS).

LOOP AT ITAB.

  WRITE 'hello'.

ENDLOOP.

the above code i'm unable to use select options in where clause, is there any other way to use select options in dynamic where clause.

Please suggest the possible solution if any.

Regards

vijay

[Former Member]

Try to CONCATENATE all entries of S_VBELN into a field, so that this field will have the following contents:

( 'value1', 'value2' )

[Former Member]

Hi,

i don't know what the user going to Give in selection screen. what if he enter Ranges , CP, or exclude etc..

Regards

vijay

[Former Member]

Is the user allowed to enter ranges? you can restrict him from doing this ...

[Former Member]

Why did you include a dot (.) in your dynamic condition ?? perhaps this is the reason ...

[Former Member]

and if you enter the condition in the select-option table like eq and you use the in operator?

[RichHeilman]

Hey Vijay, there may be a problem with using the IN operater here. Check out this code. It generates a FORM on the fly and execute your select statement.

report zrich_0001.


tables: vbak.

data: begin of itab occurs 0,
      vbeln like vbak-vbeln,
      posnr like vbap-posnr,
      end of itab.

data: it_options like rfc_db_opt occurs 0 with header line.
data: condition type string.

types: t_source(72).

data: routine(32) value 'TEMP_ROUTINE',
            program(8) value 'ZTEMP_REPORT',
            message(128),
            line type i.

data: isource type table of t_source,
      xsource type t_source.


select-options: s_vbeln for vbak-vbeln.

start-of-selection.

  concatenate 'VBELN'  'IN'  'S_VBELN.'
          into condition separated by space.
  it_options-text = condition.
  append it_options.
  clear it_options.

  perform execute_select_statement.

  loop at itab.
    write:/ itab-vbeln, itab-posnr.
  endloop.


*---------------------------------------------------------------------*
*       FORM execute_select_statement                                 *
*---------------------------------------------------------------------*
form execute_select_statement.

  xsource = 'REPORT ZTEMP_REPORT.'.
  insert xsource  into isource index 1.
  xsource = 'FORM TEMP_ROUTINE tables itab s_vbeln.'.
  insert xsource  into isource index 2.

  xsource = 'select vbeln posnr from vbap into table itab'.
  append xsource to isource.

  xsource = 'WHERE'.
  append xsource to isource.

  loop at it_options.

    xsource = it_options.
    append xsource to isource.

  endloop.

  xsource = '.'. append xsource to isource.


  xsource = 'ENDFORM.'.
  append xsource to isource.

  generate subroutine pool isource name program
                           message message
                           line line.
  if sy-subrc = 0.
    perform (routine) in program (program) tables itab
                                                  s_vbeln.
  else.
    write:/ message.
  endif.

endform.

Regards,

Rich Heilman

[Former Member]

Vijay,

Has this example not worked on your system?

REPORT ZDYNAMIC_WHERE.

TABLES: VBAK.

DATA: CONDITION TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

END OF ITAB.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

SELECT VBELN POSNR FROM VBAP INTO TABLE ITAB

WHERE (CONDITION).

LOOP AT ITAB.

WRITE 'hello'.

ENDLOOP.

[RichHeilman]

John, it doesn't work for me. Says that CONDITION is not an internal table.

Regards,

Rich Heilman

[Former Member]

That's whack !!! And you have CONDITION defined as STRING?

I enter a range of 3 order numbers. And execute and I get 6 "hello"-s strung across the screen (b/c each order has 2 line items) on our 5.0 system.

We have a 4.7 also - I'm gonna try it there.

[RichHeilman]

I'm on a 46c, it comes up with this syntax error.

Yes, that is whack!!

REgards,

Rich Heilman

[Former Member]

Hi all,

John my code is working in Unicode environment(4.7), i tested it in non unicode below 4.7.

and Rich i checked the similar code from Srinivas code, but i want it to be Optimized one.

Mike . is not the problem, any way it solved .

Regards

vijay

[RichHeilman]

John, your solution works quite well in my WAS 7.0 engine. Good call!

report zrich_0001.

tables: trdir.

data: condition type string.
data: begin of itab occurs 0,
      name like trdir-name,
      cnam like trdir-cnam,
      end of itab.

select-options: s_name for trdir-name..

condition =  'NAME IN S_NAME.'.

select name cnam from trdir into table itab
where (condition).

loop at itab.
  write :/ itab-name, itab-cnam.
endloop.

Regards,

Rich Heilman

[Former Member]

Yes Rich, it was i generally work test programs in 4.6C , i didn't test it in My DEv. Now i'm able to RUn it.

Thank you all for your inputs.

Regards

Vijay

[Former Member]

Excellent... dynamic WHERE clauses are wonderfully, powerful statements.

Throw in some field symbols, a few class objects, and a flux capacitor... and you've got a REAL app then.

[RichHeilman]

<i>and a flux capacitor</i>

LOL. That's funny.

That reminds me of something that our class did to a teacher in 11th grade. We were suppose to watch a video(MacBeth I think) and the teacher was a little weird(crazy). We always would mess with her. She couldn't get the VCR working. One of us, said, "I think it could be the flux capacitor". She fell for it. Another teacher came into help, and she said, "I think it might be the flux capacitor". The other teacher started busting up laughing. It was hilarious.

Regards,

Rich Heilman

[Former Member]

Nice!!!

Teacher abuse... a true American tradition.

[Former Member]

Sure...

Change your code to this:

REPORT ZDYNAMIC_WHERE.

TABLES: VBAK.

DATA: CONDITION TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

END OF ITAB.

*DATA: IT_OPTIONS LIKE RFC_DB_OPT OCCURS 0 WITH HEADER LINE.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

*IT_OPTIONS-TEXT = CONDITION.

*APPEND IT_OPTIONS.

*CLEAR IT_OPTIONS.

SELECT VBELN POSNR FROM VBAP INTO TABLE ITAB

WHERE (CONDITION).

LOOP AT ITAB.

WRITE 'hello'.

ENDLOOP.

[Former Member]

try this

CONCATENATE 'VBELN'  'IN'  'S_VBELN' INTO CONDITION SEPARATED BY SPACE.

instead of this

CONCATENATE 'VBELN'  'IN'  'S_VBELN.' INTO CONDITION SEPARATED BY SPACE.

[Former Member]

Hi Vijay,

Is your problem solved, Would you be able to pass select option in dynamic where clause. Please let me know.

Regards,

Srinivas