Hi everyone -

I am running asa7

something i thought would be simple has turned into the "query from hell"

I have a simple looping query...


BEGIN
DECLARE i INT ;
SET i = 1;
WHILE i <= 9 LOOP
if (select count(*) from tblSpecialFieldConfiguration where sfc_szname = 'Extra ' + cast(i as char)) = 0
begin
insert into tblSpecialFieldConfiguration
(
SFC_lfkdtkey,
sfc_lFieldNumber,
SFC_lSequence,
SFC_szName, SFC_boolDetailVisible, SFC_boolEditable, SFC_bytMaxLength, SFC_lChangeCtr, SFC_bytWorkQTranslate)
values
(
(select dt_lkey from tbldevicetype where dt_szcode like '%dcunit%'),
47 + i,
(select max(sfc_lsequence) + 20 from tblSpecialFieldConfiguration),
'Extra ' + cast(i as char),
1,
1,
20,
0,
0);
end;
SET i = i + 1;
END LOOP;
END;


That query runs fine...

NOW - i simply wanted to put a big if statement around
this loop using this query...


if (select count(*) from tbldevicetype where dt_szcode like '%dcunit%') = 1
BEGIN
////// query from above goes here....
END




Each part of the two step process works fine, seperately....

combined, they fail... error message reads....


line 29, column 1
Syntax error near if



any ideas would be appreciated...

thanks
tony