CODEWITHSUNDEEP
sqloracle-database
Worp
Worp

Reputation: 1028

Oracle - Map column data to a value

Let me first point out that my question is going to be very very close to this question: map-column-data-to-a-value-oracle

Please quickly read that one first.

Now in my case I need the exact same thing but not as the primary query. Instead I need the information as one part of my query.

I have this table:

someId | someValue | dataType
   1   |    500    | 1
   2   | someValue | 2

And I know that dataType "1" means "Integer". I also know the meaning of the other values in the dataType column.

So I want to select all entries in the table but have their dataTypes as their human readable values instead of their numbers:

Results:

1, 500, Integer
2, someString, String

Trying to apply the solution of the question I linked, I created a subquery like

SELECT
someId,
someValue,
(
  SELECT CASE
  WHEN dataType = 1 THEN 'INTEGER'
  WHEN dataType = 2 THEN 'FLOAT'
  WHEN dataType = 3 THEN 'TEXT'
  ELSE 'DATE'
END
  myTable
) as myDataType

I will get a subquery that returns more than 1 result and Oracle will complain.

Since I access the DB through SQL directly, I need a "pure SQL" solution. Otherwise I could just parse the value through a mapping, in say PHP. But that's not possible here. I am shooting some queries at a DB to try and gather information about the data and structure, which we don't know about. So only SQL is available.

Upvotes: 3

Views: 8482

Answers (1)

JohnHC
JohnHC

Reputation: 11205

Get rid of the subquery:

SELECT someId,
       someValue,
       CASE
          WHEN dataType = 1 THEN 'INTEGER'
          WHEN dataType = 2 THEN 'FLOAT'
          WHEN dataType = 3 THEN 'TEXT'
          ELSE 'DATE'
       END as Datatype
from  myTable

Upvotes: 8

Related Questions