IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Getting -802 error when using MOD function in where clause


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sathiyendran.erulan

New User


Joined: 04 Mar 2009
Posts: 11
Location: chennai

PostPosted: Wed Sep 12, 2012 3:35 pm
Reply with quote

One of the field declared as decimal(9,0) called cust_nbr in table abc.
05 l-number-cnt pic s9(4) comp-4.

I need to get first or last four digits matched records from table abc. Example: when the user enters, 1234 , i need to get the cust_nbr which starting with 1234 and also ending with 1234.
Logic:
For getting the starting value, we can set the range that 123400000 to 123499999. This works fine.
For gettitng the ending value, we are using MOD function to match the ending value. MOD(cust_nbr, 10000)=1234

Query used:
select count(*) into l-number-cnt from abc where ( cust_nbr between 123400000 and 123499999
and MOD(cust_nbr, 10000)=1234 )
order by cust_name;

When i execute the query, i am getting -802 error. Reply me if you have any idea on this.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 4:01 pm
Reply with quote

well, other than this seems to be homework:

what if the customer number is 000123456?
does it qualify as starting with 1234?

and as far as the -802, you are assuming that all customer numbers are => 10000,
which means if the customer number is < 10000,
you will receive an -802 because of the decimal precision error
which would have been obvious, had you looked-up the error
and read the explanation.

is the customer number range really 10000000 to 999999999?
that would not be logical.

why not cast the customer number to char, and then do a substr compare?

also,
Code:
( cust_nbr between 123400000 and 123499999
and MOD(cust_nbr, 10000)=1234 )

this is BS: no need for the extra parenthesis.

and BTW,
we reply when we want to,
if we want to,
regardless of your request.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Sep 12, 2012 4:02 pm
Reply with quote

try increasing the size of variable l-number-cnt
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 4:04 pm
Reply with quote

gylbharat wrote:
try increasing the size of variable l-number-cnt
36_2_35.gif 36_2_35.gif 36_2_35.gif 36_2_35.gif 36_2_35.gif 36_2_35.gif 36_2_35.gif
Back to top
View user's profile Send private message
sathiyendran.erulan

New User


Joined: 04 Mar 2009
Posts: 11
Location: chennai

PostPosted: Wed Sep 12, 2012 4:37 pm
Reply with quote

cust number should not have leading zeros and always it have length 9 digits. So cust number will start from 100000000 and 999999999

If we remove MOD function, its working fine since there is possibility that
search range will produce 1 laksh records.

Also i have increased the count variable length to 10 and checked but it is now working
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 4:39 pm
Reply with quote

but do you understand why you received a -802?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 4:46 pm
Reply with quote

Quote:
cust number should not have leading zeros and always it have length 9 digits. So cust number will start from 100000000 and 999999999


easy way to find out if that statement is true, would be to
select min(cust_nbr), max(cust_nbr) from abc.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed Sep 12, 2012 4:58 pm
Reply with quote

Hi,

can you see what happens if you execute query like below

Code:
select  cust_nbr
   ,MOD(cust_nbr, 10000)
 from abc
where cust_nbr between 123400000 and 123499999


