VIEWS WITH CASE CONDITION

[nouf]

HI,,
I WANT TO KNOW IF I CAN CREATE VIEW WITH CASES, FOR EXAMPLE
IF I HAVE FOUR TABLES LIKE THE FOLLOWING

EMPLOYEE TABLE
---------------
EMP_CODE EMP_NAME DEPT_CODE SEC_CODE
-------- -------- --------- --------
1 EMP1 2 2
2 EMP2 1001 1002
3 EMP3 1 1


DEPT TABLE
----------
D_CODE D_NAME D_TYPE
------ ------ ------
1 DEPT1 'D'
2 DEPT2 'D'
1001 FED1 'F'
1002 FED2 'F'

SECTIONS TABLE
--------------
S_CODE DEPT_CODE S_NAME
------ --------- ------
1 1 SEC1
2 2 SEC2
3 2 SEC3

CLUBS TABLE
------------
C_CODE CLUB_NAME
------ ---------
1001 CLUB1
1002 CLUB2
1003 CLUB3

I WANT TO CREATE A VIEW CONTAINING THE FOLLOWING
EMP_CODE EMP_NAME DEPT SECTION
-------- -------- ------ --------
1 EMP1 DEPT2 SEC2
2 EMP2 FED1 CLUB2
3 EMP3 DEPT1 SEC1

WHERE THE VIEW BRINGS THE SECTION DEPENDING ON THE DEPARTMENT IF ITS TYPE = 'F' THEN IT WILL LOOK FOR THE SECTION FROM THE CLUB TABLE AND IF DEPT TYPE = 'D' THEN IT WILL LOOK FOR THE SECTION IN THE SECTION TABLE..

 

[lewisp]

Try something like

SELECT Decode(d.d_type,
              'F',c.club_name,
              'D',s.s_name,
              'default section') section_name
FROM   dept     d,
       sections s,
       clubs    c
WHERE  <join conditions>

 

[carp]

Since a view is just a stored query, if your query works, your view should work. Have you tried to build a view with a CASE statement? If so, what were your results? If not, why not?