Join tables with dynamic stored operator conditions

Transact-SQL
1

In a MS-SQL 2019 Enviroment , i have a table with stored Policy Conditions that will continuously be updated by a user.
The user will change the operators of the conditions and the values per condition, insert new rows etc.

image
image1113×289 7.67 KB

What i am struggling with , is to be able to have a query that will match the invoiced lines with the matched policy (if matched..). The need to to handle the more operator and in the future maybe like operator is what makes it difficult to me because it's not a simple concatenation join
I tried creating a Stored Procedure that returns an SQL statement with dynamic where clause as a string, but i am stuck because i cant use it dynamically in another Function using exec

Any help is appreciated ... :slight_smile:

Consumer data is provided :

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;

GO

DROP TABLE IF EXISTS #Conditions
CREATE TABLE #Conditions

(

Operator_ItemType Varchar(10) ,

ItemType Varchar(15) ,

Operator_Category Varchar(10) ,

Category Varchar(50) ,

Operator_Sex Varchar(10) ,

Sex Varchar(15) ,

Operator_Discount Varchar(10) ,

Discount int ,

PolicyCode varchar(10)


);



INSERT INTO #Conditions 
VALUES ('<>', '01', '=', 'GUESS ACCESSORIES MEN' , '=' , 'MALE' , NULL , NULL , 'A00'),

       ('=', '01', '=', 'GUESS ACCESSORIES MEN' , '=' , 'MALE' ,  NULL , NULL,  'A01'),

       ('<>','01', '=', 'GUESS ACCESSORIES MEN' , '=' , 'MALE' ,  '>' , 15 , 'B00'),

       ('=', '01', '=', 'GUESS ACCESSORIES MEN' , '=' , 'MALE' ,  '>', 10 , 'C00')


Select * from #Conditions

DROP TABLE IF EXISTS #InvoiceLines
CREATE TABLE #InvoiceLines

(

ItemCode Varchar(20) ,

ItemType Varchar(15) ,

Category Varchar(50) ,

Sex Varchar(15)  ,

Discount int 

);

INSERT INTO #InvoiceLines VALUES ('100', '01', 'GUESS ACCESSORIES MEN' , 'MALE' , NULL ),

       ('101', '01', 'GUESS ACCESSORIES MEN' , 'MALE' , NULL ),

       ('102', '01', 'GUESS ACCESSORIES MEN' , 'MALE' , NULL ),

       ('103', '01', 'GUESS ACCESSORIES MEN' , 'MALE' , 18 )

Select * from #InvoiceLines
2

As you are already describing, you will continue to struggle with this structure to manage this kind of feature. If you are able to change your table structure a bit, take a look here:

Tony's article ultimately leads here:

It may not be readily apparent what relational division is for, but it is almost exactly what you're trying to do. Peter Larsson has a video that covers it:

I confess that these resources are a bit difficult to digest, I wish there were better ones available. I found the presentation that Tony mentions:

https://www.sqltopia.com/publications/sqlrally-amsterdam-2013/

That last link includes code, and if you want to test it out, I suggest using it exactly as-is until you feel comfortable and fully understand it. I've used a variation of it at a previous job, it worked well, but other people modified it and it ended up performing worse than expected. I've seen Peter demonstrate the original code in person, and it performs exactly like Tony described.

1 Like
3

And here I was thinking I was going to be bored tonight.

4

Thank you for the answer ...

What if we say that I have a physical field in the Invoice lines table and wanted to create a procedure that will get as input the uniqueidentifier for every invoiceline of a specific set of lines , and output a set of update statements for specific batches of invoicelines ? ...I don't think I will have problem running this procedure before reporting for specific invoices .. We are talking for about 100-200 policies max.the procedure will be run at a time for 10-50 invoicelines from the left side and 100-200 policies from the right side...
Can you suggest me how will you approach this dynamic update statement idea ? Something like a simple expression builder ..

Thanks :pray::pray:

5

It would be helpful to provide some sample data and the expected results. I'm not 100% clear on what you're suggesting.

In any case, if you need to pass multiple identifiers to a procedure to UPDATE multiple rows, why not use a table parameter:

There's an example in there using INSERT that could be easily modified to do an UPDATE. Also, why generate UPDATE statements, instead of just running the UPDATE in the procedure? You can batch UPDATEs in the procedure as well, or even run it as a single UPDATE that processes all rows.

6 

SELECT 
    il.*, 
    c.PolicyCode
FROM #InvoiceLines il
OUTER APPLY (
    SELECT TOP (1) c.*
    FROM #Conditions c
    WHERE ((c.Operator_ItemType = '=' AND c.ItemType = il.ItemType) OR 
           (c.Operator_ItemType = '<>' AND c.ItemType <> il.ItemType)) AND
          ((c.Operator_Category = '=' AND c.Category = il.Category) OR 
           (c.Operator_Category = '<>' AND c.Category <> il.Category) OR 
           (c.Operator_Category = 'LIKE' AND il.Category LIKE c.Category)) AND
          ((c.Operator_Sex = '=' AND c.Sex = il.Sex) OR 
           (c.Operator_Sex = '<>' AND c.Sex <> il.Sex)) AND
          ((c.Operator_Discount IS NULL AND il.Discount IS NULL) OR 
           (C.Operator_Discount = '>' AND il.Discount > c.Discount))
    /*ORDER BY PolicyCode*/
) AS c
1 Like