Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: updating course tables

    Hey All,

    I've been stuck on this update for a while. I'm trying to update some new cipc codes for all the courses that need the code and are unique by a combination of crse, subj, and max term code effective. The data out put of what I show here gets unique rows but only for term code 201108 not the "max effective term code" for which that course was updated.

    UPDATE scbcrse b

    scbcrse_cipc_code =(SELECT DISTINCT (ycipcre_new_cip)
    FROM regmaster.ycipcre
    WHERE scbcrse_cipc_code = ycipcre_old_cip)
    WHERE EXISTS
    (SELECT scbcrse_cipc_code
    FROM scbcrse, scrlevl, regmaster.ycipcre
    WHERE scbcrse_cipc_code = ycipcre_old_cip
    AND ycipcre_new_cip IS NOT NULL
    AND scbcrse_subj_code = scrlevl_subj_code
    AND scbcrse_crse_numb = scrlevl_crse_numb)

    AND scbcrse_eff_term =(SELECT MAX(scbcrse_eff_term)
    FROM scbcrse b,scrlevl,regmaster.ycipcre
    WHERE scbcrse_eff_term <= '201108'
    AND scbcrse_eff_term = b.scbcrse_eff_term
    AND scbcrse_subj_code = b.scbcrse_subj_code
    AND scbcrse_crse_numb = b.scbcrse_crse_numb
    AND scbcrse_cipc_code = ycipcre_old_cip
    AND ycipcre_new_cip is NOT NULL)
    AND scbcrse_csta_code = 'A'

    Anyone have any ideas on how to get my courses updated with the correct (MAX)scbcrse_eff_term) ? Any advise would be great. Thanks all!

    --Nathan Streetman

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The data out put of what I show here gets unique rows but only for term code 201108 not the "max effective term code"
    Code is difficult to understand as you didn't format it, but this:
    Code:
    WHERE scbcrse_eff_term <= '201108'
    might explain your statement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE scbcrse_eff_term <= '201108'

    is SCBCRSE_EFF_TERM really a VARCHAR2 datatype?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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