Shrinking a very long SQL statement 1

[mrliam69]

Don't mock me as this statement works but added to the rest of the query I soon run out of space (not suprised) this is just a small snippet of the code but if someone can point me in the right direction to shrink it I would be eternally gratefull... I was hoping there would be some sort of DIM DECLARE type thing to call back on......

Here goes

SELECT
case ( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))
when 0 then 0
when 1 then 10
when 2 then 10
when 3 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 4 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 5 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 6 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 7 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 8 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 9 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
when 10 then
( ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0))*5-5
else 0
end
AS Bend4SetTotal
FROM TBL_Assembly_Data

For some reason I can't get > or < to work on this case statement...

 

[vongrunt]

Aren't cases 3-10 identical?

Try derived table; this will shrink code size a lot.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrliam69]

YEs I know they are indenticle but for some reason I can't get it to work when I say if it is > 3 then result

Do you have an example of a derived table ?

I was hoping in someway to declare the long isnull statements into a smaller more manageable one, but at a loss how too...

Regards
Liam

 

[vongrunt]

Here is general idea: use derived table to avoid long redundant code, and alternate CASE syntax to allow IN/BETWEEN stuff (> will also work):

select case when blah = 0 then 0
	when blah in (1, 2) then 10
	when blah between  3 and 10 then blah*5-5
	else 0
	end as Bend4SetTotal
	, someothercolumns
from
(	select 
  ISNULL(b4a01 / NULLIF (b4a01, 0.0), 0.0)
+ ISNULL(b4a02 / NULLIF (b4a02, 0.0), 0.0)
+ ISNULL(b4a03 / NULLIF (b4a03, 0.0), 0.0)
+ ISNULL(b4a04 / NULLIF (b4a04, 0.0), 0.0)
+ ISNULL(b4a05 / NULLIF (b4a05, 0.0), 0.0)
+ ISNULL(b4a06 / NULLIF (b4a06, 0.0), 0.0)
+ ISNULL(b4a07 / NULLIF (b4a07, 0.0), 0.0)
+ ISNULL(b4a08 / NULLIF (b4a08, 0.0), 0.0)
+ ISNULL(b4a09 / NULLIF (b4a09, 0.0), 0.0)
+ ISNULL(b4a10 / NULLIF (b4a10, 0.0), 0.0) as blah
	,someothercolumns
	FROM TBL_Assembly_Data
) X

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

On a side note, what is the purpose of dividing every b4a0% with itself?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrliam69]

I tell you what mate I don't care what the others say about you but that was bob on.... Cheers

 

[mrliam69]

The reason is

The table contains a list of bend angles and I need to find how many angles are greater than 0 for a later costing calculation so adding up the divisions will give me a count.... unless you know of a different way ?

 

[vongrunt]

Greater than 0?

Try SIGN() function - it returns 1 for greater than zero value...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrliam69]

Crikey your on a roll today , much appreciated....


Liam

 

[chrissie1]

I don't care what the others say about you

Now you made me curious.

What do they say about vongrunt?

Christiaan Baes
Belgium

"My new site" - Me

 

[mrliam69]

Dont worry about it Christiaan its just a British Sense of humour.....

Vongrunt you have been most helpful and I have used the sigh thing to reduce the size of the query but a couple more things if you don't mind...

How would I add Bend4SetTotal with Bend1SetTotal + Bend2SetTotal + Bend3SetTotal (field B4% is now b1% b2% b3%)

and what is the X at the end ?

 

[vongrunt]

> How would I add Bend4SetTotal with Bend1SetTotal + Bend2SetTotal + Bend3SetTotal (field B4% is now b1% b2% b3%)

Plz explain... I don't know what are Bend1SetTotal, Bend2Settotal etc...

> and what is the X at the end ?

Derived table is... table. And tables cannot be nameless, so you type in something (usually with some semantical value, not that X thing) otherwise server reports syntax error.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrliam69]

(select case when B4TOT = 0 then 0
when B4TOT in (1, 2) then 10
when B4TOT between 3 and 10 then B4TOT*5-5
else 0
end as Bend4SetTotal
from
(select sign(b4a01)+ sign(b4a02)+ sign(b4a03)+ sign(b4a04)+ sign(b4a05)
+ sign(b4a06)+ sign(b4a07)+ sign(b4a08)+ sign(b4a09)+ sign(b4a10) as B4TOT

FROM TBL_Assembly_Data
) x)

which returns

Bend4SetTotal
10
0
0

How would I add Bend3SetTotal to this using

(select case when B3TOT = 0 then 0
when B3TOT in (1, 2) then 10
when B3TOT between 3 and 10 then B3TOT*5-5
else 0
end as Bend3SetTotal
from
(select sign(b3a01)+ sign(b3a02)+ sign(b3a03)+ sign(b3a04)+ sign(b3a05)
+ sign(b3a06)+ sign(b3a07)+ sign(b3a08)+ sign(b3a09)+ sign(b3a10) as B3TOT

FROM TBL_Assembly_Data
) x)

resulting in

Bend3SetTotal Bend4SetTotal
10 15
10 10
15 15

 

[vongrunt]

Ack... Excel on roids .

I guess you can use one derived table for both results:

select 
	case when B3TOT = 0 then 0
		when B3TOT in (1, 2) then 10
		when B3TOT between  3 and 10 then B3TOT*5-5
		else 0
    end as Bend3SetTotal,
	case when B4TOT = 0 then 0
   	when B4TOT in (1, 2) then 10
   	when B4TOT between  3 and 10 then B4TOT*5-5
   	else 0
	end as Bend4SetTotal
from
(	select sign(b3a01)+ sign(b3a02)+ sign(b3a03)+ sign(b3a04)+ sign(b3a05)
		+ sign(b3a06)+ sign(b3a07)+ sign(b3a08)+ sign(b3a09)+ sign(b3a10) as B3TOT,
			sign(b4a01)+ sign(b4a02)+ sign(b4a03)+ sign(b4a04)+ sign(b4a05) 
		+ sign(b4a06)+ sign(b4a07)+ sign(b4a08)+ sign(b4a09)+ sign(b4a10) as B4TOT 
 
    FROM TBL_Assembly_Data
) X

To sum Bend3SetTotal and Bend4SetTotal...

select Bend3SetTotal, Bend3SetTotal + Bend4SetTotal
from
(	insert query from above
) X2

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)