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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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

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
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
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
Oracle Database

From novice to tech pro — start learning today.