SQL Joins (Possible CASE or If Statement Needed) Help???


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: SQL Joins (Possible CASE or If Statement Needed) Help???

Hybrid View

  1. #1
    Join Date
    Dec 2010
    Location
    Baltimore, MD
    Posts
    8

    Lightbulb 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!
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Code:
    --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.
    Last edited by Hack; 01-21-2011 at 09:26 AM. Reason: Added Code Tags

Similar Threads

  1. errors with some varibles
    By ELHEK in forum Java
    Replies: 1
    Last Post: 10-01-2006, 06:08 PM
  2. Why is this in the console?
    By Dark Rain in forum Java
    Replies: 8
    Last Post: 09-30-2005, 07:42 PM
  3. Sr. SQL Development DBA Needed
    By SFXSoftware in forum Careers
    Replies: 0
    Last Post: 06-04-2005, 02:27 AM
  4. using a variable in a SQL statement.
    By Dean Earley in forum VB Classic
    Replies: 3
    Last Post: 05-03-2002, 09:50 AM
  5. SQL Error using CASE statement
    By Ted Young in forum Database
    Replies: 1
    Last Post: 07-20-2001, 11:52 AM

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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center