How to optimise this query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to optimise this query?

Author

Topic

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-22 : 14:23:47

Hello,

I have this working part in a SELECT statement


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

And was wondering how to improve it and not repeat the AVG(CASE...) part twice

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 04:38:30

Can't see the rest of your query but it seems like you don't want any USER_FRIENDLY > 0

If USER_FRIENDLY can not be negative then you could replace the inner cases with:

NULLIF([USER_FRIENDLY], 0)

Probably wouldn't improve things much.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-23 : 05:17:41

Also, the average of positive numbers will always be positive!
So what's the point of having the CASE statement?

N 56°04'39.26"
E 12°55'05.63"

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-23 : 05:40:10

quote:
Originally posted by Peso

Also, the average of positive numbers will always be positive!
So what's the point of having the CASE statement?

N 56°04'39.26"
E 12°55'05.63"

Because I want to exclude the 0's in my case which affect the average.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 06:07:07

Can you just move the condition to the WHERE clause? If you don't want the 0's don't select them?

Or do the selection inside a derived table.....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-23 : 06:42:16

quote:
Originally posted by Transact Charlie

Can you just move the condition to the WHERE clause? If you don't want the 0's don't select them?

Or do the selection inside a derived table.....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Hi Charlie,

Because I've got more fields like this in the same row and I don't want to exclude the whole row cause it may contain data that is valid for other fields.
Anyway, I now realize that our JDBC driver uses Open SQL that doesn't permit CASEs so it it is not relevant anymore and I need to find a way to achieve that part in Open SQL:

SELECT AVG(CASE WHEN VALUE_COST > 0 THEN VALUE_COST ELSE NULL END)
FROM... WHERE...

I have opened new post for that:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150536[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-23 : 07:57:02

Stop and think about your math skills!

CASE WHEN AVG(CASE WHEN USER_FRIENDLY > 0 THEN USER_FRIENDLY ELSE NULL END)

The CASE statement filters out all positive number; 1, 2, 3, 4 etc.
An average of these numbers will ALWAYS be a positive number greater than zero. It can NEVER be zero or less.
If one and only one record is positive, the average will be positive. The resulting value can only be zero if ALL records all less than or equal to zero.
This is if we only discuss integers.

As Kristen points out, which range does USER_FRIENDLY operate?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-23 : 07:59:32

Are we talking about 0 and 1 only? Or 0 to 100?

N 56°04'39.26"
E 12°55'05.63"

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-23 : 08:02:56

quote:
Originally posted by Peso

Are we talking about 0 and 1 only? Or 0 to 100?

N 56°04'39.26"
E 12°55'05.63"

To answer your question the field rangers between 0 to 5.

Try running a normal AVG 0n this numbers: 1,2,5,0
Then try it with my CASEd AVG.
You will get different results and the result I'm interested at is the second one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-23 : 09:09:38

I am pretty sure I known math. I studied math on University to become a High School teacher.

N 56°04'39.26"
E 12°55'05.63"

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-23 : 09:27:21

It' not related to math but to the fact that the standard AVG counts zeros as well and I would like to exclude them but with open sql case is not permitted.
I'm pretty sure that 5+0+2/3=2.3 is different than 5+2/2=3.5....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-24 : 06:10:22

Your question is ALL about math. I realize that, why don't you?

Try this

SUM(USER_FRIENDLY) / SUM(SIGN(USER_FRIENDLY))

And beware of integer division...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-24 : 06:14:57

Now when we know that range of USER_FRIENDLY, Charlie gave you another working solution in the first answer to this topic.

AVG(NULLIF([USER_FRIENDLY], 0))

However, I am not sure of the syntax for Open SQL.
Don't blame Charlie for this, he didn't know you were using a third party tool. It wasn't revealed for us until some postings back and forth.

N 56°04'39.26"
E 12°55'05.63"

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-24 : 06:24:54

quote:
Originally posted by Peso

Now when we know that range of USER_FRIENDLY, Charlie gave you another working solution in the first answer to this topic.
AVG(NULLIF([USER_FRIENDLY], 0))
However, I am not sure of the syntax for Open SQL.
Don't blame Charlie for this, he didn't know you were using a third party tool. It wasn't revealed for us until some postings back and forth.

N 56°04'39.26"
E 12°55'05.63"

Not blaming anyone for anything, we are having a conversation/debate and I am grateful that you are taking the time to help :)

Unfortunately NULLIF is not supported either. As a workaround I can do a simple SELECT and do the manipulations from the Java code but I really want to use this as last resort.

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 06:47:00

COALESCE available instead of NULLIF? (COALESCE is ANSI standard, so perhaps more likely to exist)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-24 : 07:00:21

Huh?? Something was said...... Not good.....?

Don't think there was any blame thrown around?

I think NULLIF *is* an open sql keyword but I'm not sure (taken from here : http://help.sap.com/saphelp_nw04/helpdata/en/40/b54ea14933458e96c736ec03295bfa/content.htm )

However I don't know how it's called / used.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-24 : 07:19:37

quote:
Originally posted by Transact Charlie

Huh?? Something was said...... Not good.....?

Don't think there was any blame thrown around?

I think NULLIF *is* an open sql keyword but I'm not sure (taken from here : http://help.sap.com/saphelp_nw04/helpdata/en/40/b54ea14933458e96c736ec03295bfa/content.htm )

However I don't know how it's called / used.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Thanks Charlie.
I am checking the NULLIF option in the SAP forum.

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-24 : 09:22:59

Here's the response I got there:

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

So it seems like the SAP Open SQL is very limited and I have no choive other than getting the data and go through it line by line.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-24 : 09:33:36

you could write one I guess.

A scalar function that returns NULL if the value is less than 1 or returns the value if 1 or greater.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-24 : 12:39:36

What about the suggestion made 09/24/2010 : 06:10:22 ?

N 56°04'39.26"
E 12°55'05.63"

royc
Yak Posting Veteran

53 Posts

Posted - 2010-09-24 : 12:46:13

SIGN doesn't work either there.
Open SQL sucks :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources