Informix 12.10.FC14
Solaris 10 1/13
I have a table with a smart blob column. (It is a photo.) I am interested in determining the total number of photos, and also the number of unique photos.
To determine the total number of photos, I did the following:
SELECT * FROM <table> WHERE <photo> IS NOT NULL;
There are 22 such rows. (This is a small table for testing purposes.)
I also did the following query (because this is how I would do it on a large table):
SELECT COUNT( <photo> ) WHERE <photo> IS NOT NULL;
Same answer: 22.
To determine the number of unique photos, I first thought to use ifx_checksum(), figuring I am not absolutely, positively guaranteed that results would be unique, but I am comfortable that "almost certainly" is good enough for present purposes.
So, I did this:
SELECT ifx_checksum( <photo>, 0) FROM <table> WHERE <photo> IS NOT NULL;
The table is small, and I got back the 22 rows for all the existing photos. By inspection, I observed that there were 22 photos, and 21 distinct photos (as determined by the ifx_checksum hash, anyway).
But, then, I thought some more, and figured that the LO Handle would likely be close enough for what I want to do, and probably would be less computational work than executing ifx_checksum() for every row in a very large table.
So, I did these two commands:
SELECT <photo> FROM <table> WHERE <photo> IS NOT NULL;
SELECT DISTINCT <photo> FROM <table> WHERE <photo> IS NOT NULL; (highlight explained later)
As I expected, I got 22 rows, and 21 rows from those two queries, respectively.
Then I figured (for a large table) better to just COUNT the rows-- not necessary to bring them all back. So, I did this:
SELECT COUNT( <photo> ) FROM <table> WHERE <photo> IS NOT NULL;
SELECT COUNT( DISTINCT <photo> ) FROM <table> WHERE <photo> IS NOT NULL;I got 22 for the first query, but, instead of 21 for the second query, I got a -9643 Error (BLOB is not hashable).
Q: Why is <photo> hashable (i.e., "DISTINCT <photo>" works) in the first highlighted query, but not in the second?Just for grins, I then explicitly used the ifx_checksum() hash function:
SELECT COUNT( DISTINCT ifx_checksum( <photo>, 0) ) FROM <table> WHERE <photo> IS NOT NULL;
Worked like a charm, and returned 21.
Thank you for any comments about why "DISTINCT" worked in one case, but threw a "non-hashable data type" error in the second example.
DG
------------------------------
David Grove
------------------------------
#Informix