Any structure on Sql Server like WITH ... SELECT structure on DB2

[Memduh Durmaz]

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

.............................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (code char(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
Memduh

[Dan Guzman]

In SQL Server, you can use SELECT ... INTO to create a table using a SELECT
query as the source for schema and data. Untested example:

SELECT
code,
SUM(amount)
INTO #totals
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code

SELECT
code,
SUM(amount)
FROM #totals
GROUP BY code

You can also produce the result using a derived table instead of a temp
table:

SELECT
code,
SUM(amount)
FROM
(
SELECT
code,
SUM(amount) AS amount
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code
) AS totals
GROUP BY code

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Memduh Durmaz" <md*****@derece.com.tr> wrote in message
news:e0**************************@posting.google.c om...

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

............................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (code char(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
Memduh

[Ross Presser]

On 29 Aug 2004 14:50:25 -0700, Memduh Durmaz wrote:

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

You can always use a derived table:

SELECT code, SUM(amount)
FROM (
SELECT code, SUM(amount) FROM trans1 GROUP BY CODE
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY CODE
) AS TOTALS
GROUP BY CODE

(the "AS" in "AS TOTALS" is optional, and is often omitted.)

[Gert-Jan Strik]

SQL-Server 2005 will support Common Table Expressions (the WITH ...
SELECT stuff). If you don't want to wait a year, then you would have to
work around in SQL-Server 2000, or get hold of a beta-version.

HTH,
Gert-Jan
Memduh Durmaz wrote:

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

............................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (code char(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
Memduh

--
(Please reply only to the newsgroup)