Skip to Content
author's profile photo Former Member
Former Member

How to reference input parameters in opensql where clause?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2014 at 04:51 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2014 at 04:55 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2014 at 05:25 PM

    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).


    😔

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.