CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
SalesDB database with a view named SalesV. The SalesV view was created using the following
Transact-SQL code:
CREATE VIEW SalesDB.ProductsSalesV
AS
SELECT OrderID, ProductID, ShipDate, OrderDate, Amount
FROM SalesDB.Orders;
You want to create an inline table-valued function named fn_ABC that accepts a @ProductID
parameter of the integer data type. The inline table-valued function should also allow for sales
orders for each product to be listed by the latest sale.
How would you create this inline table-valued function?
To answer, type the correct code in the answer area.
Answer:
Explanation:
CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int )
RETURNS TABLE
AS
RETURN
(
SELECT OrderID, ProductID, ShipDate, OrderDate, AmountFROM Sales. ProductsSalesV
WHERE ProductID = @ProductID
ORDER BY OrderDate DESC
);
Has anyone actually tried to run the code given as the answer?
I get this error –
Msg 1033, Level 15, State 1, Procedure fn_test, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
If i take out the order by clause it works ok.
Any ideas please?
0
0
–Add top 100 percent
CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int )
RETURNS TABLE
AS
RETURN
(
SELECT top 100 percent
OrderID, ProductID, ShipDate, OrderDate, Amount
FROM Sales. ProductsSalesV
WHERE ProductID = @ProductID
ORDER BY OrderDate DESC
);
0
0
Yes, u are right
0
0
Gady answer is correct
0
0
The question asks for the most recent order for each product not allproducts in descending order . So when supply the variable info
Select * From SalesDB.fn_abc (2)
CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int )
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID, ProductID, ShipDate, Max(OrderDate) as MostRecentorder, Amount
FROM Sales. ProductsSalesV
WHERE ProductID = @ProductID
Group by OrderID, ProductID, ShipDate, Amount
)
0
0
BTW, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0