February 4, 2013 at 11:00 am
I have this code that is not giving any results but should give some results with input 1, but it gives nothingalter procedure dbo.OrdersLookupByWildOrderID
@WildOrderID varchar(15)
set nocount on
create table #tmpOrderHistory
(
OriginalOrderID int
, OrderID int
, CreatedDtm datetime
, MDN varchar (15)
, Type varchar (20)
, OrderTypeCD int
, AccountNumber varchar (32)
, Entity varchar (32)
, AccountLast4 varchar (4)
, AccountType varchar (30)
, FirstName varchar (20)
, LastName varchar (20)
, AddressLine1 varchar (30)
, AddressLine2 varchar (30)
, City varchar (30)
, State varchar (30)
, PostalCode varchar (12)
, OrderStatus varchar (64)
, OrderStatusCD int
, Channel varchar (30)
, TotalAmt money
, POSSalesID varchar (255)
,RefundOrderIDint
,RefundOrderStatusCDint
)
-- add all the orders into the temp table
insert into #tmpOrderHistory
select distinct top 100
OriginalOrderID = o.OrderID
, OrderID = o.OrderID
, CreatedDtm = dbo.DateTimeToISOString(o.CreatedDtm)
, MDN = sva.MDN
, Type = ot.Name
, OrderTypeCD = o.OrderTypeCD
, AccountNumber = sva.SubscriberKey
, Entity = pcc.Name
, AccountLast4 = COALESCE(pdc.AccountNumberLast4, pdec.AccountNumberLast4)
, AccountType = COALESCE(CASE WHEN pdct.Name = 'Unknown' THEN 'Debit' ELSE pdct.Name END, pdect.Name)
, FirstName = na.FirstName
, LastName = na.LastName
, AddressLine1 = a.AddressLine1
, AddressLine2 = a.AddressLine2
, City = a.City
, State = a.Region
, PostalCode = a.PostalCode
,OrderStatus = case when orev.OrderID is not null and coalesce(o_rev.OrderStatusCD,'') = 10 then 'Refunded'
when orev.OrderID is not null and coalesce(o_rev.OrderStatusCD,'') <> 10 then 'Refund Failed'
else os.Name
end
, OrderStatusCD = o.OrderStatusCD
, Channel = c.Name
, TotalAmt = op.Amount
, POSSalesID = ptd.POSSalesID
,RefundOrderID=orev.OrderID
,RefundOrderStatusCD=o_rev.OrderStatusCD
from Orders o (nolock)
join OrderT ot (nolock) on o.OrderTypeCD = ot.OrderTypeCD
join Chanel c (nolock) on c.ChannelCD = o.ChannelCD
join OrderStat os (nolock) on os.OrderStatusCD = o.OrderStatusCD
join LineItem li (nolock) on li.OrderID = o.OrderID
join PurchaseItem pis (nolock) on pis.PurchaseItemID = li.PurchaseItemID
join StoreValAcc sva (nolock) on sva.SVAID = pis.SVAID
join OrderPay op (nolock) on op.OrderID = o.OrderID
join NameAdd na (nolock) on na.NameAddressID = op.NameAddressID
join Address a (nolock) on a.AddressID = na.AddressID
left join PartTaxSumm ts (nolock) on ts.LineItemID = li.LineItemID
left join PayDevCharge pdc (nolock) on pdc.PaymentDeviceID = op.PaymentDeviceID
left join PayDevChargeType pdct (nolock) on pdct.PayDevChargeTypeCD = pdc.PayDevChargeTypeCD
left join PayDevCheck pdec (nolock) on pdec.PayDevID = op.PayDevID
left join PayDevCheckType pdect (nolock) on pdect.PayDevCheckTypeCD = pdec.PayDevCheckTypeCD
join Product p (nolock) on p.ProductID = li.ProductID
join PartCompanyCode pcc (nolock) on pcc.CompanyCode = sva.CompanyCode
left join PartTrackingData ptd (nolock) on ptd.OrderID = o.OrderID
left join OrderReversal orev (nolock) on o.OrderID = orev.OriginalOrderID
left joinOrders o_rev (nolock) on o_rev.OrderID= orev.OrderID
where CAST(o.OrderID AS varchar(15)) like @WildOrderID
and o.OrderTypeCD = 1 -- Topup
and p.ProductTypeCD in (1, 2, 4, 6, 7)
and o.OrderStatusCD in (1,3,10,90)
go
grant execute on dbo.OrdersLookupByWildOrderID to db_ProcExecuter
go.
Running the same query from a SQL window with condition where CAST(o.OrderID AS varchar(15)) like '%1%', I get 4 rows of data. I do not know what is wrong with this query. I have tried putting where condition like where CAST(o.OrderID AS varchar(15)) like '%' + @WildOrderID '%' but that gives me a Msg 102, Level 15, State 1, syntax error near '%'. PLEASE HELP. I have also tried making @WildOrderID an int without success
February 4, 2013 at 11:34 am
just missing your concatenation:
where CAST(o.OrderID AS varchar(15)) like '%' + @WildOrderID '%'
--should be
where CAST(o.OrderID AS varchar(15)) like '%' + @WildOrderID + '%' --<-- missing plus sign to concatenate the trailing percent sign
Lowell
February 4, 2013 at 12:11 pm
And be VERY careful with all those NOLOCK hints. Do you know the dangers that hints brings along? Missing and/or duplicate data? It may be fast but accuracy is impossible and you can introduce bugs that are nearly impossible to reproduce.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2013 at 3:47 pm
Thanks all for your responses, i got this to work. Thanks:-)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply