Oracle/SQL/Decode question

[sleestak]

I am trying to come up with a single SQL statement that will print out a list of addresses grouped by even and odd addresses. My DB has field called house_number which is a VARCHAR2, so I am using the last character of that field to determine if it is even/odd. Unfortunately, the last character is not always a digit, so the mod function won't work. BTW, this is in Oracle 8. What I have right now is:<br>
<br>
select ALL count(*), decode(mod(substr(house_number,-1),2),0,'even','odd') even_odd, house_number from member where member_id&gt;589990 group by decode(mod(substr(house_number,-1),2),0,'even','odd'), house_number;<br>
<br>
There are two problems with this:<br>
1) The mod problem mentioned above.<br>
2) Even though, I specified ALL...it is really doing a DISTINCT in that like house numbers are being grouped together.<br>
<br>
Any suggestions would be helpful.

 

[appcoder]

1. The following decode will correctly determine whether the last character of House_Number is even, odd, or alphabetic:<br>
<br>
decode(mod(<br>
(to_number<br>
(decode<br>
(substr(house_number,-1),<br>
'1','1',<br>
'2','2',<br>
'3','3',<br>
'4','4',<br>
'5','5',<br>
'6','6',<br>
'7','7',<br>
'8','8',<br>
'9','9',<br>
'0','0',<br>
'-1')<br>
)<br>
),<br>
2),<br>
-1, 'alpha',<br>
0, 'even',<br>
'odd')<br>
<br>
2. Get rid of your &quot;group by&quot; clause and COUNT(*).