error in select query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error in select query

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.creditnumber
else replace(r.creditnumber,substring(r.creditnumber,1,len(r.creditnumber)-4),'xxxx xxxx xxxx
')
end as creditcard from creditcardtable r

join salestable s

on s.salesdate = r.creditdate

where (r.creditnumber <> '') AND (NOT (ISNULL(r.creditnumber, '999') = '999'))

order by r.creditdate desc

first 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: count

can 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 Athalye
India.
"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.creditnumber
else replace(r.creditnumber,substring(r.creditnumber,1,len(r.creditnumber)-4),'xxxx xxxx xxxx
')
end as creditcard from creditcardtable r

join salestable s
on s.salesdate = r.creditdate

where (r.creditnumber <> '') AND (NOT (ISNULL(r.creditnumber, '999') = '999'))

order by r.creditdate desc

can 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 DATETIME

SELECT @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 CreditCard
FROM CreditCardTable AS cct
INNER JOIN SalesTable AS st ON st.SalesDate = cct.CreditDate
WHERE cct.CreditNumber > ''
ORDER BY cct.CreditDate DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources