How to reference input parameters in opensql where clause?

[Former Member]

Hello!

I'm new to ABAP development and I'm having difficulty getting a piece of opensql to do what I want.

I'm trying to reference a parameter in an openSQL query, but it does not seem like it.  The parameter is PI_VKORG.

This code works ok:

SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~vtweg vbak~auart
   vbak~ernam vbak~erdat vbak~vdatu vbak~vsbed vbuk~lfstk
   vbkd~inco1 INTO TABLE t_vbak
    from vbak
     inner join vbuk on vbak~vbeln = vbuk~vbeln
     left join vbkd on vbak~vbeln = vbkd~vbeln and vbkd~posnr = '000000'
     where ( vbuk~lfstk = 'B' OR vbuk~lfstk = 'C' ) AND
       ( VBAK~VKORG = PI_VKORG ).

What I'd like to do is change the last line to this:

PI_VKORG is not null and VBAK~VKORG = PI_VKORG

... but this throws the syntax error "Field PI_VKORG is unknown".  The intent is to only check against this condition if the function module received a value for that parameter.  This is typical of how I would do this in T-SQL, but maybe it doesn't work here.

Thoughts?  Do I have to grab the whole thing then filter out the vkorg I don't want when I'm building my output table in the loop?

Thanks!

Pete

[Former Member]

Hello!

I'm new to ABAP development and I'm having difficulty getting a piece of opensql to do what I want.

I'm trying to reference a parameter in an openSQL query, but it does not seem like it.  The parameter is PI_VKORG.

This code works ok:

SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~vtweg vbak~auart
   vbak~ernam vbak~erdat vbak~vdatu vbak~vsbed vbuk~lfstk
   vbkd~inco1 INTO TABLE t_vbak
    from vbak
     inner join vbuk on vbak~vbeln = vbuk~vbeln
     left join vbkd on vbak~vbeln = vbkd~vbeln and vbkd~posnr = '000000'
     where ( vbuk~lfstk = 'B' OR vbuk~lfstk = 'C' ) AND
       ( VBAK~VKORG = PI_VKORG ).

What I'd like to do is change the last line to this:

PI_VKORG is not null and VBAK~VKORG = PI_VKORG

... but this throws the syntax error "Field PI_VKORG is unknown".  The intent is to only check against this condition if the function module received a value for that parameter.  This is typical of how I would do this in T-SQL, but maybe it doesn't work here.

Thoughts?  Do I have to grab the whole thing then filter out the vkorg I don't want when I'm building my output table in the loop?

Thanks!

Pete

[Former Member]

You can make the parameter mandatory, so that it will never be empty.

Or you could turn it into a SELECT-OPTION and add a line that excludes null values.

Rob

[Former Member]

Sometimes the user will want to pass a blank parameter; in that case I would disregard the where clause.

[Former Member]

HI pete,

Can you change your query to

IF PI_VKORG NE ''.
SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~vtweg vbak~auart
    vbak~ernam vbak~erdat vbak~vdatu vbak~vsbed vbuk~lfstk
    vbkd~inco1 INTO TABLE t_vbak
     from vbak
      inner join vbuk on vbak~vbeln = vbuk~vbeln
      left join vbkd on vbak~vbeln = vbkd~vbeln and vbkd~posnr = '000000'
      where ( vbuk~lfstk = 'B' OR vbuk~lfstk = 'C' ) AND
        ( VBAK~VKORG = PI_VKORG ).
   ENDIF.

[Former Member]

I'm looking for a bit more of a general solution; I have about 8 more parameters to check on this table.  Thanks though.

[Former Member]

This works, but it seems like overkill for what I'm doing:

DATA:
       L_WHERECLAUSE TYPE STRING.

L_WHERECLAUSE = '( vbuk~lfstk = ''B'' OR vbuk~lfstk = ''C'' )'.
if PI_VKORG is not initial.
   CONCATENATE L_WHERECLAUSE ' AND ( VBAK~VKORG = PI_VKORG )' into L_WHERECLAUSE.
   ENDIF.
if PI_VTWEG is not initial.
   CONCATENATE L_WHERECLAUSE ' AND ( VBAK~VTWEG = PI_VTWEG )' into L_WHERECLAUSE.
   ENDIF.

SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~vtweg vbak~auart
   vbak~ernam vbak~erdat vbak~vdatu vbak~vsbed vbuk~lfstk
   vbkd~inco1 INTO TABLE t_vbak
    from vbak
     inner join vbuk on vbak~vbeln = vbuk~vbeln
     left join vbkd on vbak~vbeln = vbkd~vbeln and vbkd~posnr = '000000'
     where (L_WHERECLAUSE).