April 20, 2010 at 2:40 am
Hi,
This may seem like a strange question... but I'm not a SQL expert :S
We are implementing a server for monitoring and reporting (IBM ITM).
This includes some open reports that can be downloaded.
These reports in some cases has a statement with inside with the reserved word "WITH"
They seem to work on SQL 2005, but not in SQL 2000, is there a difference in how the with statement is interpreted in SQL2000 compared to 2005 and if so what is the difference?
//Martin
April 20, 2010 at 2:53 am
New to SQL2005 were Common Table Expressions (CTEs) which begin with the reserved word 'WITH' - these won;t work in SQL2000
There are many other ways that WITH can be used, but this is the main difference between 2005 and 2000.
If CTEs don't seem to fit with your reports, post an example, so we can give you a better explanation.
April 20, 2010 at 2:57 am
From SQL2005 the keyword WITH denotes a common table expression (CTE) that can be used in the query as an "inline view" to make code more readable or to achieve some looping (recursive CTE).
SQL2000 doesn't understand that syntax. You can convert the code into a subquery:
WITH CTEName AS (
SELECT SomeColumn
FROM SomeTable
)
SELECT *
FROM CTEName
translates to
SELECT *
FROM (
SELECT SomeColumn
FROM SomeTable
) AS CTEName
Hope this helps
Gianluca
-- Gianluca Sartori
April 20, 2010 at 4:48 am
Hi, thanks for the responses so far 🙂
This is the full statement.
with CPU_Util as
(
select "Server_Name" as CPU_SERVER, avg("AVG_%_Total_Processor_Time") as CPU_UTIL, "WRITETIME" as CPU_WRITETIME
from "NT_System_HV"
where "SHIFTPERIOD"= ? and "VACATIONPERIOD"= ?
and "WRITETIME" between '0000000000000000' and '9999999999999999'
and "Server_Name" = ?
group by "WRITETIME", "Server_Name"
),
Disk_Util as
(
select "Server_Name" as DISK_SERVER, avg("AVG_%_Used") as DISK_UTIL, "WRITETIME" as DISK_WRITETIME
from "NT_Logical_Disk_HV"
where "SHIFTPERIOD"= ? and "VACATIONPERIOD"= ? and "Disk_Name" = '_Total'
and "WRITETIME" between '0000000000000000' and '9999999999999999'
and "Server_Name" = ?
group by "WRITETIME", "Server_Name"
),
Memory_Util as
(
select "Server_Name" as MEM_SERVER, 100-avg("AVG_Available_Usage_Percentage") as MEM_UTIL, "WRITETIME" as MEM_WRITETIME
from "NT_Memory_HV"
where "SHIFTPERIOD"= ? and "VACATIONPERIOD"= ?
and "WRITETIME" between '0000000000000000' and '9999999999999999'
and "Server_Name" = ?
group by "WRITETIME", "Server_Name"
)
select CPU_Util.CPU_SERVER, CPU_Util.CPU_UTIL, Disk_Util.DISK_UTIL, Memory_Util.MEM_UTIL, CPU_Util.CPU_WRITETIME
from CPU_Util full outer join Disk_Util on CPU_Util.CPU_SERVER = Disk_Util.DISK_SERVER and
CPU_Util.CPU_WRITETIME = Disk_Util.DISK_WRITETIME
full outer join Memory_Util on Memory_Util.MEM_SERVER = CPU_Util.CPU_SERVER
and Memory_Util.MEM_WRITETIME = CPU_Util.CPU_WRITETIME
April 20, 2010 at 6:05 am
It should translate to this:
SELECT CPU_Util.CPU_SERVER,
CPU_Util.CPU_UTIL,
Disk_Util.DISK_UTIL,
Memory_Util.MEM_UTIL,
CPU_Util.CPU_WRITETIME
FROM (
SELECT Server_Name AS CPU_SERVER,
AVG(AVG_ % _Total_Processor_Time) AS CPU_UTIL,
WRITETIME AS CPU_WRITETIME
FROM NT_System_HV
WHERE SHIFTPERIOD = ?
AND VACATIONPERIOD = ?
AND WRITETIME BETWEEN '0000000000000000'
AND '9999999999999999'
AND Server_Name = ?
GROUP BY WRITETIME,
Server_Name
) AS CPU_Util
FULL OUTER JOIN (
SELECT Server_Name AS DISK_SERVER,
AVG(AVG_ % _Used) AS DISK_UTIL,
WRITETIME AS DISK_WRITETIME
FROM NT_Logical_Disk_HV
WHERE SHIFTPERIOD = ?
AND VACATIONPERIOD = ?
AND Disk_Name = '_Total'
AND WRITETIME BETWEEN '0000000000000000'
AND '9999999999999999'
AND Server_Name = ?
GROUP BY WRITETIME,
Server_Name
) AS Disk_Util
ON CPU_Util.CPU_SERVER = Disk_Util.DISK_SERVER
AND CPU_Util.CPU_WRITETIME = Disk_Util.DISK_WRITETIME
FULL OUTER JOIN (
SELECT Server_Name AS MEM_SERVER,
100 - AVG(AVG_Available_Usage_Percentage) AS MEM_UTIL,
WRITETIME AS MEM_WRITETIME
FROM NT_Memory_HV
WHERE SHIFTPERIOD = ?
AND VACATIONPERIOD = ?
AND WRITETIME BETWEEN '0000000000000000'
AND '9999999999999999'
AND Server_Name = ?
GROUP BY WRITETIME,
Server_Name
) AS Memory_Util
ON Memory_Util.MEM_SERVER = CPU_Util.CPU_SERVER
AND Memory_Util.MEM_WRITETIME = CPU_Util.CPU_WRITETIME
Question marks seem to come from a prepared statement in app code.
-- Gianluca Sartori
April 20, 2010 at 8:03 am
Hi,
thanks a lot. I will test it, but thanks for your efforts! :))
//Martin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply