Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Unanswered: Base column contents on other column contents in the same table. It's possible???

    I have a huge View set up in my Oracle Database (8i). I wanted to know if you're able to use a column value from the same view as condition in a case statement.

    Do something like:
    Code:
    select case when days_overdue> 0 THEN 'OVER DUE'  ELSE 'ON SCHEDULE'  end  from 
    spctrend
    where days_overdue is a column from the same view; avoiding [Error Code: 1731, SQL State: 42000] ORA-01731: circular view definition encountered error?

    Do I need to substitude days_overdue with its subquery statement?

    Is creating another views to feed this one the only viable option?

    It's so redundant in my opinion. I guess there's a better way.

    Just in case you need more info here's the complete view statement:
    Code:
    CREATE OR REPLACE  VIEW SPCTrend AS ( SELECT  
    (SELECT F_NAME FROM SHFT_DAT A WHERE SGRP_INF.F_SPLT = A.F_SHFT) AS Shift, 
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP 
    AND B.F_NAME= 'NCR NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS NCR,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'PRR NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS PRR,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'RISK LEVEL' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS RISK,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'LINE/MACHINE NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS LINE_MACHINE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'REASON' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS REASON,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'DATE GENERATED' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS GENERATED,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND
     B.F_NAME= 'INCIDENT DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS INCIDENT_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'RESPONSIBLE PERSON' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS RESPONSIBLE_PERSON,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'DUE DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS DUE_DATE,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND
     B.F_NAME= 'APPROVAL DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS APPROVAL_DATE,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMPLETION DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS COMPLETION_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMPLETED BY' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS COMPLETED_BY,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'EXTENSION NEW DUE DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP),'MM/DD/YYYY') AS EXTENSION_NEW_DUE_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'EXTENSIONS' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS EXTENSIONS,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'ROOT CAUSE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS ROOT_CAUSE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'STATUS' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS STATUS,
    (select case when due < ext then due else ext   end  from (select dd.due as due,ee.ext as ext  ,dd.idn as idn  from (SELECT c.F_sgrp idn, 
    round(sysdate-to_date(A.F_NAME,'MM/DD/YYYY')) due FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE 
    A.F_DSGP = B.F_DSGP AND B.F_NAME= 'DUE DATE' AND C.F_SGRP = D.F_SGRP AND A.F_DESC = D.F_DESC AND C.F_FLAG = 0 ) dd,
    (SELECT c.F_sgrp idn, round(sysdate-to_date(A.F_NAME,'MM/DD/YYYY')) ext FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D 
    WHERE A.F_DSGP = B.F_DSGP AND B.F_NAME= 'EXTENSION NEW DUE DATE' AND C.F_SGRP = D.F_SGRP AND A.F_DESC = D.F_DESC AND
     C.F_FLAG = 0 ) ee,(select F_SGRP idn from SGRP_TST) tt WHERE tt.idn=dd.idn AND tt.idn=ee.idn) where idn=SGRP_INF.F_SGRP) as DAYS_OVERDUE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'TURN AROUND TIME' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS TURN_AROUND_TIME,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'OVERDUE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS OVERDUE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMMENT' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS COMMENTS,
    (SELECT A.F_NAME  FROM PART_DAT A WHERE A.F_PART =SGRP_INF.F_PART) AS PART, 
    (SELECT A.F_NAME FROM PRCS_DAT A WHERE A.F_PRCS =SGRP_INF.F_PRCS)AS PROCESS, 
    (SELECT A.F_NAME FROM TEST_DAT A WHERE A.F_TEST =SGRP_TST.F_TEST) AS TEST, 
    (SELECT A.F_NAME FROM  PART_LOT A WHERE A.F_LOT = SGRP_INF.F_LOT) AS LOT,
    (SELECT A.F_NAME FROM EMPL_INF A WHERE A.F_EMPL =SGRP_INF.F_EMPL) AS EMPLOYEE 
     FROM SGRP_INF, SGRP_TST WHERE SGRP_INF.F_SGRP=SGRP_TST.F_SGRP  AND SGRP_INF.F_FLAG =0)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can nest queries like this:

    Code:
    select x, y, days_overdue, case when days_overdue> 0 THEN 'OVER DUE'  ELSE 'ON SCHEDULE'  end  from 
    (select x, y, z, col1+col2 as days_overdue
     from ...
    )
    That view definition is a bit hairy, isn't it? Do we have the dreaded EAV and OTLT models here by any chance?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •