Hi All,

I have Customer hierarchy as follow :
  CustomerCategory
  CustomerType
  CustomerGroup
  Customer

Discount can be given to a CustomerCategory, or CustomerType, or a
CustomerGroup, or a Customer , or to ALL Customer. Discount is defined
in a Promotion Table

Customer Table :
  CustomerCode
  CustomerName
  CustomerCategoryCode
  CustomerTypeCode
  CustomerGroupCode

Order Table :
  OrderNo
  OrderDate
  CustomerCode
  OrderGross
  Discount

Promotion Table :
  PromoNumber
  PromoType (ALL / CustomerCategory / CustomerGroup / CustomerType /
Customer)
  PCode (ALL / 'xxxx' )
  Discount
   ** note :
       if PromoType = PCode = 'ALL', means the Discount is given to all
customer.
       If PromoType = 'CustomerCategory'  then PCode = Customer
Category Code
       If PromoType = 'Customer'  then PCode = Customer Code
       and so on...
   ** by doing this I avoid to define discount in four table.

The question is :

1) Is there any better way to define the promotion table

2) If I do design the Promotion table like above, how is the efficient
query to find the discount for an Order ??

Thank you for your help,
xtanto

I would use materialized paths.
Suppose a customer has
  CustomerCategory = 1
  CustomerType  = 23
  CustomerGroup = 345
  CustomerID = 45734896

thern the path is 1.23.345.45734896

Discounts:
for all
'%'
for  CustomerCategory = 1
'1.%'
for
  CustomerCategory = 1
  CustomerType  = 23
'1.23.%'

so on

WHERE Customer.Path LIKE Discount.Path

Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string
parse out set of numbers from a column