Select columns from table conditionally

[Former Member]

Experts,

I have a custom table with 2 columns. Either column A will be filled or column B will be filled for every single row.

I want to fetch the value of column A into a variable(lv_amount) if it's filled, or I have to fetch the value of column B into that same variable lv_amount. The easiest thing to do will be to fetch both columns from the table and then use an IF statement to see which column is filled and move the value to lv_amount. Since we have lot of newer expressions in ABAP, I am curious to know if we can select columns from a table conditionally?

BR,

Bharath

[venkateswaran_k]

Hi aspire.wf

You can do as below: The example code below

You can get sum of A and Sum of B in C1 and C2 respctively. Then lv_amount = c1 + c2.

data : c1 type p decimals 2,
       c2 type p decimals 2.

SELECT SUM( a ) SUM( b ) into ( c1 , c2 ) from <yourtable>.

lv_amount = c1 + c2.

Regards,

Venkat

[Former Member]

Hi Venkat,

Thanks a lot for your reply. Sorry - I think I have not clearly mentioned the requirement.

I have 2 columns in a table. Both columns are amount columns. At a time, only 1 of the columns will have value in the table. For example, assume there are 2 records in the table. It will look something as below,

Column A Column B

100 0(ZERO)

0(ZERO) 500

As you can see, for every record, only one of the columns will be filled(Either column A or column B). Now I have to fetch a single record from this custom table. I want to check which colulmn is filled(Either A or B) and then fetch that amount value into a local variable.

What I am trying to avoid is to do the below,

select single column A, column B from <<ZTAB1>> into....
if column A is not initial.
lv_amount = column A.
else.
lv_amount = column B.
endif.

<br>In this approach, I will fetch both the columns from the database using select query. I am interested to know 
if the latest ABAP has syntax to check which column is filled in the table record and then fetch that value 
into lv_amount directly in the select query itself.

BR,

Bharath

[Sandra_Rossi]

Is CASE what you need?

SELECT
CASE WHEN column1 <> ' ' THEN column1 ELSE column2 END AS column1_or_column2
FROM table
...

[Former Member]

Sandra,

Thank you so much for your reply. I tried the below code based on your reply and its working perfectly now. I was struggling with the part of getting column value after THEN keyword. I didn't know that we can simply mention the column name after THEN and its value will be returned. This is so cool.

DATA: lv_amount TYPE i.

SELECT SINGLE CASE WHEN column1 IS NOT INITIAL  THEN column1 ELSE column2
END AS col1or2
FROM ztable1
WHERE customer = '1A'
INTO @lv_amount.

[Sandra_Rossi]

Cool. Edit: fixed the typo in my answer ENDCASE -> END

[Former Member]

Hi @sandra.rossi,

I came across a function called COALESCE in ABAP today. This seems to be an alternate option to solve my problem.

So the same case using case statement can be written like this,

SELECT SINGLE
COALESCE( column1, column 2 ) AS column1_or_column2
FROM table

COALESCE function will take 1 or more input parameters. Whichever parameter has a value in it, the value of that parameter is returned. This exactly meets my requirement.

Hope this may benefit someone in the future.

BR,

Bharath

[Sandra_Rossi]

At w3schools.com, it says "The COALESCE() function returns the first non-null value in a list."

You must be careful with NULL as ABAP usually doesn't use NULL for an initial field. With ABAP, NULL values usually concern only new columns of already existing lines (provided that the new column is not marked "not null" in the DDIC), or columns from joined tables for which no lines have been extracted in left joins for instance.

[Former Member]

Hi Sandra,

Thanks a lot for the warning. I tried my program with COALESCE again now and clearly its failing now based on the Not null condition that you have mentioned. Its not going to work and I take back suggesting this option. I am sticking to case statement that you suggested which works perfectly.

Thank you so much for taking your time.

BR,

Bharath

[Former Member]

Hi Sandra,

I have column 1 with value as 0.00 and column 2 as 354. Now the expectation is that 354 value should be returned. This is happening in Case statement. So far so good. When I use COALESCE, instead of returning column 2, its returning column 1 which has the value 0.00.

As per my understanding, this is happening because COALESCE thinks that value 0.00 is a NOT NULL value. So it returns the first NOT NULL value. Is this correct?

Just to play around, I set the initial flag for column 1 and column 2 in SE11. But I don't see any impact for these 2 fields when I click Utilites->Database Object->Display in SE11. I was expecting that these 2 fields will change from NOT NULL to NULL. That didn't happen. Is this because ABAP will never set NULL value for any database field? If that is the case, can you please tell me the purpose of Initial checkbox in DDIC?

I read SAP documentation for this checkbox which says that NOT NULL will be set for key fields by default. This means, we don't even have to select Initial checkbox for Key fields. Is this correct?

Based on my understanding, setting Initial checkbox means, that field will be created as NOT NULL in database. If we don't set this flag, it should create the field as NULL In database. But this is not happening as I can see that whether we set the flag or not, every single database field shows as Not null in Utility->database object->display.

NOTE: Table already has 10 records. Now I created a new field in this table. I expected that this field will show as NULL in database object display. It does not. Then I set the Initial flag for this new field and checked database object display. It still shows as NOT NULL.

I am curious to know why we have Initial checkbox and when should we select it?

BR

Bharath