Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query help Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 9:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:15 PM
Points: 47, Visits: 477
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!!
Post #1470106
Posted Wednesday, July 3, 2013 9:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470108
Posted Wednesday, July 3, 2013 9:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:56 PM
Points: 33,089, Visits: 15,200
In addition to Eugene's notes, you can use a CASE statement to change things around.

case
When x = 5 then 1
when x = 4 then 2
end








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470111
Posted Wednesday, July 3, 2013 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Steve Jones - SSC Editor (7/3/2013)
In addition to Eugene's notes, you can use a CASE statement to change things around.

case
When x = 5 then 1
when x = 4 then 2
end



If OP only interested in converting of the above two values, he can use just this:

6 - X



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470114
Posted Wednesday, July 3, 2013 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:15 PM
Points: 47, Visits: 477
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_Code 4a 4b 4c 5a 5b 5c 6a 6b 6c 7a 7b 7c 7d 7e
00001 5 4 4 4 3 4 5 5 5 5 4 4 3 4


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
Post #1470119
Posted Wednesday, July 3, 2013 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
6 - X formula will work for you:

SELECT v, 6-v  as converted_v
FROM (VALUES (1),(2),(3),(4),(5)) v(v)



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470123
Posted Wednesday, July 3, 2013 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:15 PM
Points: 47, Visits: 477
Eugene Elutin (7/3/2013)
6 - X formula will work for you:

SELECT v, 6-v  as converted_v
FROM (VALUES (1),(2),(3),(4),(5)) v(v)



Eugene - not sure how i would add this to the query. Can you elaborate?
Post #1470125
Posted Wednesday, July 3, 2013 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
just replace first line of your SELECT query to:

SELECT q.Course_Code, 6-res.[4a] as [4a], 6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as [5a] ... etc.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470131
Posted Wednesday, July 3, 2013 9:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:15 PM
Points: 47, Visits: 477
Eugene Elutin (7/3/2013)
just replace first line of your SELECT query to:

SELECT q.Course_Code, 6-res.[4a] as [4a], 6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as [5a] ... etc.


like this:

SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as converted
FROM
Post #1470142
Posted Wednesday, July 3, 2013 10:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

like this:

SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as converted
FROM


The above willnot compile as has quite few errors. You should be more accurate.
Basically, in order to convert existing returned values as you requested, these values hould be subtrected out of 6, as:
6 - 5 = 1
6 - 4 = 2
6 - 3 = 3
6 - 2 = 4
6 - 1 = 5

Now, if you use the expression when retunring columns, they will need to be given aliases, therefore:
6-res.[4a] as [4a]
,6-res.[4b] as [4b]
,...
etc.



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470148
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse