• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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'
0
ndwHombre
Asked:
ndwHombre
3 Solutions
 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now