I have this append query which I am trying to make it work. The problem is with the cASE part. I am trying to derive the product name from Product field Ex: if the product is CL Nym Fut , then the product name would be CL. This is a simple case. I have some outliers like TAP - CL Nym Fut which should also be CL and then RB-Nym Fut which should be RBOB instead of RB.
So, I began writing the case statement for these outliers which is where I am stuck with the SQL. Can somebody help? Fyi, the view_RefreshTradeStation is a pass through query linked to an oracle database (so my pass through query is Oracle SQL)
SELECT view_RefreshTradeStation.Last_Updated_Date, view_RefreshTradeStation.Endur_Deal_Number, view_RefreshTradeStation.Workflow_Status, view_RefreshTradeStation.Exchange_ID, view_RefreshTradeStation.Bought_Sold, view_RefreshTradeStation.Price, view_RefreshTradeStation.Deal_Type,
CASE Mid(view_RefreshTradeStation.Deal_Type,1,InStr(1,view_RefreshTradeStation.Deal_Type,' ')-1)
WHEN 'RB' THEN 'RBOB'
WHEN 'ZC' THEN 'Corn'
WHEN 'EH' THEN 'Ethanol'
WHEN 'ICE' THEN 'CL'
ELSE '???' END,
Mid(view_RefreshTradeStation.Deal_Type,InStr(1,view_RefreshTradeStation.Deal_Type,' ')) AS Expr2, view_RefreshTradeStation.Trade_Owner, view_RefreshTradeStation.Trade_DG, view_RefreshTradeStation.Trade_Date, view_RefreshTradeStation.End_Date, view_RefreshTradeStation.Created_Date, view_RefreshTradeStation.Execution_Trader, view_RefreshTradeStation.CParty_Trade_Owner, view_RefreshTradeStation.CParty_DG, view_RefreshTradeStation.Volume_Type, view_RefreshTradeStation.Last_Updated_By