Oracle View PL/SQL

I'm having issue with this VIEW. I want to create a view that has an additional column that I create only for the view and fill in the values when the criteria is met. I'm having issues with the CASE statement. When the value is NULL I want the field FLAG = 0 and when its NOT NULL the FLAG = 1

Thank you

CREATE OR REPLACE FORCE VIEW V_ALL_RULES_STMT
AS
  SELECT DISTINCT P.PUBID,
    DECODE(RA.O_FARULE_ID, NULL, 'Child', 'Prime') RTYPE,
    FE.ENTY_ID,
    P.PUB_SHRT_TTL_TX,
    P.PUB_TTL_TX,
    P.PUB_TYP_CD,
    P.PUB_ISS_C_DT,
    P.PUB_RVW_TX,
    S.FASTMT_ID,
    S.FASTMT_SUB_REF_CD,
    S.FASTMT_SUB_REF_ID,
    S.FASTMT_SUB_REF_CMNT_TX,
    S.FASTMT_DEF_TX,
    S.FAID,
    FA.FAHIERID,
    FA.FANM,
    R.FARULEID,
    R.FARULE_SUB_REF_CD,
    R.FARULE_SUB_REF_ID,
    R.FARULE_SUB_REF_CMNT_TX,
    R.FARULE_DEF_TX,
    R.FARULE_SRC_NM,
    R.FARULE_TY_NM,
    (
    CASE
      WHEN FE.farentyid IS NULL
      THEN '1' FLAG
      WHEN farentyid IS NOT NULL
      THEN '0' FLAG
      ELSE
    END) AS FLAG
  FROM PUB P,
    STMT S,
    ACTVTY FA,
    RULE R,
    RULE_ENTY FE,
    RULE_ASC RA
  WHERE P.PUBID  = S.PUBID
  AND S.FAID     = FA.FAID
  AND S.FASTMTID = R.FASTMTID
  AND R.FARULEID = RA.OFARULEID (+)
  AND R.FARULEID = FE.FARULEID (+);
lulubell-bAsked:
Who is Participating?
 
sdstuberCommented:
you also try nvl2 instead of case
CREATE OR REPLACE FORCE VIEW v_all_rules_stmt
AS
    SELECT DISTINCT p.pubid,
                    DECODE(ra.o_farule_id, NULL, 'Child', 'Prime') rtype,
                    fe.enty_id,
                    p.pub_shrt_ttl_tx,
                    p.pub_ttl_tx,
                    p.pub_typ_cd,
                    p.pub_iss_c_dt,
                    p.pub_rvw_tx,
                    s.fastmt_id,
                    s.fastmt_sub_ref_cd,
                    s.fastmt_sub_ref_id,
                    s.fastmt_sub_ref_cmnt_tx,
                    s.fastmt_def_tx,
                    s.faid,
                    fa.fahierid,
                    fa.fanm,
                    r.faruleid,
                    r.farule_sub_ref_cd,
                    r.farule_sub_ref_id,
                    r.farule_sub_ref_cmnt_tx,
                    r.farule_def_tx,
                    r.farule_src_nm,
                    r.farule_ty_nm,
                    NVL2(farentyid, 0, 1) AS flag
    FROM pub p,
         stmt s,
         actvty fa,
         rule r,
         rule_enty fe,
         rule_asc ra
    WHERE p.pubid = s.pubid
    AND   s.faid = fa.faid
    AND   s.fastmtid = r.fastmtid
    AND   r.faruleid = ra.ofaruleid(+)
    AND   r.faruleid = fe.faruleid(+);

Open in new window

0
 
sdstuberCommented:
don't alias inside the CASE,  and your ELSE is empty

CREATE OR REPLACE FORCE VIEW v_all_rules_stmt
AS
    SELECT DISTINCT
           p.pubid,
           DECODE(ra.o_farule_id, NULL, 'Child', 'Prime') rtype,
           fe.enty_id,
           p.pub_shrt_ttl_tx,
           p.pub_ttl_tx,
           p.pub_typ_cd,
           p.pub_iss_c_dt,
           p.pub_rvw_tx,
           s.fastmt_id,
           s.fastmt_sub_ref_cd,
           s.fastmt_sub_ref_id,
           s.fastmt_sub_ref_cmnt_tx,
           s.fastmt_def_tx,
           s.faid,
           fa.fahierid,
           fa.fanm,
           r.faruleid,
           r.farule_sub_ref_cd,
           r.farule_sub_ref_id,
           r.farule_sub_ref_cmnt_tx,
           r.farule_def_tx,
           r.farule_src_nm,
           r.farule_ty_nm,
           (CASE WHEN fe.farentyid IS NULL THEN '1' WHEN farentyid IS NOT NULL THEN '0' END)
               AS flag
    FROM pub p,
         stmt s,
         actvty fa,
         rule r,
         rule_enty fe,
         rule_asc ra
    WHERE p.pubid = s.pubid
    AND   s.faid = fa.faid
    AND   s.fastmtid = r.fastmtid
    AND   r.faruleid = ra.ofaruleid(+)
    AND   r.faruleid = fe.faruleid(+);

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Try this:
...
    CASE
      WHEN FE.farentyid IS NULL
      THEN '1'
      WHEN farentyid IS NOT NULL
      THEN '0'
      ELSE
    END) AS FLAG
...
0
 
lulubell-bAuthor Commented:
Thank you, It works perfect.
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.