Nadeem Shafi (12/3/2007)

---

Thanks for the reply, but the problem still exists. For example, one of the orders that appear in the result should have 2 outputs (as shown below)

select orderdetails.orderid,...

Hey, I think it's pretty ... LOL 😀

Provided all of your return columns are of the same dataType, and I assume from their names they are. this will return the column depending on the parameter value passed...

Ok, what are the conditions for MYCOLUMN and what should/will be returned...

I mean, is it something like if a "1" is passed the first column is returned, "2" the second...

NO.... you won't have to replicate it.

Post it here and I'll show you....

duncan.williams (12/3/2007)

---

1) I didn't design the table - I am stuck with it!

2) I don't want to use dynamic SQL

3) Do I really have to do this 15 times in...

Provided each order detail has an entry in the products table, this will work.

SELECT

orders.orderid

,orders.orderdate

,orderdetails.productid

,laborderstatus.initialduedate

...

It's about time you starting talking my language. Well, second language. :w00t:

This is just a possible solution, but what if you aliased your table names? Like "table1 as a", "table2 as b". Could you work that into your solution by then...

or this might help ...

Move the sub query outside of the CTE definition.

WITH GetAllCategories

AS (SELECT

ROW_NUMBER() OVER (ORDER BY [Name] ASC)...

Jeff Moden (11/29/2007)

---

Heh... if you took 5 minutes out ahead of time to fix it, you...

Jeff, Matt and whomever else is reading this ....

I think all that happened here is something that I KNOW I have been guilty of ... "Answering a question without really...

Well if you're select a bunch of data and only the first name needs to be random for EACH record inserted. then change my query to read "TOP 1".

Otherwise, you'll...

So do you need this to run in 2000 AND 2005?

jreed,

It's difficult to really optimize the query without test data/tables, but here goes ...

First, I reformatted it a bit for ease of reading (at least for me).

Second, remove the DISTINCT...