Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    29

    Unanswered: Duplicate a record

    I managed to duplicate a single record. However I intended to display the duplicate record with different values. For example:

    A ABC 065 001 A10111 Sell 54.847500 0 0
    T ABC 065 001 A10111 Sell 50.847500 0 1

    Code:
    SELECT 
     
    case i.ordStatus 
    when 'Queued' then 'N'
    when 'Filled' then 'A'
    when 'Partial Filled' then 'A'
    when 'Cancel' then 'C'
    end AS 'ordStatus',
     
    f.code AS 'exchCode', 
    g.code AS 'companyCode', 
    h.code AS 'branchCode',
    c.code AS 'clientCode',
    i.transType AS 'transType',
     
    case i.ordStatus 
    when 'Queued' then ((i.quantity * i.price * i.exchRate) / j.denomination)
    when 'Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
    when 'Partial Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
    when 'Cancel' then ((i.adjust * i.price * i.exchRate) / j.denomination)
    end AS 'ordAmount',
     
    case i.ordStatus 
    when 'Queued' then 0
    when 'Filled' then 0
    when 'Partial Filled' then 0
    when 'Cancel' then 0
    end AS 'matchAmount'
     
    FROM TM_TradingProfile a 
    LEFT JOIN TM_clientTradingProfile b 
    ON a.id = b.id  
    LEFT JOIN TM_Client c 
    ON c.id = b.TM_client_fk 
    LEFT JOIN BKL_User d 
    ON d.id = c.id 
    JOIN BKL_Authentication e 
    ON c.id = e.BKL_user_fk 
    LEFT JOIN RM_Exchange f
    ON a.RM_exchange_fk = f.id
    LEFT JOIN BKL_Company g
    ON a.BKL_company_fk = g.id
    LEFT JOIN BKL_Branch h
    ON a.BKL_branch_fk=h.id
    LEFT JOIN RM_MarketTransaction i
    ON a.tradingAccountNumber = i.tradingAccNo
    LEFT JOIN bkl_forexexch j
    ON i.tradCurr = j.currencyCodeFrom
     
    CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
     
    WHERE a.tradingAccountNumber='ST3273'
    Besides, I should be able to display a duplicate record when the ordStatus is filled or partial filled.

    Your help is kindly appreciated.

    Thank You.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What is your definition of a "duplicate record"? For me it means an exact copy of another record.

    I intended to display the duplicate record with different values
    A duplicate record with other values is by (my) definition not a duplicate record. I think you really want to get two different records for a particular tradingAccountNumber.

    I don't know your data, but if you can write one SQL script that gives you the data that you want to display in one record. Then write another SQL script that gives you the data that you want to display in the other record. Then combine them with a UNION ALL and you get what you want. That will always work.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •