locked
parent/child query help RRS feed

  • Question

  • Hello All,

    I have a parent table tbl_trade and a child table tbl_allocation. A trade can have many allocations. I'm trying to write a query which calculates the tbl_trade.isRec value. This should be true if all the child allocations have a match key. So, in this example, tbl_trade.isrec would be true for the first trade and false for the second. How would I do this via sql ?

    e.g. something like this

    update tbl_trade set isrec = 1

    where not exists

    (

    select match_key from tbl_allocation where tbl_trade.trade_id = tbl_allocation.trade_id and match_key is null

    )

    GO

    /****** Object:  Table [dbo].[tbl_trade]    Script Date: 08/29/2013 13:28:09 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[tbl_trade](
        [trade_id] [int] IDENTITY(1,1) NOT NULL,
        [cusip] [varchar](9) NOT NULL,
        [qty] [float] NOT NULL,
        [isRec] [bit] NOT NULL
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[tbl_trade] ADD  CONSTRAINT [DF_tbl_trade_isRec]  DEFAULT ((0)) FOR [isRec]
    GO

    USE [mtge]
    GO

    /****** Object:  Table [dbo].[tbl_allocation]    Script Date: 08/29/2013 13:28:41 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[tbl_allocation](
        [alloc_id] [int] IDENTITY(1,1) NOT NULL,
        [trade_id] [nchar](10) NOT NULL,
        [qty] [int] NOT NULL,
        [match_key] [int] NULL
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[tbl_allocation] ADD  CONSTRAINT [DF_tbl_allocation_isRec]  DEFAULT ((0)) FOR [match_key]
    GO

    I have the following records:

    INSERT INTO tbl_trade (cusip, QTY)
    values ('912828AA1', 500)

    INSERT INTO tbl_trade (cusip, QTY)
    values ('912828AA2', 200)

    INSERT INTO tbl_allocation (trade_id, qty, match_key)
    values (1, 300, 1)

    INSERT INTO tbl_allocation (trade_id, qty, match_key)
    values (1, 200, 2)

    INSERT INTO tbl_allocation (trade_id, qty, match_key)
    values (2, 100, 3)

    --------------------------------------------------------------------------------------------------------------------


    Thursday, August 29, 2013 5:42 PM

Answers

  • If you just need to check match_key existence, try this one:

    UPDATE  a
    SET     isRec = CASE WHEN match_key IS NOT NULL THEN 1
                         ELSE 0
                    END
    FROM    dbo.tbl_trade a
            LEFT OUTER JOIN dbo.tbl_allocation b ON a.trade_id = b.trade_id

    If you need to check the match_key existence and the equality of qty sum, try this one:

    ;
    WITH    cte
              AS ( SELECT   a.trade_id a_trade_id ,
                            a.cusip ,
                            a.qty a_qty ,
                            a.isRec ,
                            b.alloc_id ,
                            b.trade_id ,
                            b.qty ,
                            b.match_key ,
                            SUM(b.qty) OVER ( PARTITION BY b.trade_id ) AS sumqty
                   FROM     dbo.tbl_trade a
                            LEFT OUTER JOIN dbo.tbl_allocation b ON a.trade_id = b.trade_id
                 )
        UPDATE  cte
        SET     isRec = CASE WHEN match_key IS NOT NULL
                                  AND a_qty = sumqty THEN 1
                             ELSE 0
                        END
        FROM    cte 
    

     

    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Thursday, August 29, 2013 7:09 PM

All replies

  • In sample data provided the 2nd trade also having a match key. is it about SUM of quantity?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 29, 2013 5:52 PM
  • If you just need to check match_key existence, try this one:

    UPDATE  a
    SET     isRec = CASE WHEN match_key IS NOT NULL THEN 1
                         ELSE 0
                    END
    FROM    dbo.tbl_trade a
            LEFT OUTER JOIN dbo.tbl_allocation b ON a.trade_id = b.trade_id

    If you need to check the match_key existence and the equality of qty sum, try this one:

    ;
    WITH    cte
              AS ( SELECT   a.trade_id a_trade_id ,
                            a.cusip ,
                            a.qty a_qty ,
                            a.isRec ,
                            b.alloc_id ,
                            b.trade_id ,
                            b.qty ,
                            b.match_key ,
                            SUM(b.qty) OVER ( PARTITION BY b.trade_id ) AS sumqty
                   FROM     dbo.tbl_trade a
                            LEFT OUTER JOIN dbo.tbl_allocation b ON a.trade_id = b.trade_id
                 )
        UPDATE  cte
        SET     isRec = CASE WHEN match_key IS NOT NULL
                                  AND a_qty = sumqty THEN 1
                             ELSE 0
                        END
        FROM    cte 
    

     

    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Thursday, August 29, 2013 7:09 PM
  • >> I have a parent [sic] table tbl_trade and a child [sic] table tbl_allocation. <<

    Everything you are doing is wrong. The prefix “tbl-” is so awful that it has a name, “tblling”, which you can Google. The terms “parent” and “child” come from 1970's pre_RDBMS network databases. Since tables model sets, the names have to be collective or plural. But you pre_RDBMS database users did record by record processioning so you use singular names on tables even today! 

    >> A trade can have many allocations. <<

    Please, please just read one book on RDBMS. Just one book. 

    A trade is a strong entity. It exist by itself. An allocation is a weak entity. It exist because of a strong entity. 

    >> Trades.is_rec value. This should be TRUE [sic] if all the child [sic] allocations have a match key. So, in this example, Trades.isrec [sic] would be TRUE for the first [sic] trade and FALSE for the second. How would I do this via SQL? <<

    There is no ordering concrete in RDBMS, so first and next make no sense. We do not use bit flags in SQL; that was 1950's assembly language. You do not even know that rows are nothing whatsoever like records! This is a predicate language. 

    We do not use IDENTITY in a valid data model. Why is the physical insertion attempt sequence an attribute of anything but the disk where the data is kept?? 

    https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/  

    Again, everything you are doing is wrong. If I put you in a book, people would accuse me of making up an insanely bad example to demonstrate basic principles.  

    CUSIP is not variable length, but since you invited garbage data you will get it. Why did you use FLOAT for a trade quantity? You like insane rounding errors? Your auditor will not. It is specially illegal; read the GAAP and EU rules for computations. 

    CREATE TABLE Trades
    (cusip CHAR (9) NOT NULL
      CHECK (cusip LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][A-Z0-9][A-Z0-9][A-Z0-9]'),
     PRIMARY KEY,
     trade_qty INTEGER NOT NULL
      CHECK (trade_qty > 0));

    You did not know the ANSI Standard syntax for insertion. 

    INSERT INTO Trades
    VALUES ('912828AA1', 500), ('912828AA2', 200);

    I have done basket systems before. What you have is a total mess. 

    CREATE TABLE Trade_Allocations
    (cusip CHAR (9) NOT NULL
      REFERENCES  Trades(cusip)
     trade_seq INTEGER NOT NULL
      CHECK (trade_seq > 0),
     allocation_qty INTEGER NOT NULL);

    INSERT INTO Trade_Allocations 
    VALUES('912828AA1', 1, 300),
          ('912828AA1', 2, 200),
          ('912828AA2', 1, 100);

    I am not going to post the SQL needed to assure that the sum of the allocations is less than or squeal to the original trade quantity. This cannot be fixed in a forum. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 30, 2013 2:46 AM
  • Hi,

    I see little issues with the above script as we are getting all the records from allocation and updating trade table. Instead you can use below query:

    ;WITH    cte
    AS ( SELECT   DISTINCT a.trade_id trade_id ,
        CASE WHEN EXISTS 
    		( SELECT 1 
    			FROM dbo.tbl_trade 
    			WHERE trade_id = a.trade_id 
    				AND ISNULL(match_key, '') <> '') 
    			THEN 1 
    			ELSE 0 
    	END matchkey,
        SUM(b.qty) OVER ( PARTITION BY b.trade_id ) AS sumqty
    FROM     dbo.tbl_trade a
        INNER JOIN dbo.tbl_allocation b ON a.trade_id = b.trade_id
    )
    
    Update t
    SET IsRec = CASE WHEN (t.qty = c.sumqty	AND c.matchkey = 1) THEN 1 ELSE 0 END
    FROM dbo.tbl_trade t
    INNER JOIN cte c
    ON t.trade_id = c.trade_id

    Thanks,

    Amit


    Thanks, Amit Srivastava

    Friday, August 30, 2013 7:32 AM
  • Tables are fictional. Invented solely for the purpose of this question. Wasn't aware you had to be an expert DBA to post on this messageboard.

    Tuesday, September 3, 2013 3:15 PM