January 13, 2012 at 4:09 pm
I need to use this statement below that I need to create a view. How can I do this with Variables in the Subquery)? I can put the where clause outisde of the view itself, but I am not sure this will work with a subquery. Any thoughts?
select le.*
from ORR_LINEITEM_EX le
join ORR_LINEITEM l on le.OrderLineItemId = l.OrderLineItemId
join ORRS o on l.OrderId = o.OrderId
join ORR_LINEITEM_ACK ack on le.OrderLineItemId = ack.OrderLineItemId
join ORR_LINEITEM_ACK_STAT ackstat on ack.AcknowledgementStatusId = ackstat.AcknowledgementStatusId
where o.OrderId in (SELECT distinct O.OrderID
FROM ORDERS O WITH (NOLOCK)
JOIN ORDER_LINEITEM OL WITH (NOLOCK) ON O.OrderId=OL.OrderId
JOIN ORDER_LINEITEM_EXCEPTION OLE WITH (NOLOCK) ON OL.OrderLineItemId=OLE.OrderLineItemId
JOIN ORDER_LINEITEM_EXCEPTION_TYPE OLET WITH (NOLOCK) ON OLET.ExceptionTypeId=OLE.ExceptionTypeId
join snp_member sm on sm.MemberId=o.MemberId
left join dbo.MEMBER_GROUP_LOOKUP AS MGL ON sM.SF_Account_Id = MGL.SF_Account_Id
where O.OrderReceivedDate >=dateadd(m,-6,getdate())
and OLE.WorkedTimeStamp is NULL
and (O.BuyerName in ('Bob BUSCHBHER', 'MELVIN CHRIST' , 'Charlie SOPHIA')) -- Variable
and O.OrderReceivedDate >= '2011-12-29T00:00:00.00' /* @p1 */ -- variable
and (sm.MedicalID in ('135357' /* @p7 */))) -- Variable
and ExceptionTypeId in (5)
and WorkedByUserId is null
and ackstat.AcknowledgementStatusId in (6)
January 13, 2012 at 4:18 pm
View no, in-line table-valued function, yes. Views can't have variables of any form.
Also, btw... http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 9:14 pm
This is the outline of a solution:
CREATE FUNCTION dbo.NameOfFunction
(
@Buyers varchar(100),
@ReceivedDate date,
@MedicalID integer
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
LE.* -- expand this wildcard to a list of column names
FROM dbo.ORR_LINEITEM_EX AS LE
JOIN dbo.ORR_LINEITEM AS L ON
L.OrderLineItemId = LE.OrderLineItemId
JOIN dbo.ORRS AS O ON
OrderId = L.OrderId
JOIN dbo.ORR_LINEITEM_ACK AS ack ON
ack.OrderLineItemId = LE.OrderLineItemId
JOIN dbo.ORR_LINEITEM_ACK_STAT AS ackstat ON
ackstat.AcknowledgementStatusId = ack.AcknowledgementStatusId
JOIN
(
SELECT
O.OrderID
-- dbo.Split is your favourite in-line
-- string-splitting function
FROM dbo.Split(@Buyers, ',') AS B
JOIN dbo.ORDERS AS O
ON O.BuyerName = B.item
JOIN dbo.ORDER_LINEITEM AS OL ON
OL.OrderId = O.OrderId
JOIN dbo.ORDER_LINEITEM_EXCEPTION AS OLE ON
OLE.OrderLineItemId = OL.OrderLineItemId
JOIN dbo.ORDER_LINEITEM_EXCEPTION_TYPE AS OLET ON
OLET.ExceptionTypeId = OLE.ExceptionTypeId
JOIN dbo.snp_member AS SM ON
SM.MemberId = O.MemberId
LEFT JOIN dbo.MEMBER_GROUP_LOOKUP AS MGL ON
MGL.SF_Account_Id = SM.SF_Account_Id
WHERE
O.OrderReceivedDate >= DATEADD(MONTH, -6, GETDATE())
AND OLE.WorkedTimeStamp IS NULL
AND O.OrderReceivedDate >= @ReceivedDate
AND SM.MedicalID = @MedicalID
AND OLET.ExceptionTypeId = 5
AND MGL.WorkedByUserId IS NULL
AND ACKSTAT.AcknowledgementStatusId = 6
) AS Q ON
Q.OrderId = O.OrderId
January 14, 2012 at 12:09 pm
GilaMonster (1/13/2012)
Also, btw... http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Thank you for that link.
The discussion following that article is excellent as well.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply