Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: query help please!!!!

    Hi I need help adjusting this query: SELECT STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE , STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE, { fn CONCAT({ fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*') }, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) } AS STUDENT_ACAD_LEVEL, STUDENT_ACAD_LEVELS_VIEW.STA_CLASS, PERSON.LAST_NAME AS UG_Inst, ACAD_CREDENTIALS.ACAD_DEGREE, ACAD_CREDENTIALS.ACAD_GPA, ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID FROM STUDENT_ACAD_LEVELS_VIEW FULL OUTER JOIN STUDENT_ACAD_PROGRAMS_VIEW LEFT OUTER JOIN PERSON RIGHT OUTER JOIN ACAD_CREDENTIALS ON PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ON STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID ON STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID AND STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active') ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID


    I need it so that if the person has more then one institution is will display UG_Inst, ACAD_DEGREE, ACAD_GPA, and ACAD_INSTITUTION_ID for the 2nd institution as well (can be a second column for each value) also, if the acad_institution_ID is 0000004 it should display all of the persons information but nulls for those 4 values.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE
    ,  STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE
    ,     { fn CONCAT(
             { fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*')
             }
    ,     STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) 
          } AS STUDENT_ACAD_LEVEL
    ,  STUDENT_ACAD_LEVELS_VIEW.STA_CLASS
    ,  PERSON.LAST_NAME AS UG_Inst
    ,  ACAD_CREDENTIALS.ACAD_DEGREE
    ,  ACAD_CREDENTIALS.ACAD_GPA
    ,  ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID
       FROM STUDENT_ACAD_LEVELS_VIEW 
       FULL OUTER JOIN STUDENT_ACAD_PROGRAMS_VIEW 
       LEFT OUTER JOIN PERSON 
       RIGHT OUTER JOIN ACAD_CREDENTIALS
          ON PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID
       ON  STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID
       ON  STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID
       AND STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL
       WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active') 
       ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID
    This isn't Microsoft SQL, it looks like either Mimer or possibly Drizzle to me. It would help if you could clarify what language you are using, then I could move the post to somewhere it might get more useful answers.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    This is MSSQL but I am using SSRS to build the query.

    Here is what I have now which puts nulls for the 4 fields when the Institution ID is 0000004. Now I just need to see who has attended more than one institution and put their instution information in those four fields for both institutions like ACAD_GPA1 and ACAD_GPA2

    SELECT STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME,
    STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME,
    STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM,
    STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE ,
    STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE,
    STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE, { fn CONCAT({ fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*') },
    STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) } AS STUDENT_ACAD_LEVEL, STUDENT_ACAD_LEVELS_VIEW.STA_CLASS,
    CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN PERSON.LAST_NAME ELSE NULL END AS UG_Inst,
    CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_DEGREE ELSE NULL END AS ACAD_DEGREE,
    CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_GPA ELSE NULL END AS ACAD_GPA,
    CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ELSE NULL
    END AS ACAD_INSTITUTIONS_ID
    FROM STUDENT_ACAD_LEVELS_VIEW FULL OUTER JOIN
    STUDENT_ACAD_PROGRAMS_VIEW LEFT OUTER JOIN
    PERSON RIGHT OUTER JOIN
    ACAD_CREDENTIALS ON PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ON
    STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID ON
    STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID AND
    STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL
    WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active')
    ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To puts nulls for the 4 fields when the Institution ID is 0000004,
    please try to add the condition in ON clause, like...
    Code:
      ON   STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID
       AND ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004'
    So, CASE expressions would be unnecessary.
    Last edited by tonkuma; 06-27-12 at 18:15. Reason: Capitalize "ACAD_INSTITUTIONS_ID"

  5. #5
    Join Date
    Jun 2012
    Posts
    6
    thank you that works! any insight on how to display multiple schools the person attended?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Now I just need to see who has attended more than one institution and put their instution information in those four fields for both institutions like ACAD_GPA1 and ACAD_GPA2
    How will your expected output columns and its values look like ?

    Why did you mentioned ACAD_GPA1 and ACAD_GPA2 only?
    - Isn't ACAD_INSTITUTIONS_ID key?
    If so, why not to write like "ACAD_INSTITUTIONS_ID1, ACAD_INSTITUTIONS_ID2, ACAD_GPA1 and ACAD_GPA2, so on ..."?
    - At most two institutions? Or, more institutions possible?
    - so on...

  7. #7
    Join Date
    Jun 2012
    Posts
    6
    yes you are correct I need ACAD_GPA1, ACAD_GPA2, ACAD_INSTITUTIONS_ID1, ACAD_INSTITUTIONS_ID2, ACAD_DEGREE1, ACAD_DEGREE2, and UG_Inst1, UG_Inst2 and yes at most I need 2 schools

  8. #8
    Join Date
    Jun 2012
    Posts
    6
    Here is what I need the expected output to be: STUDENT_ID, STUDENT_LAST_NAME, STUDENT_FIRST_NAME, STUDENT_MIDDLE_NAME, STP_ACAD_LEVEL (there could be 2) so STP_ACAD_LEVEL2 (if there isnt one than null), STP_ACADEMIC_PROGRAM (also could be 2) so STP_ACADEMIC_PROGRAM2 (if there isnt one than null), STP_CURRENT_STATUS, STP_CURRENT_STATUS_DATE, STP_START_DATE (one for each acad program) so there could be STP_START_DATE2, STP_END_DATE, STP_ANT_CMPL_DATE (could be one for each program), STUDENT_ACAD_LEVELS_ID (concatenation of STUDENT_ID '*' and STP_ACAD_LEVEL), STA_CLASS, [UG_Inst, ACAD_DEGREE, ACAD_GPA, ACAD_CREDENTIALS_ID] <--- these last 4 fields may have 2 values I so UG_Inst1, UG_Inst2, etc. I do not need to report more than 2 schools. I will include the DDLS for my tables in my next comment. Thank you for any help!!

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My idea is ...

    (1) Put whole your query into a subquery.
    (2) Add ROW_NUMBER() OVER(PARTITION BY ...) function and name it(e.g. row_num).
    (3) GROUP BY in outer(final) select.
    (4) Use CASE expressions, like
    MAX(CASE row_num WHEN 1 THEN STP_ACAD_LEVEL END) AS STP_ACAD_LEVEL
    MAX(CASE row_num WHEN 2 THEN STP_ACAD_LEVEL END) AS STP_ACAD_LEVEL2
    so on...

  10. #10
    Join Date
    Jun 2012
    Posts
    6
    thank you very much

Posting Permissions

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