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

    Unanswered: Case ... then ... end dilema

    I've been the whole day working in this query and I came close but not enough.

    I have 2 date columns, DUE_DATE and EXTENSION_NEW_DUE_DATE, which hold a document due date and new extension due date respectively. I also have 2 columns, DAYS_OVERDUE and STATUS. DAYS_OVERDUE = the lower of the DUE_DATE and EXTENSION_NEW_DUE_DATE since empty EXTENSION_NEW_DUE_DATE = 01/01/0001. If DAYS_OVERDUE <= 0 STATUS = ON SCHEDULE otherwise is OVERDUE.

    I managed to calculate the DUE_DATE and EXTENSION_NEW_DUE_DATE but can't make Oracle decide which to use.

    I know that the approach is something like:
    Code:
    select case when DUE_DATE < EXTENSION_NEW_DUE_DATE then DUE_DATE else EXTENSION_NEW_DUE_DATE end from table
    But since it's a view and all the mentione columns are from the same table the thing gets ugly.

    Here's the SQL that shows both results:
    Code:
    select round(sysdate - to_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= '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'))),round(sysdate - to_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= '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'))) FROM SGRP_INF, SGRP_TST WHERE SGRP_INF.F_SGRP=SGRP_TST.F_SGRP  AND SGRP_INF.F_FLAG = 0
    And the result:
    Code:
    179	161
    183	1740
    194	1740
    197	1740
    229	1740
    229	1740
    89	1740
    231	1740
    231	191
    172	1740
    112	1740
    Where the right column is calculated from DUE_DATE. In this example the choice is 161 for the first row. SO I guess the SQL should be like this:

    Code:
    select case when round(sysdate - to_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= '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')))<round(sysdate - to_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= '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'))) then  round(sysdate - to_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= 'DUE DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) else round(sysdate - to_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= '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'))) end FROM SGRP_INF, SGRP_TST WHERE 
    SGRP_INF.F_SGRP=SGRP_TST.F_SGRP  AND SGRP_INF.F_FLAG = 0
    But it doesn't work. It displays missing from keyword. Any ideas?

    BTW if you know how to format the code better let me know.

  2. #2
    Join Date
    Sep 2005
    Posts
    22
    The select you have posted looks strange
    there is a to_date of a to_date which may account for the error message
    D.F_SGRP and SGRP_DSC.F_SGRP appear to be the same value

    maybe this will work

    select aa.due,ee.ext 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;

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    There was a minor mistake in your sql. Here's the correction:

    Code:
    select dd.due,ee.ext 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;
    But it returns an empty result set. I tried all subqueries separately and dd and ee bringgs empty result sets. I'll be trying to figure it out, but wanted to tell you just in case you find it before I do...

  4. #4
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    The sql is correct, I was looking in the wrong database. Anyways even if the result are there I want the column to be the lowest of the 2 values. Any idea?

  5. #5
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Finally got it! Thanks pal! Here's the final sql statement:
    Code:
    select case when due < ext then due else ext   end  from (select dd.due as due,ee.ext as ext 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)
    Thanks a lot!

Posting Permissions

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