CODEWITHSUNDEEP
sql
Tai Lung
Tai Lung

Reputation: 89

create sql view with case

I have a table named VWDRSSTA and it has the following fields SYSTEM, EREIGNIS; DATUM_ZEIT, ANTRAGSNUMMER,VORGANGSNUMMER,VERS_NR_INT, DOK_ART, DUNKEL

I am looking to create a view of this table with a filter in two fields DOK_ART and DUNKEL using CASE statement. Here is what I tried doing

CREATE VIEW VWDRSSTA_VIEW As
SELECT SYSTEM, EREIGNIS, DATUM_ZEIT, ANTRAGSNUMMER, VORGANGSNUMMER, VERS_NR_INT,
CASE 
 WHEN EREIGNIS = 'EIN-ES' AND DOK_ART = 'EN' Then 'EN'
ELSE ''
END
CASE
 WHEN EREIGNIS = 'POL_AN' AND DUNKEL = 1 Then 1
ELSE ''
END
FROM VWDRSSTA;

Which is going wrong. How can I achieve the above?

Upvotes: 1

Views: 3399

Answers (1)

daZza
daZza

Reputation: 1689

Try this:

    CREATE OR REPLACE FROCE VIEW VWDRSSTA_VIEW As
SELECT SYSTEM, EREIGNIS, DATUM_ZEIT, ANTRAGSNUMMER, VORGANGSNUMMER, VERS_NR_INT,
      CASE
        WHEN EREIGNIS = 'EIN-ES' AND DOK_ART = 'EN'
        THEN 'EN'
        ELSE ''
      END AS DOK_ART,
      CASE
        WHEN EREIGNIS = 'POL_AN' AND DUNKEL = 1
        THEN 1
        ELSE ''
      END AS DUNKEL           
    FROM
      VWDRSSTA;

Upvotes: 3

Related Questions