Solved

Oracle Case statement problem

Posted on 2012-12-27

1 solution

595 Views

Last Modified: 2012-12-27

Im relatively new to Oracle query syntax so can you help me out with the Case statement.

Select SECTION_ID, SECTION_TYPE, SECTION_ORDINAL, SECTION_TABLE_CELL,
CASE SECTION_TYPE
WHEN LINKLIST
THEN (SELECT LINK_LIST_NAME FROM OMNI_ENT_LINKLIST WHERE LINKLIST_ID = SECTION_ID)
WHEN LINE
THEN (SELECT LINK_ID FROM OMNI_ENT_LINK WHERE LINK_ID = SECTION_ID)
WHEN HEADER
THEN (SELECT LINK_ID FROM OMNI_ENT_HEADER WHERE HEADER_ID = SECTION_ID)
WHEN PARAGRAPH
THEN (SELECT LINK_ID FROM OMNI_ENT_PARAGRAPH WHERE LINK_ID = SECTION_ID)
ELSE ''
END AS SECTION_ENTITY
FROM OMNI_ENT_PAGE_CONTENT

Comment

Question by:jknj72

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 22

Accepted Solution

by:Steve Wales

Steve Wales earned 500 total points

ID: 387245452012-12-27

What's the error you're getting ?

Here's a reference to using the CASE statement: http://www.techonthenet.com/oracle/functions/case.php

Assuming that LINKLIST, LINE, HEADER and PARAGRAPH are valid values of column SECTION_TYPE, enclose the values in quotes:

CASE SECTION_TYPE
WHEN 'LINKLIST' then

etc

Try that and if you're still getting errors, can you come back with that the error message is ?

LVL 70

Expert Comment

by:Qlemo

ID: 387246472012-12-27

I would prefer to not use a case here:

Select SECTION_ID, SECTION_TYPE, SECTION_ORDINAL, SECTION_TABLE_CELL,
   types.ID AS SECTION_ENTITY
FROM OMNI_ENT_PAGE_CONTENT
Left Join  (
  select 'LINKLIST' as Type, Link_List_Name as ID from Omni_Ent_Linklist
  union
  select 'LINE', Link_ID from Omni_Ent_Link
  union
  select 'HEADER', Link_ID from Omni_Ent_Header
  union
  select 'PARAGRAPH', Link_ID from Omni_Ent_Paragraph ) types
on types.type = Section_Type

Open in new window

Author Comment

by:jknj72

ID: 387246942012-12-27

yes that worked, glad I wasnt far off....Thanks

Author Closing Comment

by:jknj72

ID: 387246992012-12-27

thanks

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursor in Oracle

Article by: Swadhin

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…

Oracle Database

The Lone Date Ranger :: there is just one right way

Article by: PortletPaul

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

Query Syntax

Using the Original Oracle Export and Import Utilities

Video by: Steve

This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.