CODEWITHSUNDEEP
oracle-databaseviewwhere-clausecase-when
Marcus
Marcus

Reputation: 3869

Add condition in where clause in Oracle View

I have below Oracle View which is working fine. But now i want to add combination of condition like below:

EUREX_ROOT <> ROOT
AND EUREX_ROOT = 'BAY'

So i want all the remaining data excluding the data with the above combination of condition but dont know how to achive this in this View.

In BB_IMPORT table field EUREX_ROOT is not present and i am using case condition to generate it so there might be issue with the alias here if we try to use this field in where condition.

CREATE OR REPLACE VIEW
    BB_IMPORT_TEST
    (        
        ROOT,
        EUREX_ROOT
    ) AS
    (
        SELECT
        ROOT,
        CASE
            WHEN exch_code IN ('EUX')                
            THEN ID_EXCH_SYMBOL            
            ELSE NULL
        END AS "EUREX_ROOT" from BB_IMPORT)

Upvotes: 0

Views: 125

Answers (1)

Littlefoot
Littlefoot

Reputation: 142733

Wouldn't that be

SELECT root, eurex_root
  FROM (SELECT ROOT,
               CASE
                  WHEN exch_code IN ('EUX') THEN ID_EXCH_SYMBOL
                  ELSE NULL
               END
                  AS EUREX_ROOT
          FROM BB_IMPORT)
 WHERE NOT (    EUREX_ROOT = ROOT
            AND EUREX_ROOT <> 'BAY')

Upvotes: 2

Related Questions