1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Rewrite create view query using cte

Discussion in 'General' started by sania, Jul 5, 2018.

  1. sania

    sania Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    india
    This is the below query, I want to rewrite using CTE. please help.

    CREATE OR REPLACE VIEW "Mx"."Dim_abc_v"
    ("abc", "abc", "pqr", "l_disble", "l_rpair",
    "hij", "date1", "lmn", "ckey", "par", "ttech", "date1_P_C") AS
    SELECT O.A_id abc,
    UPPER (L.abc),

    pvl.desc pqr,

    Mx.F_FLAG (L.disbl, 'Y'),

    Mx.F_FLAG (L.rpair, 'Y'),

    LOP.hij,
    L.date1,
    lop.lmn,

    NVL2 (L.clsstrucid,
    L.clsstrucid || 'abcS',
    L.clsstrucid)
    ckey,
    Mx.F_par (GREATEST (Mx.fn (L.date1), Mx.fn (lspec.date1))),
    --Mx.F_par (L.date1),
    lspec.tch,
    Mx.fn ( lspec.date1) date1_P_C

    FROM M.ste S,
    M.abcS L,
    M.abc O,
    M.abcpr lop,
    (SELECT *
    FROM M.plus pvl
    WHERE pvl.name = 'blah'
    AND pvl.k_1 != 'blah') pvl,
    (SELECT la.abc, la.Stid, et.tch,lo.date1
    FROM M.abcs lo,
    M.clsstruc c,
    Mx.e_m_s et,
    M.lancestr la
    WHERE c.clsid = 'UNIT'
    AND c.clsstrucid = lo.clsstrucid
    AND et.id = lo.abcsid
    AND et.tflag = 4
    AND la.ancestr = lo.abc
    AND la.Stid = lo.Stid
    AND LA.sysid = 'PRIMARY') lspec
    WHERE S.Stid = L.Stid
    AND L.A_id = O.A_id
    AND L.abc = lop.abc(+)
    AND L.Stid = lop.Stid(+)
    AND L.A_id = lop.A_id(+)
    AND s.A_id = l.A_id
    AND L.efs = pvl.k_1(+)
    AND l.abc = lspec.abc(+)
    AND l.Stid = lspec.Stid(+)
    UNION ALL
    SELECT '1.1' abc,
    '1.1' abc,
    'Dummy System desc' pqr,

    'NA' disbl,

    NULL rpair,

    NULL hij,
    TO_DATE ('07/13/2015 12:00:01 AM', 'MM/DD/YYYY HH:MI:SS AM')
    date1,
    'Dummy mtr grp' lmn,


    NULL ckey,
    1,
    NULL,
    TO_DATE ('07/13/2015 12:00:01 AM', 'MM/DD/YYYY HH:MI:SS AM')
    date1_P_C
    FROM DUAL;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,610
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to provide table definitions and sample data for the underlying tables this view is based upon and some information as to HOW you plan on using CTE to do this.

    You have not provided enough information to proceed further.