title: SQL Select Data Retrieval with Local Variables
body: <p>I'm trying to write an SQL statement that has some cascading conditional logic in it.  I have several fields that need to be returned but their values are conditional on previously determined fields.  For example, buysell_flag is dependent on the spotfwd_flag value and counter_amt and given_amt are dependent on the buysell_flag value, and so on.  </p>
  
  <p>I found out quickly that I cannot combine a data retrieval query and a variable assigning query in the same space, so I tried all sorts of things but couldn't return the data I wanted to.  This is a template for clients, who will be editing this SQL and pasting it into a program.  It will not always be the same, so I can't handle the logic in the program or in a stored proc or UDF.  I've tried to use a table, but when inserting that and selecting stuff to it, I ran into the same problems.  You can see my attempts below, but if it can't be done, please let me know - otherwise, I'm open to any and all suggestions!  Thanks!</p>
  
  <pre><code>    DECLARE @trans_type nvarchar(255), @trade_date nvarchar(255), @settle_date nvarchar(255), 
  @order_id nvarchar(255), @oaexecbroker nvarchar(255), @exec_broker nvarchar(255),
  @ostatus nvarchar(255), @trade_id nvarchar(255), @reference_price nvarchar(255), 
  @given_ccy nvarchar(255), @spotfwd_flag nvarchar(255), @buysell_flag nvarchar(255),
  @counter_ccy nvarchar(255), @counter_amt nvarchar(255), @given_amt nvarchar(255),
  @oacct_cd nvarchar(255)
  
  SELECT 
  @trans_type = o.trans_type,
  @settle_date = o.settle_date,
  @order_id = o.order_id,
  @oaexecbroker = oa.exec_broker,
  @exec_broker = o.exec_broker,
  @ostatus = o.status,
  @trade_id = oa.trade_id,
  @reference_price = o.exec_price,
  @given_ccy = o.target_crrncy,
  
  --o.trans_type    AS trans_type, 
  --  o.trade_date    AS trade_date, 
  --  o.settle_date   AS settle_date, 
  --  o.order_id      AS order_id, 
  --  oa.exec_broker  AS oaexecbroker, 
  --  o.exec_broker   AS exec_broker, 
  --  o.status        AS ostatus, 
  --  oa.trade_id     AS trade_id, 
  --  o.exec_price    AS reference_price, 
  --  o.target_crrncy as given_ccy, 
      -- for spot/fwd
  
      @spotfwd_flag = CASE 
          WHEN LEFT(o.inv_class_cd,1) = 'F' THEN ('FWD')
          WHEN LEFT(o.inv_class_cd,2) = 'CU' THEN ('SPOT')
      ELSE 'SPOT'
      END, -- must be spot or fwd
      --for buysell   
  
      @buysell_flag = CASE
          WHEN @spotfwd_flag = 'FWD' THEN 
              CASE
                  WHEN o.target_crrncy = o.to_crrncy THEN 'BUY'
                  ELSE 'SELL'
              END
      ELSE
          CASE 
              WHEN o.trans_type = 'BUYL' THEN 'BUY'
              ELSE 'SELL'
          END
      END, -- must be buy or sell
  
      @counter_amt = CASE 
          WHEN @buysell_flag = 'BUY' Then oa.exec_amt
          ELSE oa.exec_qty
      END,
  
      @given_amt = CASE
          WHEN @buysell_flag = 'SELL' THEN oa.exec_amt
          ELSE oa.exec_qty
      END, 
  
      @counter_ccy = CASE 
          WHEN o.target_crrncy = o.to_crrncy THEN o.from_crrncy
          ELSE o.to_crrncy
      END,
  
      @oacct_cd = CASE 
          WHEN f.udf_char6 = 'Y' THEN (oa.acct_cd + oa.custodian)
          ELSE oa.acct_cd 
      END 
  FROM   ts_order_alloc oa 
      LEFT OUTER JOIN ts_order o 
      ON oa.order_id = o.order_id 
      LEFT OUTER JOIN cs_fund f 
      ON oa.acct_cd = f.acct_cd 
  --  LEFT OUTER JOIN csm_security s 
  --  ON o.sec_id = s.sec_id 
  --WHERE  s.sec_typ_cd IN ( 'CFWD', 'CURR' ) 
  --  AND o.status IN ( 'READY','ACCT' ) 
  --  AND oa.usr_class_cd_2 = 'GTSSREADY' 
  
  select @trans_type as trans_type,
      @trade_date    AS trade_date, 
      @settle_date   AS settle_date, 
      @order_id      AS order_id, 
      @oaexecbroker  AS oaexecbroker, 
      @exec_broker   AS exec_broker, 
      @ostatus        AS ostatus, 
      @trade_id     AS trade_id, 
      @reference_price   AS reference_price, 
      @given_ccy as given_ccy,
      @buysell_flag as buysell_flag,
      @spotfwd_flag as spotfwd_flag,
      @given_ccy as given_ccy,
      @counter_ccy as counter_ccy,
      @given_amt as given_amt,
      @counter_amt as counter_amt
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>I'm expecting to get rows out based on the given variables.  The reason I have so many vars is I was trying to get make it comply to the SQL error messages.  </p>
  
  <p>Should come out like so:</p>
  
  <pre><code>trans_type  trade_date  settle_date order_id    oaexecbroker    exec_broker ostatus trade_id    reference_price given_ccy   buysell_flag    spotfwd_flag    given_ccy   counter_ccy given_amt   counter_amt
  BUYL    NULL    2010-04-06 00:00:00.000 1442084139  3PCITI  3PCITI  Ready   1389278710  1.50705 GBP BUY SPOT    GBP USD 604292  910699
  </code></pre>