PB 2019 R3, PostgreSQL 11
This may best fit to be asked in a SQL Forum. Still I feel someone here might have encountered this issue and may have a (better) solution.
I need to read the Disc % from a Discount Master table where there are 8 columns apart from Disc % column. The columns are:
Data in that table looks something like this.
Here, first row says that any items from the brand Vivaldi got a discount of 5%, whereas Shirt from Vivaldi got a discount of 10% and 42 sized Shirt from Vivaldi got a discount of 15%.
Similarly all Sarees got a discount of 10%, whereas Sarees with Poonam material will have discount of 12% and Silk Sarees a discount of 8%.
Now in Sales screen, Sarees may be in any combination, we need to get the appropriate discount % from the above discount master table
For the first row above, we need to apply 12% as discount as Sarees and Poonam matches with the master record. For the second row also, same 12%. For 3rd row, since it's a Silk saree, discount of 8% to be applied and for the fourth one, a discount of 10% to be applied.
We need to write queries to arrive this discount from the Discount Master. If we take all possibilities, there will be 256 combinations and we need to write 255 queries, (leaving all null combo) in correct order, to arrive the discount %.
Would like to know whether anyone have come across this issue and have found a elegant solution that could be done in a better way than writing 255 queries ? Any ideas is welcome.