CODEWITHSUNDEEP
sqloracle-database
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

New column based on the value of another column | Oracle?

In an Oracle 11g database I have table called organizations which looks like this:

| ORGANIZATION_ID | ORGANIZATION_NAME | TREE_ORGANIZATION_ID | ORGANIZATION_RANG |
|-----------------|-------------------|----------------------|-------------------|
| 1               | Facebook          | \1                   | 1                 |
| 2               | Instagram         | \1\2                 | 2                 |
| 3               | Whatsapp          | \1\3                 | 2                 |
| 4               | Alphabet          | \4                   | 1                 |
| 5               | Nest              | \4\5                 | 2                 |
| 6               | Google            | \4\6                 | 2                 |
| 7               | YouTube           | \4\6\7               | 3                 |

As you can see this table has column called TREE_ORGANIZATION_ID where I store information about relationship of organizations.

This code returns all organizations that have a specific ID in the column TREE_ORGANIZATION_ID. In my case this code return Google and YouTube entries.

SELECT
    *
FROM 
    ORGANIZATIONS
WHERE 
    TREE_ORGANIZATION_ID LIKE '%\' || '6'
OR
    TREE_ORGANIZATION_ID LIKE '%\' || '6' || '\%';

I want to add new column called STATUS which looks like this: 

| ORGANIZATION_ID | ORGANIZATION_NAME | TREE_ORGANIZATION_ID | ORGANIZATION_RANG | STATUS   |
|-----------------|-------------------|----------------------|-------------------|----------|
| 6               | Google            | \4\6                 | 2                 | root     |
| 7               | YouTube           | \4\6\7               | 3                 | not root |

I tried next code but it raise error ORA-00937 not a single-group group function.

How do I create a new column based on the value of another column?

SELECT
    ORGANIZATION_ID,
    ORGANIZATION_NAME,
    TREE_ORGANIZATION_ID,
    CASE
        WHEN ORGANIZATION_RANG = MIN(ORGANIZATION_RANG) THEN 'root'
        ELSE 'not root'
    END AS STATUS
FROM 
    ORGANIZATIONS
WHERE 
    TREE_ORGANIZATION_ID LIKE '%\' || '6'
OR
    TREE_ORGANIZATION_ID LIKE '%\' || '6' || '\%';

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want to use an analytic function, not an aggregation function:

SELECT ORGANIZATION_ID, ORGANIZATION_NAME, TREE_ORGANIZATION_ID,
       (CASE WHEN ORGANIZATION_RANG = MIN(ORGANIZATION_RANG) OVER ()
             THEN 'root'
             ELSE 'not root'
        END) AS STATUS
FROM ORGANIZATIONS O
WHERE TREE_ORGANIZATION_ID || '\' LIKE '%\' || '6' || '\%';

Note that this also simplifies the logic for matching 6 by testing the organization id with a backslash on the end. You could also use REGEXP_LIKE() for this purpose.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below -

SELECT
    ORGANIZATION_ID,
    ORGANIZATION_NAME,
    TREE_ORGANIZATION_ID,
    CASE
        WHEN TREE_ORGANIZATION_ID LIKE '%\' || '6' THEN 'root'
        when TREE_ORGANIZATION_ID LIKE '%\' || '6' || '\%' then 'not root'
    END AS STATUS
FROM 
    ORGANIZATIONS
WHERE 
    TREE_ORGANIZATION_ID LIKE '%\' || '6'
OR
    TREE_ORGANIZATION_ID LIKE '%\' || '6' || '\%'

Upvotes: 2

Related Questions