April 21, 2011 at 4:32 am
Hello all,
I need to create a temporary table so as to do some calculation.
For example there is a table named Product having columns -ProductID, ProductName, Price and there is an another table say Order with columns - ProductID, Quantity, OrderDate.
Now I want the result-set as ProductId, ProductName,Price, and Amount with a particular date.
If I will use inner join, then it will produce only those records that are in the table Order for the specified date.
So I decided to use tempory table or table data type and then used IF EXISTS to get the desired result.
DECLARE @tbl_order TABLE (RowId INT IDENTITY (1,1), ProductID INT, ProductName nvarchar(250), Price FLOAT, Amount FLOAT).
If I use CTE in place of table data type then I am not able to add extra column (Amount) to the CTE.
WITH CTE_OrderDeatil (ProductID, ProductName, Price) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS RowId, P.ProductID, P.ProductName, Price, O.Quantity
FROM Product P INNER JOIN Order O ON P.ProductID = O.ProductID
WHERE OrderDate = @OrderDate
)
My question is can I add extra column say Amount to the CTE result as I have done in the table variable?
Thanks in advance!
April 21, 2011 at 4:42 am
v-anand (4/21/2011)
Hello all,I need to create a temporary table so as to do some calculation.
For example there is a table named Product having columns -ProductID, ProductName, Price and there is an another table say Order with columns - ProductID, Quantity, OrderDate.
Now I want the result-set as ProductId, ProductName,Price, and Amount with a particular date.
If I will use inner join, then it will produce only those records that are in the table Order for the specified date.
So I decided to use tempory table or table data type and then used IF EXISTS to get the desired result.
DECLARE @tbl_order TABLE (RowId INT IDENTITY (1,1), ProductID INT, ProductName nvarchar(250), Price FLOAT, Amount FLOAT).
If I use CTE in place of table data type then I am not able to add extra column (Amount) to the CTE.
WITH CTE_OrderDeatil (ProductID, ProductName, Price) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS RowId, P.ProductID, P.ProductName, Price, O.Quantity
FROM Product P INNER JOIN Order O ON P.ProductID = O.ProductID
WHERE OrderDate = @OrderDate
)
My question is can I add extra column say Amount to the CTE result as I have done in the table variable?
Thanks in advance!
It's quite difficult to tell what you are trying to achieve here. Have you tried left-joining the order table to the product table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 5:06 am
Have you tried?
If so post DML and error plus DDL and test data
Far away is close at hand in the images of elsewhere.
Anon.
April 21, 2011 at 5:19 am
I'm more than a bit list too. What error are you getting when you add the column to the CTE. Yes, you should be able to add a column to a CTE. You added columns to it to begin with.
I agree, it sounds like you just need a LEFT JOIN.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply