February 25, 2002 at 2:15 pm
Please forgive us if we have frustrated you. We are doing our best to help you. I can't guarantee that the solution I proposed won't run as long as the other solution since I don't have a table as large as yours with which to test it on. If you are willing to try again the following will resolve the subquery returned more than one value error:
CREATE TABLE #report (siteid varchar(20), callid varchar(20))
DECLARE @SiteID varchar(30)
DECLARE csrSites CURSOR FOR
SELECT DISTINCT siteid
FROM billable_transactions
ORDER BY siteid
OPEN csrSites
FETCH NEXT FROM csrSites INTO @SiteID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #report (siteid, callid)
SELECT DISTINCT siteid, CASE WHEN (SELECT DISTINCT siteid FROM billable_transactions where callid = 'crew' AND siteid=@SiteID) IS NULL THEN 'Crew No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT DISTINCT siteid FROM billable_transactions where callid = 'Pax' AND siteid=@SiteID) IS NULL THEN 'Pax No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT DISTINCT siteid FROM billable_transactions where callid = 'Admin' AND siteid=@SiteID) IS NULL THEN 'Admin No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
FETCH NEXT FROM csrSites INTO @SiteID
END
SELECT *
FROM #report
CLOSE csrSites
DEALLOCATE csrSites
DROP TABLE #report
You'll notice the only difference is I added the keyword DISTINCT to the SELECT statement that comes after the CASE keyword.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 25, 2002 at 2:32 pm
one more thing I just thought of. If you want to see if this even has a chance of working for you, try putting a WHERE clause in the cursor declaration to limit the reslults to 2 or 3 sites. For example:
SELECT DISTINCT siteid
FROM billable_transactions
WHERE siteid IN ('Amsterdam', 'ARCADIA', 'CAPE MAYLIGHT ')
ORDER BY siteid
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 25, 2002 at 2:40 pm
Hey Robert
thank you my friend, this one work very good
and only take 01 minut and 30 seconds to run over 3million records...
Thank you very very much
Nelson
February 25, 2002 at 3:12 pm
You are welcome.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 26, 2002 at 7:35 am
Sorry about that, found the big gapping flaw in my code and created this updated version. I took the time and ran against my largest database (10,573,787 rows)
It took me 1:08 minutes on a dry run. Altough it is entirely up to you I still feel this would be better for your server for several reasons
1) You completely avoid the cursor.
2) No temp tables involved.
3) Can take full advantage as a stored procedure of the stored query plan.
4) Has fewer database reads (Only 2 occurr).
You have a working solution which is guaranteed in the other query. But this should give you the exact same results in about the same time or less and do it with less total server overhead.
Let me know if you try how it acts.
Note: If you want to make this into a stored procedure just remove the -- from in front of Create Procedure and name according to your requirements.
--CREATE PROCEDURE usp_SiteIDData
SELECT
BaseItems.siteid,
BaseItems.callid + (CASE
WHEN billable_transactions.Callid IS NULL THEN ' No Traffic'
ELSE ''
END) AS Callid
FROM
billable_transactions
RIGHT JOIN
(
SELECT siteid, callid FROM
(SELECT DISTINCT siteid FROM billable_transactions) AS DisVals
CROSS JOIN
(
SELECT 'pax' AS callid
UNION ALL
SELECT 'admin'
UNION ALL
SELECT 'crew'
) AS BaseList
) AS BaseItems
ON
billable_transactions.siteid = BaseItems.siteid AND
billable_transactions.Callid = BaseItems.callid
ORDER BY siteid, callid
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply