Access SQL: Case doesn't work

Posted on 2011-04-26
Last Modified: 2012-05-11
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

Question by:deeboll
    LVL 42

    Accepted 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

    Author Comment

    I made it work with IIF. Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now