Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mkool
Starting Member
25 Posts |
Posted - 2008-04-15 : 10:20:58
|
i have select query:select distinct s.stype, s.snumber, r.custnumber, r.custname, r.creditdate, case when (r.creditdate >= dateAdd(m, -3, convert(datetime,convert(varchar(10), getdate(), 112), 112))AND r.creditdate < dateAdd(d, 1, convert(datetime,convert(varchar(10), getdate(), 112), 112))) then r.creditnumberelse replace(r.creditnumber,substring(r.creditnumber,1,len(r.creditnumber)-4),'xxxx xxxx xxxx') end as creditcard from creditcardtable rjoin salestable son s.salesdate = r.creditdatewhere (r.creditnumber <> '') AND (NOT (ISNULL(r.creditnumber, '999') = '999')) order by r.creditdate descfirst time i execute this query i got an error like:An error occurred while executing batch. Error message is: Internal connection fatal error.second time i execute this query i got an error like:An error occurred while executing batch. Error message is: Non-negative number required.Parameter name: countcan anyone tell me what's the reason? and any solution for it?thanks. |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-15 : 10:26:23
|
I guess some of the values in your creditnumber column are less than 4 characters.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
mkool
Starting Member
25 Posts |
Posted - 2008-04-15 : 10:36:18
|
well, thanks for replying harsh,i checked so many times and there is no less than 4characters creditcardnumbers. all have either null or 16characters.also,well, my query is :i have to replace or convert creditcards that is older than 3 months to the XXXX XXXX XXXX 4321 - format.and on the date of the most recent order as opposed to the create date of the customer. The reason is that we have repeat customers.i have to create sproc against creditcardtable that use the most recent order date for the customer as the last modified date. so i did like this: select distinct s.stype, s.snumber, r.custnumber, r.custname, r.creditdate, case when (r.creditdate >= dateAdd(m, -3, convert(datetime,convert(varchar(10), getdate(), 112), 112))AND r.creditdate < dateAdd(d, 1, convert(datetime,convert(varchar(10), getdate(), 112), 112))) then r.creditnumberelse replace(r.creditnumber,substring(r.creditnumber,1,len(r.creditnumber)-4),'xxxx xxxx xxxx') end as creditcard from creditcardtable rjoin salestable son s.salesdate = r.creditdatewhere (r.creditnumber <> '') AND (NOT (ISNULL(r.creditnumber, '999') = '999')) order by r.creditdate desccan anyone tell me my query is correct with this scenario or there is problem in query itself?can anyone try to help me?thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 10:42:08
|
[code]DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = DATEDIFF(DAY, '19000101', DATEADD(MONTH, -3, GETDATE())), @ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')SELECT DISTINCT st.sType, st.sNumber, cct.CustNumber, cct.CustName, cct.CreditDate, CASE WHEN CreditDate >= @FromDate AND CreditDate < @ToDate THEN CreditNumber ELSE 'xxxx xxxx xxxx ' + RIGHT(CreditNumber, 4) END AS CreditCardFROM CreditCardTable AS cctINNER JOIN SalesTable AS st ON st.SalesDate = cct.CreditDateWHERE cct.CreditNumber > ''ORDER BY cct.CreditDate DESC[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|