Hello,
So, a product can have many attributes...things that describe the product. In our 3rd party ERP, these are stored in a key-value manner.
product_code
attribute_code
attribute_value
etc.
Now, for some products there are 150+ attributes....you can pretty much guess where this is going...
User wants a report that shows an product_code and it's attributes on a single line (in separate columns) for Excel manipulation(s).
So, the SQL would require joining the same attribute table as many times as there are distinct attribute_codes for a given product_code.
If there are 150 named/distinct attributes that need to be lined up, this would mean 150 joins on that one table.
OR write scalars for each attribute
OR write a function that fetches the attribute_value when you pass the product_code and attribute_code and call this function 150 times in the SQL select list.
Yes, I know, I should benchmark each approach and select the one that works best....BUT, I would like to poll the wisdom of outstanding individuals on this group to see which of the 3 approaches would be preferred.
Oh and the users typically "query" hundreds to thousands of products and want this result set.
We are still on the terminally supported Oracle 10g database on Linux.
Thanks,
Manish