Query help

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

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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?

  • 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]

  • 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

  • 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]

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

  • 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?

  • any help?

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

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

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