Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Shrinking a very long SQL statement 1

Status
Not open for further replies.

mrliam69

Programmer
Joined
Jul 21, 2003
Messages
75
Location
GB
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...
 
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)
[banghead]
 
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
 
Here is general idea: use derived table to avoid long redundant code, and alternate CASE syntax to allow IN/BETWEEN stuff (> will also work):
Code:
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)
[banghead]
 
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)
[banghead]
 
I tell you what mate I don't care what the others say about you but that was bob on.... Cheers
 
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 ?
 
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)
[banghead]
 
Crikey your on a roll today , much appreciated....


Liam
 
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 ?





 
> 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)
[banghead]
 
(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

 
Ack... Excel on roids [smile].

I guess you can use one derived table for both results:
Code:
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...
Code:
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)
[banghead]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top