Query help

Question

Query help

Keejli69

Ten Centuries

Points: 1307
More actions
July 3, 2013 at 9:10 am

#395081

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!!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 2

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Keejli69 Ten Centuries Points: 1307 More actions · Answer 4

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Keejli69 Ten Centuries Points: 1307 More actions · Answer 6

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?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Keejli69 Ten Centuries Points: 1307 More actions · Answer 8

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Keejli69 Ten Centuries Points: 1307 More actions · Answer 10

Eugene Elutin (7/3/2013)
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.

Thanks Eugene !! I appreciate your help !!

Keejli69 Ten Centuries Points: 1307 More actions · Answer 11

elee1969 (7/3/2013)
Eugene Elutin (7/3/2013)
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.
Thanks Eugene !! I appreciate your help !!

Right now 6 - 0 returns 6. What if i want to keep it as 0?

Keejli69 Ten Centuries Points: 1307 More actions · Answer 12

Keejli69

Ten Centuries

Points: 1307

July 3, 2013 at 1:45 pm

#1629654

any help?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

You can use a CASE statement.

SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],

...

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Keejli69 Ten Centuries Points: 1307 More actions · Answer 14

Luis Cazares (7/3/2013)
You can use a CASE statement.
SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],
...

ah thanks luis. 🙂 i knew it would be the CASE statement but didn't know how i would work it into the Select section. Thanks again !!