1. Sivaprakash BKR
  2. PowerBuilder
  3. Monday, 19 July 2021 14:25 PM UTC

Hello,

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:

Variety
Brand
Model
Pattern
Shade
Material
Finish
size
Disc %

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.

Happiness Always
BKR Sivaprakash

 

John Fauss Accepted Answer Pending Moderation
  1. Saturday, 24 July 2021 15:59 PM UTC
  2. PowerBuilder
  3. # 1

I've come up with a solution; However, because I have no experience with PostgreSQL and currently have access only to Microsoft SQL Server, you'll have to determine if the technique can be translated and applied to PostgreSQL.

In order to make this work, I had to add Identity / Primary Key columns to the DiscountMaster and Sales tables.

DiscountMaster:

Sales:

You can see that I've also added a fifth Sales row (Levi's/Denim). I did this to determine what would happen when NO matching discounts exist.

I created a scalar (returns a single value) SQL function; func_SalesDiscountID, which takes a SalesID value argument and returns a DiscountMasterID of the best matching discount or NULL if there is no matching discount for the requested Sales row. Here's the SQL script that creates the SQL function:

-- Drop the function (if it exists) before creating it...
IF OBJECT_ID('func_SalesDiscountID') IS NOT NULL     
   DROP FUNCTION dbo.func_SalesDiscountID
go

CREATE FUNCTION dbo.func_SalesDiscountID(@salesid int)
RETURNS int
AS
BEGIN
-- This function returns the ID of the DiscountMaster row that best matches the requested Sales row.
-- Note: If there are no matches, the function returns NULL.
RETURN
(SELECT t2.DiscountMasterID
   FROM (SELECT TOP 1 
                t1.DiscountMasterID,
                t1.Ranking,
                MAX(t1.ranking) AS MaxRanking
           FROM (SELECT s.SalesID,
                        s.Variety,
                        s.Brand,
                        s.Model,
                        s.Pattern,
                        s.Shade,
                        s.Material,
                        s.Finish,
                        s.Size,
                        dm.DiscountMasterID,
                        dm.DiscountPercent,
                     -- Increment ranking whenever any Sales value matches a DiscountMaster value...
                        CASE WHEN s.Variety  IS NOT NULL AND s.Variety  = dm.Variety  THEN 1 ELSE 0 END
                       +CASE WHEN s.Brand    IS NOT NULL AND s.Brand    = dm.Brand    THEN 1 ELSE 0 END
                       +CASE WHEN s.Model    IS NOT NULL AND s.Model    = dm.Model    THEN 1 ELSE 0 END
                       +CASE WHEN s.Pattern  IS NOT NULL AND s.Pattern  = dm.Pattern  THEN 1 ELSE 0 END
                       +CASE WHEN s.Shade    IS NOT NULL AND s.Shade    = dm.Shade    THEN 1 ELSE 0 END
                       +CASE WHEN s.Material IS NOT NULL AND s.Material = dm.Material THEN 1 ELSE 0 END
                       +CASE WHEN s.Finish   IS NOT NULL AND s.Finish   = dm.Finish   THEN 1 ELSE 0 END
                       +CASE WHEN s.Size     IS NOT NULL AND s.Size     = dm.Size     THEN 1 ELSE 0 END AS Ranking
                   FROM dbo.Sales s
                  CROSS JOIN dbo.DiscountMaster dm --Tests all discounts against all Sales rows
                  WHERE s.SalesID = @salesid) AS t1
          GROUP BY t1.DiscountMasterID,
                   t1.Ranking
         HAVING t1.Ranking > 0 --Ensures no matches results in zero discount.
          ORDER BY t1.Ranking DESC) AS t2)
END
go 		

GRANT EXECUTE ON dbo.func_SalesDiscountID TO PUBLIC
go 

Some comments regarding the SQL function:

(1) It was not necessary to include all of the Sales columns in the innermost SELECT, but this was helpful when I was constructing/developing the overall SQL, so I've left it in for illustrative purposes.

(2) The "ranking" value is calculated by summing the result of each CASE/END statement, each of which produces 1 if the Sales column value matches the corresponding DiscountMaster column value, or zero if the Sales column value is NULL or does not match.

