• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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