Troubleshooting aggregate function

Question

Troubleshooting aggregate function

jvElecenter

SSCarpal Tunnel

Points: 4502
More actions
August 6, 2007 at 8:00 am

#117681

Hi
I have a stored procedure which calculates a result.
The data is fine, but the aggregate funtion does not work allthough i use the convert to int function (See green part)
Anyone got a clue ?
Start o f sp :
@CardNmbr
as varchar(9)
AS
BEGIN
SET NOCOUNT ON;
Declare
@Authorisationcode
as varchar(3),
@Result1
as Int,
@Result2
as Int,
@status
as varchar(2),
@day
as varchar(3)
Set @status = 99
SELECT @CardNmbr = CardNbr, @status = Status from IVR_EXXON_LOY where CardNbr = @CardNmbr
If @status = '12'
Begin
Set @CardNmbr = convert(int, @CardNmbr)
Set @Day = convert(int,datepart(dy, getdate()))
Set @Result1 = ((convert(int,substring(@Cardnmbr,1,2)) + convert(int,@day)) --> This should be 20 + 218 and the result is 20218 (so a concatenation of strings) +
3
* convert(int,(substring(@Cardnmbr,3,2)) + @day) +
7
* convert(int,(substring(@Cardnmbr,5,2)) + @day) +
13
* convert(int,(substring(@Cardnmbr,7,2)) + @day))
Set @Result2 = (@Result1 * (substring(@day,1,1) + substring(@day,2,1) + substring(@day,3,1) ))
Set @Authorisationcode = right(@Result2,3)
Set @status = 0
End

Thx in advance
El Jefe

JV

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

what value are you using for card number?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 2

Hi, sorry : it's 200031250

Here are the results when running the stored procedure :

Reset status

99

---------

Status voor select statement

12

-------

Calculation

20218 <---------------

654 <----------------

218526 <------------------

327834 <---------------------

Kaartnummer : 200031250

------------------------

Results :

Status : 0

Dag : 218

Authorisatie : 936

--------------------

Breakdown Cardnmbr

substring(@Cardnmbr,1,2) : 20

substring(@Cardnmbr,3,2) : 00

substring(@Cardnmbr,5,2) : 31

substring(@Cardnmbr,7,2) : 25

-----------------------

JV

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What would help even more is sample input data and wha the results of the process should return based on that output.

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 4

Hi

the green is the calculation method.

in my next reply the <-------------- are the results of this stored procedure

and they should be (20 + 218) , (3 * (0 + 218) )+ (7 * (31 + 218)) + (13 * (25+218))

20 (first two digits of the card + 218 day of the year)

+ 3 * (next two digits of the cardnr + 218)

+ 7 ...

etc...

Now it is 20+218 = 20218

Any clue ?

thx

JV

Amit Choudhary-216780 Old Hand Points: 384 More actions · Answer 5

At mine pc it giving 238 , that is perfect according to me :

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 6

jvElecenter

SSCarpal Tunnel

Points: 4502

August 6, 2007 at 9:03 am

#724862

Using just the data or the stored procedure ?

JV

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I am still not sure what your procedure is supposed to do. What is the input and what is the expected (not actual) output from the procedure?

Amit Choudhary-216780 Old Hand Points: 384 More actions · Answer 8

I m using this

Declare @status varchar(2),

@day varchar(3),

@Result1 Int,

@CardNmbr varchar(9)

Set @Day = convert(int,datepart(dy, getdate()))

select @CardNmbr = '200031250'

select @Result1 = ((convert(int,substring(@Cardnmbr,1,2)) + convert(int,@day)) )

select @Result1

Amit Choudhary

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 9

First of all, thnx for looking into this.

The purpose of this sp is to calculate a code depending on a cardnbr and date of today.

The formula is ok in the stored procedure, the only problem i have is that sql concatenates the results in stead of aggregate, e.g. :

((convert(int,substring(@Cardnmbr,1,2)) + convert(int,@day)) ) is 20 + 219, so it should be 239, but my result is 20219.

So the rest of the calculation is also not ok.

Thx

El Jefe

JV

Vladan SSC-Insane Points: 22072 More actions · Answer 10

Hi,

I don't understand it fully yet - will try to look into it later - but there is one thing that's strange:

First, you declare variables as VARCHAR

@CardNmbr as varchar(9)

Declare @day as varchar(3)

and then you convert the value to integer and insert it back into them:

Set @CardNmbr = convert(int, @CardNmbr)

Set @Day = convert(int,datepart(dy, getdate()))

Of course, because the variable is declared as character data, you can't insert integer into it, it always gets converted back to VARCHAR automatically. Therefore, all this is unnecessary and probably does not change the data (it could be a strange way of rounding, but both card number and number of day should be integers already).

Vladan SSC-Insane Points: 22072 More actions · Answer 11

When I cut out part of the code in green (+declarations), the result was what you consider correct result - i.e. addition occurred. If we are to find the source of problem, you will have to post table IVR_EXXON_LOY definition, some sample data, and the entire CREATE procedure statement as it is in your system.

It might be some typo... e.g. in your posted example of the procedure, a "+" is commented out by preceding "--> This should be 20 + 218 and the result is 20218 (so a concatenation of strings) ". I removed that comment and some offending brackets while testing the first two lines of the green code.

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 12

Hey guyz

I solved the problem.

Strange enough when i tear down the calculation it works just fine.

Many thx for helping me solving this.

JV