(3) Note that a cartesian product (Cross Join) is used to check the requested Sales row against every DiscountMaster row. Always take care when requesting a cartesian product!

Once the function is created and execution rights are granted, the query to produce the results is straightforward:

SELECT s.Variety,
       s.Brand,
       s.Model,
       s.Pattern,
       s.Shade,
       s.Material,
       s.Finish,
       s.Size,
       --No discount, if no matching DiscountMaster found.
       ISNULL(dm.DiscountPercent,0.000) AS DiscountPercent
  FROM dbo.Sales s
  -- A left outer join is required because there may be no
  -- DiscountMaster rows that apply to a Sales row.
  LEFT OUTER JOIN dbo.DiscountMaster dm ON 
       (dbo.func_SalesDiscountID(s.SalesID) = dm.DiscountMasterID)

The above query produces the following results:

Disclaimer: I make no claim this solution is optimal or that it handles tie-breaker conditions adequately, since Sivaprakash provided no information concerning tie-breakers. Tie-breaking is currently addressed via the "TOP 1" condition in conjunction with the ORDER BY clause in the SQL function definition.

Obviously this is not a PB problem, as Sivaprakash indicated at the onset, but it is an interesting and challenging problem nonetheless.

Regards, John

Comment
There are no comments made yet.
Ronnie Po Accepted Answer Pending Moderation
  1. Wednesday, 21 July 2021 16:24 PM UTC
  2. PowerBuilder
  3. # 2

I believe this query would work:

  SELECT

    DiscountMaster.DiscPct AS DiscToBeApplied

  FROM

    Sales

    JOIN DiscountMaster ON (

      ( isNull(Sales.Variety, '') = isNull(DiscountMaster.Variety, isNull(Sales.Variety, '') ) ) AND

      ( isNull(Sales.Brand, '') = isNull(DiscountMaster.Brand, isNull(Sales.Brand, '') ) ) AND

      ( isNull(Sales.Model, '') = isNull(DiscountMaster.Model, isNull(Sales.Model, '') ) ) AND

      ( isNull(Sales.Pattern, '') = isNull(DiscountMaster.Pattern, isNull(Sales.Pattern, '') ) ) AND

      ( isNull(Sales.Shade, '') = isNull(DiscountMaster.Shade, isNull(Sales.Shade, '') ) ) AND

      ( isNull(Sales.Material, '') = isNull(DiscountMaster.Material, isNull(Sales.Material, '') ) ) AND

      ( isNull(Sales.Finish, '') = isNull(DiscountMaster.Finish, isNull(Sales.Finish, '') ) ) AND

      ( isNull(Sales.Size, '') = isNull(DiscountMaster.Size, isNull(Sales.Size, '') ) )

    )

  WHERE

    ( <criteria for Sales> )

 

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 20 July 2021 21:00 PM UTC
  2. PowerBuilder
  3. # 3

I really like all the answers given here.

Maybe it would be simpler if you just load everything in a dw and then use filters, groups, etc. to get what you want.

Just an idea,

regards

Comment
  1. Miguel Leeuwe
  2. Wednesday, 21 July 2021 21:36 PM UTC
It might make your queries faster too, since you can use computed columns etc. to calculate sub-totals etc.
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 21 July 2021 21:39 PM UTC
All the isnull(), coalesce(), possible "group by", "order by" and "having" could make your query not be able to use the optimal indexes or primary keys. I usually try to keep my sql as simple as possible and do as much as possible on the resultset itself on the dw (depending on how much data is involved, etc.).

  1. Helpful
  1. René Ullrich
  2. Thursday, 22 July 2021 05:54 AM UTC
I have a similar query. To make it as much easy as possible I use:

- a database column to specify the priority of the rows. So I don't have to analyse in SQL what combination of data have a higher priority. I simple order by priority column.

- no empty values. I use % to specify "all". In query I use a "reverse" LIKE (e.g. :ARG_Variety LIKE Variety)
  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 20 July 2021 06:22 AM UTC
  2. PowerBuilder
  3. # 4

If I understand you right you want to get the value of the best matching row.

You need to bring the rows in priority order to find the row that best match you product.

