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
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.currencyCodeFromCROSS 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.
-
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
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.currencyCodeFromCROSS 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.
Can't tell exactly what's wrong but you seem to have too many joins. That cross join... hmmm... I am nos sure what you want to do. Personally, I would use a union operator to duplicate records.
SELECT a.col1, a.col2,... 0 As Flag
FROM myTable a
WHERE ...
UNION
SELECT b.col1,b.col2,... 1 as Flag
FROM myTable b
WHERE ....