Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting -802 error when using MOD function in where clause
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Getting -802 error when using MOD function in where clause
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 269
Location: Mumbai

PostPosted: Wed Sep 12, 2012 4:58 pm    Post subject:
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    Post subject:
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: 269
Location: Mumbai

PostPosted: Wed Sep 12, 2012 5:21 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Sep 12, 2012 6:48 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Sep 12, 2012 8:22 pm    Post subject: Reply to: Getting -802 error when using MOD function in wher
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    Post subject:
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

Site Director


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

PostPosted: Wed Sep 12, 2012 9:19 pm    Post subject: Reply to: Getting -802 error when using MOD function in wher
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    Post subject:
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: 269
Location: Mumbai

PostPosted: Thu Sep 13, 2012 9:27 am    Post subject:
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    Post subject:
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    Post subject: apology
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us