#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Effective Dated error


    I have a query where I'm trying to get one row for a student that is their academic program. A view PS_NTSR_PRGPLN_VW returns all active programs. This student has three.

    The query is pretty straightforward but I'm not getting any rows, and I think it is because I'n not using MAX(pe.effdt) correctly.

    Code:
    select * from PS_NTSR_PRGPLN_VW h
     where h.emplid = 'xxxxxx'
     AND h.institution = 'ABCD'
       AND h.acad_plan_type = 'MAJ'
       AND h.primary_indicator = 'Y'
       AND h.plan_sequence = (
     SELECT MIN(hh.plan_sequence)
      FROM PS_NTSR_PRGPLN_VW hh
     WHERE h.emplid = hh.emplid
       AND h.institution = hh.institution
       AND h.effdt = hh.effdt)
       AND h.effdt = (
     SELECT MAX(pe.effdt)
      FROM PS_NTSR_PRGPLN_VW pe
     WHERE pe.emplid = h.emplid
       AND pe.institution = h.institution
       AND pe.effdt <= sysdate);
    I've had rows where I have the same plan_sequence so I do need to use MAX(pe.effdt), but when I do I get zero results.
    Attached Images
    • File Type: png 6.png (8.5 KB, 34 views)
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    854
    Rep Power
    387

    Cool


    Originally Posted by SFDonovan
    I have a query . . . E t c
    . . . but I'm not getting any rows, and I think it is because I'n not using MAX(pe.effdt) correctly.
    The "h.effdt = (SELECT MAX (pe.effdt)" part is returning the MAX(effdt) of all rows, you need to restrict as follows:
    Code:
    SELECT *
      FROM ps_ntsr_prgpln_vw h
     WHERE h.emplid = 12345
       AND h.institution = 'ABCD'
       AND h.acad_plan_type = 'MAJ'
       AND h.primary_indicator = 'Y'
       AND h.plan_sequence = (SELECT MIN (hh.plan_sequence)
                                FROM ps_ntsr_prgpln_vw hh
                               WHERE h.emplid = hh.emplid
                                 AND h.institution = hh.institution
                                 AND h.effdt = hh.effdt)
       AND h.effdt = (SELECT MAX (pe.effdt)
                        FROM ps_ntsr_prgpln_vw pe
                       WHERE pe.emplid = h.emplid
                         AND pe.institution = h.institution
                         AND pe.acad_prog = h.acad_prog
                         AND pe.effdt <= SYSDATE);

    Comments on this post

    • SFDonovan agrees : Thanks

IMN logo majestic logo threadwatch logo seochat tools logo