what was the error you got after the UNION? On Jan 22, 2010, at 9:27 AM, Brian Humes wrote:
> Hi all, > > I have the following in a Direct DBMS action: > > //begin SQL > declare @phone_id int > declare @manu varchar(50) > declare @phone_name varchar(50) > declare @carrier_id int > declare @carrier varchar(50) > declare @rownum_phones int > declare phonelist cursor for > SELECT DISTINCT > b1.phone_id,b2.manufacturer,b1.phone_name,b3.carrier_id,c1.carrier FROM > dbo.bc_phone b1,dbo.bc_manufacturer b2,dbo.bc_pmc2 b3,dbo.bc_marketxdevice > b4, dbo.bc_carrier c1 WHERE ((b3.status1='approved1' OR b3.status1='failed1') > AND (b4.market_id=1) AND ((b1.manufacturer_id=b2.manufacturer_id) AND > (b3.phone_id=b1.phone_id) AND (b4.phone_id=b3.phone_id) AND (b3.carrier_id = > c1.carrier_id)) > open phonelist > fetch next from phonelist into > @phone_id,@manu,@phone_name,@carrier_id,@carrier > set @rownum_phones = 0 > while <@literal value="@@FETCH_STATUS"> = 0 > begin > set @rownum_phones = @rownum_phones + 1 > if (SELECT COUNT(b1.phone_id) FROM dbo.bc_pvc b1,dbo.bc_carrier > b2,dbo.bc_pmc2 b3 WHERE (b1.phone_...@phone_id AND b1.carrier_...@carrier_id) > AND ((b2.carrier_id=b1.carrier_id) AND (b3.phone_id=b1.phone_id))) = 0 > select @manu,@phone_name,@carrier > fetch next from phonelist into > @phone_id,@manu,@phone_name,@carrier_id,@carrier > end > close phonelist > deallocate phonelist > //end SQL > > The SQL call returns a list (phonelist) and then loops through that list to > find if some conditions are met. If so (if zero records are returned) then I > simply "print" the variables using a select call. So, Witango stores the > first return returned value, but no more. I need to get all of them. > > I tried putting in a UNION and UNION ALL beneath the "select > @manu,@phone_name,@carrier", but I got errors (presumably because I was > putting the UNION after the last cycle?). > > Any ideas on how I can get all of the results? By the way, I was doing this > using Witango (search, then loop, then test for zero, then addrows to an > array) but it was taking WAY too long. > > MS SQL Server 2005. > > Any help would be greatly, greatly appreciated. > > Thanks! > > > > > Brian Humes > Director, Interactive > JohnsonRauhoff > 269-428-9257 (direct) > 269-428-3377 (main) > 269-428-3312 (fax) > www.johnson-rauhoff.com > [email protected] > > > > > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
