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

Oracle Case When with fields in table

I cannot get this to work:

CASE TYPE_ASSMT WHEN 'F' THEN TTL_APPRAISE ELSE FUL_APPRAISE END AS APPRAISAL
Where TTL_APPRAISE and FUL_APPRAISE are columns in the table I am selecting from, I can get it to work with static values

It also fails using DECODE, I am attempting to select the value  one of two fields based on the contents of another field
0
shelbyinfotech
Asked:
shelbyinfotech
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
I'm guessing you are trying to use this in a where clause and it is causing a syntax problem.

Can you post more of the query and what you are trying to do?  You might need dynamic SQL.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:

CASE WHEN TYPE_ASSMT = 'F' THEN TTL_APPRAISE ELSE FUL_APPRAISE END AS APPRAISAL

anyhow, what exactly is the error you get?
0
 
shelbyinfotechAuthor Commented:
Error from designer (Visual Studio 2010 )
Error in SELECT clause: expression near 'TYPE_ASSMT'.
Unable to parse query text.

Error from Database:

ORA-00918:      column ambiguously defined
0
 
slightwv (䄆 Netminder) Commented:
>> column ambiguously defined

this means the column exists in more than one table used in the select.

Just qualify the column with the table name you want the column from:
CASE TYPE_ASSMT WHEN 'F' THEN TABLE_A.TTL_APPRAISE ELSE TABLE_A.FUL_APPRAISE END AS APPRAISAL
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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