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

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 (codechar(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
MemduhIn 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" <mdurmaz@.derece.com.tr> wrote in message
news:e0c9cfd5.0408291350.31b8585a@.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|||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.)|||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)

Any significant differences between these two date CAST statements

I need to find records that have a date stamp of yesterday. I have created two where clauses that use CAST and DATETIME. Both seem to return the same results. Is either one better?

CAST(FLOOR(CAST(cc.Date AS float)) AS datetime) = CAST(FLOOR(CAST((getdate()-1) AS float)) AS datetime)

-- OR --

cast (round(cast(cc.Date as float),0,1) as datetime) = cast (round(cast((getdate()-1) as float),0,1) as datetime)

BTW: Oracle handles this easily as trunc(mydate)

Thanks in advance

Oh Using SQLServer 2005

Doug

www.cooltimbers.com

It looks to me like they will both work:

declare @.morningDt datetime set @.morningDt = '3/13/7 0:05'
declare @.afternoonDt datetime set @.afternoonDt = '3/13/7 23:59'

select cast(round(cast(@.morningDt as float),0,1) as datetime),
cast(round(cast(@.afternoonDt as float),0,1) as datetime),
round(cast(@.morningDt as float),0,1) ,
round(cast(@.afternoonDt as float),0,1)

-- -- - -
2007-03-13 00:00:00.000 2007-03-13 00:00:00.000 39152.0 39152.0

I am more used to seeing the FLOOR version.

|||

Thanks Kent for the prompt reply... and for your help in general!

I sure do wish Microsoft implements something similar to Oracles "TRUNC" function that nicely truncates to a day.

ANSI Nulls SQL Server Setting

Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI nulls
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,