Hi - We have created a Table where CUST_ADDR_ID is a hashrow (combination of few columns) as shown below. Now we would like to query data based on that value but having trouble, Can someone please guide.
CREATE TABLE TEMP_TABLE AS
(SELECT ID1, STREET_ADDRESS, CITY, STATE_CD, ZIPCODE,
TRANSLATE(COALESCE(STREET_ADDRESS,'') ||COALESCE(CITY,'') ||COALESCE(STATE_CD,'') ||COALESCE(ZIPCODE,'') USING UNICODE_TO_LATIN)
) AS CUST_ADDR_ID
FROM ABC_PARTIES) WITH DATA PRIMARY INDEX (ID1);
SELECT * FROM TEMP_TABLE WHERE CAST(CUST_ADDR_ID AS VARCHAR(10))='67-F0-BA-5F' -- Didnt work.
SELECT * FROM TEMP_TABLE WHERE CAST(CUST_ADDR_ID AS VARCHAR(10))=cast('67-F0-BA-5F' AS VARCHAR(10))
-- Didnt work.
Get this 'Conversion between BYTE and other data types is illegal'. Read posts which said we need to create a UDF...if so can someone share it?
Please specify such examples which can be recreated without making any changes to them. Basicaly, it is bad idea to generate IDs using hashrow because of the synonims.
Answer to your question:
CREATE TABLE TEMP_TABLE AS
(SELECT day_of_calendar id1,
HASHROW(day_of_calendar) AS CUST_ADDR_ID
FROM sys_calendar.calendar) WITH DATA PRIMARY INDEX (ID1);
SELECT * FROM TEMP_TABLE WHERE cust_addr_id = '00047103'xb
Thanks Angellore, it worked as expected, i tried 'xb' but used the exact value to search including the '-' hence didnt work.
Now that u say that its a bad idea to generate ID's with hashrow Can you share your thoughts please?
I tried rank() but i noticed that each time i create such table with same input value the ID value generated by rank is different hence wouldnt work for us.
CREATE TABLE TEMP_TABLE2 AS
(SELECT ID, STREET_ADDRESS, CITY, STATE_CD, ZIPCODE,
rank() over (order by (COALESCE(STREET_ADDRESS,'') ||
COALESCE(ZIPCODE,''))) AS CUST_ADDR_ID
FROM ABC_PARTIES) WITH DATA PRIMARY INDEX (ID);
a Hash Synonym is different values resulting in the same RowHash, of course the probability is low.
I just wonder why you use this approach, who is going to remember the 4-byte cust_addr_id used in the WHERE?
And why do you use all those COALESCEs and the TRANSLATE instead of a simple hashrow(col1,col2,col3,col4)?
Hi Dieter - You are right. But i couldnt think of any other way of generating an ID based on couple of fields which represents the same value all-the-time. Please share your thoughts with a small example if there is a better way of dealing with it and maybe generates a CHAR value rather than BYTE.
Yes i agree, it doesnt make sense [altough it doesnt hurt] to use TRANSLATE and COALESCE with hashrow.
Thanks for your time.
Why don't you use the Primary Key of your table?
What do you want to do with this ID?
To get a CHAR you need to implement a UDF like MD5.