View previous topic :: View next topic
|
Author |
Message |
sathiyendran.erulan
New User
Joined: 04 Mar 2009 Posts: 11 Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
try increasing the size of variable l-number-cnt |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
gylbharat wrote: |
try increasing the size of variable l-number-cnt |
|
|
Back to top |
|
|
sathiyendran.erulan
New User
Joined: 04 Mar 2009 Posts: 11 Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
but do you understand why you received a -802? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 |
|
|
sathiyendran.erulan
New User
Joined: 04 Mar 2009 Posts: 11 Location: chennai
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
DId you check the query to see how MOD(cust_nbr, 10000) is returned using query provided above? |
|
Back to top |
|
|
sathiyendran.erulan
New User
Joined: 04 Mar 2009 Posts: 11 Location: chennai
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
* 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Quote: |
GuyC, your input is appreciated. |
Mercy, such an understatement
d |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 . . .
d |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 )
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
gylbharat,
My apologies
for my demeaning comment
on what was actually the solution. |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
its fine Dick... Its a forum for discussing and sharing knowledge...
While seeing your comment... I felt, that I am wrong. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|