Pawel Kowalski Pawel Kowalski - 6 months ago 10
SQL Question

Fetch error list with integer to binary conversion

I have a TABLE_OF_ERRORS with ID's as 2^n values:

ID,VALUE
-----------------
1,'ERROR 1'
2,'ERROR 2'
4,'ERROR 3'
8,'ERROR 4'


etc...
In the other table let's call it PRODUCTS, for each row I have an error code that is a sum of errors from TABLE_OF_ERRORS:

PROD_ID,ERORS_CODE
--------------------
PROD1, 2
PROD2, 5
PROD3, 12


What I need is a join that converts this afrificial one_to_many relation into :

PROD1,'ERROR 2'
PROD2,'ERROR 1'
PROD2,'ERROR 3'
PROD3,'ERROR 3'
PROD3,'ERROR 4'


Can you please suggest any hint? I have no idea how to start...

Regards

Pawel

Answer

You can use BITAND():

select p.*, toe.value
from products p left join
     table_of_errors toe
     on bitand(p.errorscode, toe.id) > 0;

I would recommend that you change your data structure. Bit-packing may make sense in some computer languages, but it is not particularly useful for Oracle. There are many other facilities, such as junction tables, nested tables, and JSON fields which are more appropriate for this type of data structure.

Comments