select disc

from discountmaster

where // your matching rules (e.g. like in Mikes example)

order by // a order clause to select best fetching product first, example

          CASE WHEN Variety IS NULL THEN 1 ELSE 0 END,

          CASE WHEN Brand IS NULL THEN 1 ELSE 0 END,

          ...

fetch first 1 rows only;

 
Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 20 July 2021 05:17 AM UTC
  2. PowerBuilder
  3. # 5

Thanks Olan and mike

1.  Requirement is NOT to arrive max discount.
2.  A blank (or null) in the discount master means any value could be there in sales item.  
3.  It's just like an hierarchy.
3.  If there is an exact match between sales item and discount master, leaving the blank columns in the discount master, then that discount % to be taken.
4.  If not, then the match above in the hierarchy has to be applied.
Probably this might explain better

 

Take Sarees item in the image inserted [ Discount Master ]

1.  All Sarees will have a discount of 10%.
2.  If the Material also matches then the discount will be 12% [Poonam] or 8% [Silk]

So in Sales from the image inserted,
1.  1st Item, Sarees, Ashoka, Yello, Poonam, 6.5 will have a discount of 12% as Variety and Material matches with a record in Discount Master.
2.  2nd Item, Sarees, Ashoka, Half Saree, Red Mixed, Poonam, 5.5 will also have a discount of 12% as Variety and Material matches exactly with a record in Discount Master
3.  3rd Item, Sarees, Nature, Silk, 6.5 will have a discount  of 8% as variety and Material matches exactly with a record in Discount Master. 
4.  4th Item, Sarees, Mixed, 6.5 will have a discount of 10%, as only Variety matches with a record in Discount Master.

Comment
  1. Olan Knight
  2. Wednesday, 21 July 2021 17:39 PM UTC
My answer was designed to find the max discount, so it will not meet your needs.
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 19 July 2021 20:43 PM UTC
  2. PowerBuilder
  3. # 6

1. Put the data into a new table or DW
   An easy and efficient way to do this is to use the retrieval in #2 to populate a DW.

2. Sort the data by:
       Brand, Variety, Disc %, Model, Pattern, Shade, Material, Finish, Size

The first quesry includes the Brand and variety to get the default discount.
   Example:   select max (disc_%) from new_table where brand = :as_brand AND variety = :as_variety;
   Returns:    15%
   Set:          max_discount = 15%


If you have another component, then run a new query as the #1 query with the new component added:
   SQL:     select max (disc_%) from new_table where brand = :as_brand AND variety = :as_variety AND Size = 42;
   Returns:    15% 
   Set:          IF (this_discount > max_discount) THEN max_discount = this_discount; 

Repeat for each component where the value is not null.


If you need to know the components that comprise the discount, add a new column called ID and set it to ROWNUM. With each retreival, get the discount PLUS the rownumber.

You will have at most 7 queries to execute, but the rules are generic and those seven queries are the only ones you will need as long as the component count remains the same.


Later -

Olan

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Monday, 19 July 2021 14:39 PM UTC
  2. PowerBuilder
  3. # 7

use coalesce:

1. store no value in discountmaster as NULL value 

2. assume you send in all column values (variety, brand, etc) into the query, if what you are looking for is missing then send THAT in either as a null or space.

3.  assume you want the largest discount?

 

the :arg_variety/brand etc are the arguments to the datawindow .  if you send in blank and not null for missing arguments, then you wont' need the coalesce on the right hand side 

 

select max(disc)
from discountmaster
where coalesce( Variety , :ARG_Variety) = coalesce( :ARG_Variety, ' ')
AND coalesce( Brand , :ARG_Brand) = coalesce( :ARG_Brand, ' ')
...

Comment
  1. mike S
  2. Tuesday, 20 July 2021 13:25 PM UTC
in that case, you need to select all rows that have 1 or more matching values into a datawindow. then apply your best match logic. I assume you have logic for tie breakers.





select disc, variety, ...

from discountmaster

where coalesce( Variety , :ARG_Variety) = coalesce( :ARG_Variety, ' ')

AND coalesce( Brand , :ARG_Brand) = coalesce( :ARG_Brand, ' ')
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.