Is there another way to do this, example provided

[beetlebailey]

This query was designed and works perfectly in MSSql 2000 but will ultimately be used in FrontPage. It works fine in FrontPage until the 5 lines containing variables (marked with --) are installed then it fails. Is there perhaps another way to accomplish the same results without using the variables ?

-- declare @T1 int, @T2 int, @T3 int --
-- set @T1 = (select top 1 tester from vpd where line = 451) --
-- set @T2 = (select top 1 tester from vpd where line = 451 and Tester <> @T1) --
-- set @T3 = (select top 1 tester from vpd where line = 451 and Tester <> @T1 and Tester <> @T2) --
Select sum(Hour1) as H1, sum(Hour2) as H2, sum(Hour3) as H3, sum(Hour4) as H4, sum(Hour5) as H5,
sum(Hour6) as H6, sum(Hour7) as H7, sum(Hour8) as H8, sum(Hour9) as H9, sum(Hour10) as H10,
sum(Fail1) as F1, sum(Fail2) as F2, sum(Fail3) as F3, sum(Fail4) as F4, sum(Fail5) as F5,
sum(Fail6) as F6, sum(Fail7) as F7, sum(Fail8) as F8, sum(Fail9) as F9, sum(Fail10) as F10,
sum(Hour1)-Tact1 as PM1, sum(Hour2)-Tact2 as PM2, sum(Hour3)-Tact3 as PM3, sum(Hour4)-Tact4 as PM4,
sum(Hour5)-Tact5 as PM5, sum(Hour6)-Tact6 as PM6, sum(Hour7)-Tact7 as PM7, sum(Hour8)-Tact8 as PM8,
sum(Hour9)-Tact9 as PM9, sum(Hour10)-Tact10 as PM10, Tact1, Tact2, Tact3, Tact4, Tact5, Tact6, Tact7,Tact8,
Tact9, Tact10, TactTotal, WeldSpeed, LineSpeed, Line, Com1, Com2, Com3, Com4, Com5, Com6, Com7, Com8, Com9, Com10,
ActualTotal, FailTotal, ActualTotal-TactTotal as PMTotal,
-- @T1 as Tester1, @T2 as Tester2, @T3 as Tester3, --
case when isnull(ActualTotal,0) = 0 then 0 else cast(FailTotal/(cast(ActualTotal as decimal(6,2))) as decimal(6,2))*1000000 end as PPMTotal,
case when isnull(TactTotal,0) = 0 then 0 else cast(ActualTotal/(cast(TactTotal as decimal(6,2)))as decimal(6,2))*100 end as PCTotal,
case when isnull(Tact1,0) = 0 then 0 else cast(sum(Hour1)/(cast(Tact1 as decimal(6,2))) as decimal(6,2))*100 end as PCT1,
case when isnull(Tact2,0) = 0 then 0 else cast(sum(Hour2)/(cast(Tact2 as decimal(6,2))) as decimal(6,2))*100 end as PCT2,
case when isnull(Tact3,0) = 0 then 0 else cast(sum(Hour3)/(cast(Tact3 as decimal(6,2))) as decimal(6,2))*100 end as PCT3,
case when isnull(Tact4,0) = 0 then 0 else cast(sum(Hour4)/(cast(Tact4 as decimal(6,2))) as decimal(6,2))*100 end as PCT4,
case when isnull(Tact5,0) = 0 then 0 else cast(sum(Hour5)/(cast(Tact5 as decimal(6,2))) as decimal(6,2))*100 end as PCT5,
case when isnull(Tact6,0) = 0 then 0 else cast(sum(Hour6)/(cast(Tact6 as decimal(6,2))) as decimal(6,2))*100 end as PCT6,
case when isnull(Tact7,0) = 0 then 0 else cast(sum(Hour7)/(cast(Tact7 as decimal(6,2))) as decimal(6,2))*100 end as PCT7,
case when isnull(Tact8,0) = 0 then 0 else cast(sum(Hour8)/(cast(Tact8 as decimal(6,2))) as decimal(6,2))*100 end as PCT8,
case when isnull(Tact9,0) = 0 then 0 else cast(sum(Hour9)/(cast(Tact9 as decimal(6,2))) as decimal(6,2))*100 end as PCT9,
case when isnull(Tact10,0) = 0 then 0 else cast(sum(Hour10)/(cast(Tact10 as decimal(6,2))) as decimal(6,2))*100 end as PCT10,
case when isnull(sum(Hour1),0) = 0 then 0 else cast(sum(Fail1)/(cast(sum(Hour1) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM1,
case when isnull(sum(Hour2),0) = 0 then 0 else cast(sum(Fail2)/(cast(sum(Hour2) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM2,
case when isnull(sum(Hour3),0) = 0 then 0 else cast(sum(Fail3)/(cast(sum(Hour3) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM3,
case when isnull(sum(Hour4),0) = 0 then 0 else cast(sum(Fail4)/(cast(sum(Hour4) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM4,
case when isnull(sum(Hour5),0) = 0 then 0 else cast(sum(Fail5)/(cast(sum(Hour5) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM5,
case when isnull(sum(Hour6),0) = 0 then 0 else cast(sum(Fail6)/(cast(sum(Hour6) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM6,
case when isnull(sum(Hour7),0) = 0 then 0 else cast(sum(Fail7)/(cast(sum(Hour7) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM7,
case when isnull(sum(Hour8),0) = 0 then 0 else cast(sum(Fail8)/(cast(sum(Hour8) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM8,
case when isnull(sum(Hour9),0) = 0 then 0 else cast(sum(Fail9)/(cast(sum(Hour9) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM9,
case when isnull(sum(Hour10),0) = 0 then 0 else cast(sum(Fail10)/(cast(sum(Hour10) as decimal(6,2))) as decimal(6,2))*1000000 end as PPM10
from vpd where line = 451
group by Tact1, Tact2, Tact3, Tact4, Tact5, Tact6, Tact7, Tact8, Tact9, Tact10, TactTotal, WeldSpeed, LineSpeed, Line,
Com1, Com2, Com3, Com4, Com5, Com6, Com7, Com8, Com9, Com10, Hour1, Hour2, Hour3, Hour4, Hour5, Hour6, Hour7, Hour8,
Hour9, Hour10, Fail1, Fail2, Fail3, Fail4, Fail5, Fail6, Fail7, Fail8, Fail9, Fail10, ActualTotal,FailTotal

Thanks for any assistance

 

[beetlebailey]

Simply put, this will not work in FrontPage. This works fine in MSSql 2000 but not in FrontPage 2000 DRW. Is there another way to pass these variables ?

declare @T1 int, @T2 int, @T3 int
set @T1 = (select top 1 tester from vpd where line = 451)
set @T2 = (select top 1 tester from vpd where line = 451 and Tester <> @T1)
set @T3 = (select top 1 tester from vpd where line = 451 and Tester <> @T1 and Tester <> @T2)
Select @T1 as Tester1, @T2 as Tester2, @T3 as Tester3

Thanks for any help.