CODEWITHSUNDEEP
abapis-emptyopensqlisnullorempty
Mogera
Mogera

Reputation: 37

SELECT with default result for empty condition?

I have a database table with following fields and values:

Bukrs | Werks | Btrtl | Persg | Persk | value
1000   1000     1001     1      20      1,20
1000                                    1,10

And I am building the query with the following key:

SELECT
FROM dbtab
INTO lv_value
WHERE bukrs = bukrs
    AND (
        werks = werks
        AND btrtl = btrtl
        AND persg = persg
        AND persk = persk
        )

So for query with the exact condition condition match the value must be 1,20, and that is correct.

But for another condition without full match e.g. bukrs = 1000 werks = 1002 btrtl = 1003 persg = 2 persk = 27, it must be 1,10.

So everything which doesn't fit full key should fallback to default line with value 1,10. For example:

Andrew has the following Parameters

Bukrs = 1000
Werks = 1000 
Btrtl = 1001
Persg = 1
Persk = 20

Peter has the following Parameters

Bukrs = 1000
Werks = 1004
Btrtl = 1002
Persg = 1
Persk = 27

For Andrew 1,20 should be selected, for Peter it should be 1,10.

Upvotes: 0

Views: 3105

Answers (3)

Mogera
Mogera

Reputation: 37

I Solved my problem like this:

  SELECT * FROM  dbtab
  INTO CORRESPONDING FIELDS OF TABLE LT_TAB
  WHERE  APPLICATION  = LV_APPL
  AND ( BUKRS = E_PERNR-BUKRS OR BUKRS IS NULL OR BUKRS = SPACE )
  AND ( WERKS = E_PERNR-WERKS OR WERKS IS NULL OR WERKS = SPACE )
  AND ( BTRTL = E_PERNR-BTRTL OR BTRTL IS NULL OR BTRTL = SPACE )
  AND ( PERSG = E_PERNR-PERSG OR PERSG IS NULL OR PERSG = SPACE )
  AND ( PERSK = E_PERNR-PERSK OR PERSK IS NULL OR PERSK = SPACE )
  AND    ENDDA       GE E_BEGDA
  AND    BEGDA       LE E_BEGDA
ORDER BY BUKRS DESCENDING WERKS DESCENDING BTRTL DESCENDING PERSG DESCENDING PERSK DESCENDING.

 READ TABLE LT_TAB INTO LS_TAB INDEX 1.
 LV_KWERT = LS_TAB-KONST.

Upvotes: 0

Suncatcher
Suncatcher

Reputation: 10621

Try this way:

DATA: Andrew TYPE dbtab.

Andrew-bukrs = '1000'.
Andrew-werks = '1000'.
Andrew-btrtl = '1001'.
Andrew-persg = '1'.
Andrew-persk = '20'.

SELECT SINGLE value INTO @DATA(Andy) 
  FROM dbtab WHERE werks = ANY ( SELECT CASE WHEN bukrs = @Andrew-bukrs 
                                              AND werks = @Andrew-werks 
                                              AND btrtl = @Andrew-btrtl
                                              AND persg = @Andrew-persg 
                                              AND persk = @Andrew-persk
                                        THEN werks 
                                        ELSE '9999' 
                                 END AS werks FROM dbtab 
                               ).

WRITE / Andy-value.

This snippet gives you 1.20, and the same query for the person with only BUKRS gives you 1.10.

DATA: Peter TYPE dbtab.

Peter-bukrs = '1000'.

SELECT ....

Explanation: here we simulate XOR operator for ABAP SQL through the subqueries. Our subquery always returns two values: only one truthful row from dbtab table, the other one is always a dummy 9999. This way the outer query will always return single value by the plant, either real or dummy.

Prerequisite for this solution: 9999 (subquery plant) must be invariably non-existent in dbtab.

Upvotes: 1

shawnt00
shawnt00

Reputation: 17935

So I take it that you're matching against parameter values. Here's a shot in the dark as I don't know anything about ABAP.

SELECT
FROM dbtab
INTO lv_value
WHERE bukrs = bukrs
ORDER BY DECODE(
    (       werks = @werks
        AND btrtl = @btrtl
        AND persg = @persg
        AND persk = @persk
    ), 2,
    (werks IS NULL), 1
    0
)
UP TO 1 ROWS;

Upvotes: 0

Related Questions