SQL Joins (Possible CASE or If Statement Needed) Help???
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!
Need help with:
--My Current Version of SQL for Outbound CNS to GML:
CAST (t.ExternalFileDate as DATE) ExternalFileDate,
--SecurityIDType, ** need to build in join 'correctly' ** p.RecordType,
CAST (t.TradeDate as DATE) TradeDate,
CAST (t.SettleDate as DATE) SettleDate,
--User Remarks, **need to get description ** t.Market,
CAST (t.TaxDate as DATE) TaxDate,
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'
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.
Last edited by Hack; 01-21-2011 at 09:26 AM.
Reason: Added Code Tags
Last Post: 10-01-2006, 06:08 PM
By Dark Rain in forum Java
Last Post: 09-30-2005, 07:42 PM
By SFXSoftware in forum Careers
Last Post: 06-04-2005, 02:27 AM
By Dean Earley in forum VB Classic
Last Post: 05-03-2002, 09:50 AM
By Ted Young in forum Database
Last Post: 07-20-2001, 11:52 AM
Tags for this Thread
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL