topic Re: Dynamic Where Clause in Application Development and Automation Discussions

Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:39:03 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:44:09 GMT

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

( 'value1', 'value2' )

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:48:00 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:49:26 GMT

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.

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:49:32 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:50:58 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:51:41 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 15:53:17 GMT

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.

Re: Dynamic Where Clause

RichHeilman — Thu, 06 Apr 2006 16:01:29 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 16:41:14 GMT

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.

Re: Dynamic Where Clause

RichHeilman — Thu, 06 Apr 2006 16:46:40 GMT

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

Regards,

Rich Heilman

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 16:53:54 GMT

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.

Re: Dynamic Where Clause

RichHeilman — Thu, 06 Apr 2006 16:58:24 GMT

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

Yes, that is whack!!

REgards,

Rich Heilman

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 17:00:18 GMT

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

Re: Dynamic Where Clause

RichHeilman — Thu, 06 Apr 2006 17:18:37 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 17:21:39 GMT

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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 17:30:31 GMT

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.

Re: Dynamic Where Clause

RichHeilman — Thu, 06 Apr 2006 17:42:11 GMT

<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

Re: Dynamic Where Clause

Former Member — Thu, 06 Apr 2006 18:09:02 GMT

Nice!!!

Teacher abuse... a true American tradition.

Re: Dynamic Where Clause

Former Member — Wed, 05 Nov 2008 07:35:32 GMT

Hi Vijay,

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

Regards,

Srinivas