you can use UNION on a sql select statement that is a searching select
and not a variable select
select @menu,@... is a variable select and in the directDBMS you are
actually creating a Stored Procedure on the fly
the union is designed to be used when you have to select statements
the return the exact same fields but have different where clauses
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
Ben Johansen
http://www.webspinr.com
[email protected]
Phone: 360-597-3372
Mobile: 360-600-7775
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