Does MOD(cust_nbr, 10000 gets proper value or some error?

if this query is working fine then you can change your WHERE clause accordingly


Regards,
Chandan
Back to top
View user's profile Send private message
sathiyendran.erulan

New User


Joined: 04 Mar 2009
Posts: 11
Location: chennai

PostPosted: Wed Sep 12, 2012 5:11 pm
Reply with quote

cust number should not have leading zeros and always it have length 9 digits. So cust number will start from 100000000 and 999999999

If we remove MOD function, its working fine since there is possibility that
search range will produce 1 laksh records.

Also i have increased the count variable length to 10 and checked but it is not working
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed Sep 12, 2012 5:21 pm
Reply with quote

DId you check the query to see how MOD(cust_nbr, 10000) is returned using query provided above?
Back to top
View user's profile Send private message
sathiyendran.erulan

New User


Joined: 04 Mar 2009
Posts: 11
Location: chennai

PostPosted: Wed Sep 12, 2012 6:13 pm
Reply with quote

The above produce the below results.
123412312. 2312.
123431232. 1232.
123441593. 1593.
123446887. 6887.
123454567. 4567.
123454567. 4567.
123456785. 6785.
123456789. 6789.
123469777. 9777.

Please let us know if there are any other way to find the last four digits values other than MOD.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 12, 2012 6:48 pm
Reply with quote

* even if cust_nbr is less then 10000, mod(cust_nbr,10000) will not give -802, so half of what was said above is bs.

*
Quote:
-802
EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number

if you would have examined the errormessage better you would have noticed that the exception-type, operation-type and position-number points to :l-number-cnt pic s9(4) comp-4. which can not hold "1 laksh"

All this mod() is just a red herring.
and anyone with basic math-skills knows that
cust_nbr - (int(cust_nbr / 10000) * 10000) = mod(cust_nbr,1000)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 7:29 pm
Reply with quote

Quote:
if you would have examined the errormessage better you would have noticed that the exception-type, operation-type and position-number points to :l-number-cnt pic s9(4) comp-4. which can not hold "1 laksh"


since we were never provided with the error message expansion,
i think that you are assuming.

I think the overflow (count) would have generated a +304.
an S9(9) comp host variable would handle any count returned.

unless of course, you have created a test table,
and run the sql.

instead of the mod function, a
where substr(char(col,9),1,4) = '1234' or substr(char(col,9),6,4) = '1234'
would suffice,

GuyC, your input is appreciated.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 12, 2012 8:22 pm
Reply with quote

Quote:
GuyC, your input is appreciated.
Mercy, such an understatement icon_cool.gif

d
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 12, 2012 8:39 pm
Reply with quote

I was not being sarcastic.

GuyC is normally right on.
In this case, I think he overlooked something.
But, if in the end he is right (as he normally is),
I will stand corrected.

And I wanted him to know by that comment,
that I did not take offense at his comments,
though I have to admit, the BS part did sting. icon_surprised.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 12, 2012 9:19 pm
Reply with quote

Hi Dick,

Well, that wasn't a "dig". Just one of those 'preciation thingys.

If it weren't for him and you and several others, this could be really tough duty . . . icon_wink.gif

d
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 13, 2012 1:16 am
Reply with quote

well, anyway,

my answer was based on db2's inclination to treat
truncation as a warning (SQLCODE > 0)
and
inaccuracy (loss of precision in this case) as an error (SQLCODE < 0)

and a suggestion:
ALWAYS
SELECT COUNT(*) INTO S9(9) COMP (< billion)
since COUNT returns a Big Integer
so S9(18) Comp if you could have > (1 billion - 1)
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Thu Sep 13, 2012 9:27 am
Reply with quote

Hi Dick,

Yesteday I ran this similar query in spufi for a column declared with Decimal(4,0) like below

Code:
SELECT COL1, COL2
   FROM TABLE A
WHERE MOD(COL2,10000) = 2011
Here COL2 is declared as D(4,0) and this query was returning expected output in spufi.

So it seems what GUYC is pointing is correct (As always icon_smile.gif )

But I am also not able to figure out why he is getting -802 for overflow and not +304.

I hope GuyC will have answer for this.

Regards,
Chandan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 13, 2012 3:04 pm
Reply with quote

as far as the MOD function.

I stand corrected, (that means i was wrong!)
i went off the deep end when I read the description,
and failed to understand how it worked.

GuyC even attempted to make it easier,
still I failed to grasp the significance.

the -802 was not raised by the MOD.

so, that could only mean the COUNT.

too employ a Liberal behavior that I often criticize:
I say
had the TS used a CALL to DSNTIAR
this would have been a non-issue.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 13, 2012 5:01 pm
Reply with quote

gylbharat,
My apologies
for my demeaning comment
on what was actually the solution.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Sep 13, 2012 6:22 pm
Reply with quote

its fine Dick... Its a forum for discussing and sharing knowledge...

While seeing your comment... I felt, that I am wrong. icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 14, 2012 1:27 pm
Reply with quote

When I said "If you examined", I meant the OP not dbz. Actually it's about the same as "had the TS used a CALL to DSNTIAR this would have been a non-issue."

The "BS" part was indeed a bit harsh, "off target" would have been better.

-802 is the error for (among others) OVERFLOW on an arithmetic operation done by DB2.
+304 is the warning for conversion while moving to a HV.
It's a fine line but I guess here it applies.

I didn't mean to be offensive to anyone, certainly not to long-time active forum members.
I have some issues In Real Life, my tolerance for stupidity has been reached so I'm taking my frustration to any error made. Sorry for that.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 14, 2012 2:12 pm
Reply with quote

GuyC,
I was clearly out-of-bounds and sorta kidding about the stinging BS.

I am much more critical and insensitive than you will ever be.
you have always conducted yourself as a gentleman.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts Calling an Open C library function in... CICS 1
No new posts DATE2 function SYNCSORT 15
Search our Forums:

Back to Top