Display what column a condition came from

I have the following view:

 DEVICENAME                 LEVEL1_TOP     LEVEL2              LEVEL3              LEVEL4             LEVEL5              LEVEL6    
 ---------------                  -----------------   -------------         -------------         -------------         -------------         -------------
 DTNWWWRNW-R990X  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-N6140  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-N6077  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-2R8X0  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-619L5  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-5187V  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-N6049  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-20WLM  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-9A4C6  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-R9898  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    
 DTNWWWRNW-41CQ7  RDSHR              CORPORATE      RD00003921     RD00035504     RD00200030     RD00200249    


I want to pull all devices that correspond a code (ex: RD00200249).  I won't know what level the code is at.
I'm using the statement below but I would like to add a column in the result that displays what level the code appeared in.

select  * form view_devices where
    LEVEL1_TOP = 'RD00200249'
 or LEVEL5 = 'RD00200249'
 or LEVEL6 = 'RD00200249'
 or LEVEL4 = 'RD00200249'
 or LEVEL3 = 'RD00200249'
 or LEVEL2 = 'RD00200249'
ndwHombreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MilleniumaireCommented:
Hi,
My first thoughts when I look at your data is that it really needs to be normalised.  Of course I may be wrong and you are using a view to denormalise the data, but if that were the case you would be able to retrieve the required level information from the base tables rather than the view.

I would expect to see a table called DEVICE_LEVELS with at least three columns:
devicename
levelname
level

You would then be able to write a much simpler, more efficient query like:

select  * form device_levels where
    LEVELNAME= 'RD00200249'

However, to answer your question you could write the following query:

select  devicename,
           CASE 'RD00200249'
                 WHEN LEVEL1_TOP THEN 'LEVEL1_TOP'
                 WHEN LEVEL2 THEN 'LEVEL2'
                 WHEN LEVEL3 THEN 'LEVEL3'
                 WHEN LEVEL4 THEN 'LEVEL4'
                 WHEN LEVEL5 THEN 'LEVEL5'
                 ELSE 'LEVEL6'
          END
from view_devices where
    LEVEL1_TOP = 'RD00200249'
 or LEVEL5 = 'RD00200249'
 or LEVEL6 = 'RD00200249'
 or LEVEL4 = 'RD00200249'
 or LEVEL3 = 'RD00200249'
 or LEVEL2 = 'RD00200249'


0
Jinesh KamdarCommented:
In Oracle, try this.
SELECT v.*,
       DECODE('RD00200249', LEVEL1_TOP, 'LEVEL1_TOP',
                            LEVEL2    , 'LEVEL2',
                            LEVEL3    , 'LEVEL3'
                            LEVEL4    , 'LEVEL4'
                            LEVEL5    , 'LEVEL5'
                            LEVEL6    , 'LEVEL6') AS level
FROM view_devices v
WHERE LEVEL1_TOP = 'RD00200249'
 OR   LEVEL5     = 'RD00200249'
 OR   LEVEL6     = 'RD00200249'
 OR   LEVEL4     = 'RD00200249'
 OR   LEVEL3     = 'RD00200249'
 OR   LEVEL2     = 'RD00200249'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ee_rleeCommented:
hi

if you are using ms access, you can use switch.
you can also simplify your condition by using IN.
select  *,
        SWITCH(LEVEL1_TOP='RD00200249', 'LEVEL1_TOP',
               LEVEL2 ='RD00200249', 'LEVEL2',
               LEVEL3 ='RD00200249', 'LEVEL3',
               LEVEL4 ='RD00200249', 'LEVEL4',
               LEVEL5 ='RD00200249', 'LEVEL5',
               1=1, 'LEVEL6') AS LEVELFOUND
from view_devices 
where 'RD00200249' IN (LEVEL1_TOP,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.