January 19, 2011 at 1:44 pm
I need to "tweak" the code below to account for item # 1 & 2 below. Can anyone point me in the right direction? Any help would be greatly appreciated!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--My Current Version of SQL for Outbound CNS to GML:
select t.CNSTransID,
t.DataSource,
t.UserID,
CAST (t.ExternalFileDate as DATE) ExternalFileDate,
t.ExternalFileIdentifier,
t.OrgSysTransID,
s.HiNetMajor,
t.HiNetMinor,
t.SettleCurrCode,
t.SecurityID,
--SecurityIDType, ** need to build in join 'correctly' ** p.RecordType,
t.TransCode,
CAST (t.TradeDate as DATE) TradeDate,
CAST (t.SettleDate as DATE) SettleDate,
t.QuantityShares,
t.SettlePrice,
t.UnpaidOrders,
t.UserComments,
t.UserRemarksID,
--User Remarks, **need to get description ** t.Market,
t.ExecBroker,
t.ClearingBroker,
t.LongShortInd,
t.CustCode,
CAST (t.TaxDate as DATE) TaxDate,
t.MktPrice,
t.NetAmountLocal,
t.TradedInterestIncCurr,
t.AutoSettleInd,
t.CommType,
CAST (t.EffDate as DATE) EffDate
from tblDCF_Transactions t, tblDCF_TransactionTypes p, tblDCF_SubAccounts s
where t.TransCode = p.TransType
and t.HiNetMinor = s.HiNetMinor
and t.UserID = 77107
and t.ExternalFileDate = '12/16/2010'
and t.ExternalFileIdentifier = '0630'
Need help with:
1) SecurityIDType - related to the SecurityID field, which isn't always set in tblDCF_Transactions. We need to scan the tblDCF_Securities table if it is populated and return the security type associated with the value stored within the SecurityID field (e.g. CUSIP, SEDOL, Ticker). Another trick with this field, though, is that if the TransCode field in tblDCF_Transactions is of Record Type T04 (SRI, SDO) - we need this value to return as "CURRENCY", even though they are not stored within the tblDCF_Securities table itself - they'd be instead stored within tblDCF_Currencies.
2) User Remarks - this also isn't always set on tblDCF_Transactions - but, when it is - we need to join this table and its UserRemarksID to the tblDCF_TransactionCannedRemarks table using the ID's to link, then return the TransDesc field from the tblDCF_TransactionCannedRemarks table.
January 19, 2011 at 2:15 pm
If there are optional foreign keys in your main table, then left join. This should get those values:
select t.CNSTransID,
t.DataSource,
t.UserID,
CAST (t.ExternalFileDate as DATE) ExternalFileDate,
t.ExternalFileIdentifier,
t.OrgSysTransID,
s.HiNetMajor,
t.HiNetMinor,
t.SettleCurrCode,
t.SecurityID,
sec.SecurityIDType,
--** need to build in join 'correctly' **
p.RecordType,
t.TransCode,
CAST (t.TradeDate as DATE) TradeDate,
CAST (t.SettleDate as DATE) SettleDate,
t.QuantityShares,
t.SettlePrice,
t.UnpaidOrders,
t.UserComments,
t.UserRemarksID,
ISNULL(tcr.TransDesc,'n/a') as [User Remarks], --**need to get description **
t.Market,
t.ExecBroker,
t.ClearingBroker,
t.LongShortInd,
t.CustCode,
CAST (t.TaxDate as DATE) TaxDate,
t.MktPrice,
t.NetAmountLocal,
t.TradedInterestIncCurr,
t.AutoSettleInd,
t.CommType,
CAST (t.EffDate as DATE) EffDate
from tblDCF_Transactions t, tblDCF_TransactionTypes p, tblDCF_SubAccounts s
left join tblDCF_Securities sec on t.SecurityID = sec.SecurityID
left join tblDCF_TransactionCannedRemark tcr on t.TransactionID = tcr.TransactionID
where t.TransCode = p.TransType
and t.HiNetMinor = s.HiNetMinor
and t.UserID = 77107
and t.ExternalFileDate = '12/16/2010'
and t.ExternalFileIdentifier = '0630'
Now, how does tblDCF_Currencies relate here?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply