Eugene Elutin (7/3/2013)
elee1969 (7/3/2013)
Hello - I was handed a script to modify. I am an accidental DBA. I am confused on how to modify a query to return certain results. For example when the script returns a 5 i want it to display a 1, when it returns a 4 i want to display a 2 etc.. Any help would be great.Thanks!!
In order to help you we need a bit more details from you.
What dio you mean by "script return"?
Is it just a query script or a stored procedure?
Does it return recordset and it's a value in one of its columns, or it is atored proc return value or output parameter?
It is a query that pulls survey results. When the script pulls a survey result we want the 5's to display as 1's in the result. We don't want to change it in the tables. We just want the results to display as a different number.
SELECT q.Course_Code, res.[4a],res.[4b],res.[4c],res.[5a],res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e]
FROM
SELECT
SUBSTRING(section_master.crs_cde,1,2)+' '+rtrim(SUBSTRING(section_master.crs_cde,6,5))+' '+RTRIM(substring(section_master.crs_cde,11,3))+' - '+SECTION_MASTER.CRS_TITLE as course_title,
(rtrim(cast(SECTION_MASTER.REQUEST_NUM as CHAR))+'-'+rtrim(CAST((ROW_NUMBER() OVER (PARTITION BY section_master.REQUEST_NUM ORDER BY faculty_load_table.LEAD_INSTRCTR_FLG desc, faculty_load_table.INSTRCTR_ID_NUM asc)) as CHAR))) as Course_Code,
faculty_load_table.INSTRCTR_ID_NUM as fac_id
FROM NAME_MASTER, SECTION_MASTER,FACULTY_LOAD_TABLE
WHERE FACULTY_LOAD_TABLE.INSTRCTR_ID_NUM = NAME_MASTER.ID_NUM
AND SECTION_MASTER.YR_CDE = FACULTY_LOAD_TABLE.YR_CDE
AND SECTION_MASTER.TRM_CDE = FACULTY_LOAD_TABLE.TRM_CDE
AND SECTION_MASTER.CRS_CDE = FACULTY_LOAD_TABLE.CRS_CDE
AND FACULTY_LOAD_TABLE.INSTRCTR_ID_NUM IN (SELECT ID_NUM FROM EMPL_MAST)
AND SECTION_MASTER.YR_CDE = 2007 AND SECTION_MASTER.TRM_CDE = 'Q1'
AND SECTION_MASTER.INSTITUT_DIV_CDE in ('AE')
) q, mse_ccsd_survey_results res
WHERE res.id_num = q.fac_id
AND res.REQUEST_NUM = SUBSTRING(q.Course_Code, 1,LEN(q.Course_Code)-2)
this returns something like this:
Course_Code4a4b4c5a5b5c6a6b6c7a7b7c7d7e
00001 54443455554434
so the basically all the 5's we want them to display as 1's in the results, 4 displays as 2, 3 stays the same, 2 display as 4 and 1 display as 5