How to convert this statement to SAP's Open SQL?

[Former Member]

Hello,

Can someone please tell me how can I get an equivalent statement in Open SQL to the one below?

Unfortunately SAP's Open SQL doesn't allow CASESs and I can't have in in the WHERE clause because there are other fields I need to check and cannot eliminate a whole row if this particular field is 0.

SELECT AVG(CASE WHEN FIELD1 > 0 THEN FIELD1 ELSE NULL END)

FROM... WHERE...

Thanks

[Former Member · Accepted solution]

Roy,

The reason why Open SQL does not have a CASE construct is that it is embedded into programming languages, ABAP or Java, that do have such a construct. Program-like extensions to SQL are simply there to make them (somewhat) usable in a non-program environment. They are at best limited emulations of what a real programming language has to offer.

In your position I wouldn't think twice about trying to construct some brilliant SQL to do the trick, if simply reading the rows and processing them in the program code will be much simpler. The coding, especially testing for each field being zero, may be a little tedious but it will be crystal-clear (unlike an obscure and impossible to maintain SQL trick, if there is one).

So my advice is: code a simple SQL to retrieve the rows and use equally simple Java code to get the result you want.

Regards,

Mark

[Former Member · Accepted solution]

Thanks Mark.

I was saving this option as my last resort because processing through the code is very time consuming in case large dataset is involved...

Rgds,

Roy

[sundaresan_k]

Dear Roy,

Just came across your post and wanted to let you know that starting NW AS ABAP 7.4 SP5 onwards OpenSQL supports CASE constructs.

For example you can write and OpenSQL statement with a CASE construct as follows

    SELECT id, char1, char2,
           CASE char1
             WHEN 'aaaaa' THEN ( char1 && char2 )
             WHEN 'xxxxx' THEN ( char2 && char1 )
             ELSE @else
           END AS text
           FROM demo_expressions
           INTO TABLE @DATA(results).

You can find more information about the new capabilities of advanced OpenSQL in the following link

http://help.sap.com/abapdocu_740/en/index.htm?file=ABENNEWS-740_SP05-OPEN_SQL.htm

Best regards

Sundar

[Former Member]

Hello Roy,

I suggest you to make your AVG on FIELD1 where it is positive :

SELECT AVG( field1 )
  FROM ...
  WHERE ... AND
    field1 > 0.

Hope this help!

Samuel

[lbreddemann]

> Can someone please tell me how can I get an equivalent statement in Open SQL to the one below?

> Unfortunately SAP's Open SQL doesn't allow CASESs and I can't have in in the WHERE clause because there are other fields I need to check and cannot eliminate a whole row if this particular field is 0.

>

> SELECT AVG(CASE WHEN FIELD1 > 0 THEN FIELD1 ELSE NULL END)

> FROM... WHERE...

You don't do this in ABAP.

ABAP does not know NULL.

Therefore, I'd say: "Your data is faulty!".

If this some kind of non-SAP table you like to access, I'd go for a VIEW in which you convert the NULLs to 0 (NVL).

regards,

Lars

[Former Member]

Hi Lars,

I am not doing this in ABAP but Java and this is not an SAP standard table, I just couldn't find a better forum to place this question.

My data is not faulty, I think you just misunderstood my requirement, so forget about my query and consider this:

I have 4 fields in a table, each can be between 0-5. I would to average each field but exclude the zeros.

So, for example if this is my data:

Field1, Field2, Field3, Field4

2 3 3 5

3 0 1 0

Then the average I'm after is:

Field 1 -> (2+3)/2=2.5

Field 2 -> 3/1=3 (Not (3+0)/2 as the standard average does.

Field 3 -> (3+1)/2=2

Field 4 -> 5/1=5 (Not (5+0)/2 as the standard average does.

Samuel,

Like I said in my first post I can't have in in the WHERE clause because there are other fields I need to check and cannot eliminate a whole row if this particular field is 0. See my sample data above.

Rgds,

Roy

[lbreddemann]

HI Ray,

you're right: I totally misunderstood what you want and how your data looks like!

Now I think I do understand what you mean - but I have to tell you, that there is no equivalent for this "selective average" function you created with the case construct in OpenSQL.

Either you pack this transformation into a view in the database or you've to calc the averages yourself in your application.

I'd go for the view in this case...

regards,

Lars

[Former Member]

Thanks guys, at least I know that there is no way to get around this using the query syntax...

[Former Member]

Hello again,

I think I can pull this off using NULLIF, having looked [here|http://help.sap.com/saphelp_nw04/helpdata/en/40/b54ea14933458e96c736ec03295bfa/content.htm] I see that it can be used however I don't know how it's called / used.

Can someone please show me an example of how to use this function using Open SQL?

Thanks,

Roy

[lbreddemann]

> I think I can pull this off using NULLIF, having looked [here|http://help.sap.com/saphelp_nw04/helpdata/en/40/b54ea14933458e96c736ec03295bfa/content.htm] I see that it can be used however I don't know how it's called / used.

> Can someone please show me an example of how to use this function using Open SQL?

Hi Roy,

to my understanding, this list does only show you the reserved terms and not the implemented functionality.

This is to prevent the usage of column/table/view-names that have a specified meaning in SQL-standard.

Unfortunately this does not mean, that there actually exists a NULLIF function...

Let us know how you actually solved the issue, then!

regards,

Lars

[Former Member]

Hi Lars,

For now I am getting the data line by line and doing the average through the Java code.

I see no other way to get around this with the limited functionality of the Open SQL.

Roy

[Former Member]

What if I'll write such a function that returns NULL if the value is less than 1 or returns the value if 1 or greater?

Any idea if that is supported and how to do that?