CTE Constant

Question

Post reply

CTE Constant

josemyj

Mr or Mrs. 500

Points: 555
More actions
December 30, 2008 at 2:50 am

#383655

Hi,
I want to do the same i do with DB2:
-- Is a example. Need for multiple Joins...
with
(
values(1), values (2), values (3), ... values (n)
) as CTETable
select * from CTETable;
And i get this table:
Field
-----
1
2
3
...
n
¿What's the good way to do it with SQL Server?
¿What do you think about this?
;with CTETable (Field) as
(
Select 1 union all Select 2 union all Select 3 ... union all Select n)
)
select * from CTETable;

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

NitinPatel31

Hall of Fame

Points: 3721

December 30, 2008 at 2:55 am

#918886

Use UNPIVOT statement for this.

Regards,
Nitin

josemyj Mr or Mrs. 500 Points: 555 More actions · Answer 2

Do no seem to me a very elegant way to do it in comparison to DB2 but is a good start point:

As more fields, more alias...

select pvt.Vals from

(

select 1 as field1 ,2 as field2 ,3 as field3 ,4 as field4 ,5 as field5 ,6 as field6 ,7 as field7

) as p

unpivot

(

Vals for numbers in (field1, field2, field3, field4, field5, field6, field7 )

) pvt

Thanks.

But, Has anyone another more elegant option?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

josemyj (12/30/2008)
¿What do you think about this?
;with CTETable (Field) as
(
Select 1 union all Select 2 union all Select 3 ... union all Select n)
)
select * from CTETable;

This looks fine. Can you use a derived table?

SELECT *

FROM (SELECT CAST(1 AS INT) AS Field UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 999) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

josemyj Mr or Mrs. 500 Points: 555 More actions · Answer 4

Is a good aproach, but keeps a very big SQL when we reach the 2000 fields.

The problem: Users have a list of their shops, and choose between 4000 arbitrarily. No subtable with the data previous to the selection. In previous version over SQL 2000 they had INs with 2000 items. Not a good performance choice.

In DB2 it solved with the CTE and the value. DBM chooses to order the temporary table to use a merge join for better performance. In SQL Server it is proposed to be done with temp tables. Indexing if needed, but i wanted to propose it in a Common table expression. Just like in DB2 for keeping the same approach.

the union all aproach is more troublesome for reading than the unpivot

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

So...you need a simple list of integers, from 1 to about 2000? Is there anything else?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

josemyj Mr or Mrs. 500 Points: 555 More actions · Answer 6

No.

Is not a simple list of integers, is a selection of 2000 primary keys over 4000 primary keys non consecutive and arbitrary from a table, in an arbitrary mode (too) for selection (with many other fields) in a 2 thousand millions rows table.

We initially recomended the use of local temporary table for the 2000 keys to go over inner join with the sales table. Now we want to follow the db2 approach of using CTEs in SQL Server. But with union all is very ugly. And the UNPIVOT option needs an alias for each field. Only looking for other options.

And sorry if my english is confusing, not native language.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 7

josemyj (12/30/2008)
Is a good aproach, but keeps a very big SQL when we reach the 2000 fields.
The problem: Users have a list of their shops, and choose between 4000 arbitrarily. No subtable with the data previous to the selection. In previous version over SQL 2000 they had INs with 2000 items. Not a good performance choice.
In DB2 it solved with the CTE and the value. DBM chooses to order the temporary table to use a merge join for better performance. In SQL Server it is proposed to be done with temp tables. Indexing if needed, but i wanted to propose it in a Common table expression. Just like in DB2 for keeping the same approach.
the union all aproach is more troublesome for reading than the unpivot

You're saying that you allowed a table to have 4000 columns in DB2? SQL Server has a max capacity of only 1,024 columns in a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

josemyj (12/30/2008)
And sorry if my english is confusing, not native language.

No need to apologise, we'll get there.

Your first post said you wanted a temporary table or CTE to provide consecutive integers, 1, 2, 3, ...n

There are several good ways to do this.

But your last post says "a selection of 2000 primary keys". This isn't the same as consecutive integers.

Can you tell us in more detail how your table/CTE should best be populated?

I'm being a complete idiot here and will stand in a corner for a while.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

josemyj Mr or Mrs. 500 Points: 555 More actions · Answer 9

I didnt said they were consecutive, it was just an example. I have a 4000 rows table whose primary keys are used for filtering that way.

No 4000 columns... Only rows.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 10

Perhaps it's time to post what the tables look like and what the data looks like. Please see the link in my sigature below for how to get the best results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

josemyj (12/30/2008)
I didnt said they were consecutive, it was just an example. I have a 4000 rows table whose primary keys are used for filtering that way.
No 4000 columns... Only rows.

Sorry for the misunderstanding!

Ok, so you take a sample of the 4000 rows, say 2000, and then use this sample to filter a larger data set?

Why can you not use a derived table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 12

Is this some sort of paging that you are trying to do?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

josemyj Mr or Mrs. 500 Points: 555 More actions · Answer 13

Is no paging, simple:

I have 2 Tables: First one of 4000 rows. Users use them to filter the second table, much bigger.

They choose, almost randomly many of these rows to filter from 20 to more than a thousand.

Now is working with temporary tables this way:

Create table #Data

(

PrimaryKey int

)

-- The values are arbitrary (for us random choices from the primary key of the table used for filtering)

Insert into table values ( 1)

Insert into table values (24)

Insert into table values (1200)

.

-- Then they search:

Select Field1, Field2 ... etc

From DataTableOfSales DTOS

inner join #Data filter

on DTOS.shop = filter.PrimaryKey

Im only asking if someones uses a more elegant way throug Common Table Expresions. In DB2 we do it this way:

With filter

(values(1), values(24), values (1200)... )

Select Field1, Field2 ... etc

From DataTableOfSales DTOS

inner join filter

on DTOS.shop = filter.PrimaryKey;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

How does SQL Server get these values? One at a time? As a file/cursor?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden