Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Access SQL: Case doesn't work

Hello -

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)

Open in new window

Open in new window

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 'EH' THEN 'Ethanol'
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
FROM view_RefreshTradeStation;

Open in new window

1 Solution
If that is a Passthru query, the MID function will not work  (I believe the ORACLE equivalent is SUBSTR).

If that is an Access query, the CASE statement will not work.  The Access equivalent is IIF
deebollAuthor Commented:
I made it work with IIF. Thanks

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now