Post reply

Using CASE in a View

  • April 9, 2018 at 12:01 pm

    #410393

    I have created an employee View to which I need to add a calculated field called Calendar. All my internet searching indicates that I should be using a Case Statement but I have not succeed in creating one. I need one that will pass a value of 1 to the Calendar field if the EmployeeTypeAbbr = Disp, return a 2 if EmployeeTypeAbbr = EXP and 3 if EmployeeTypeAbbr is any other value.

    Any help would be appreciated.

  • April 9, 2018 at 12:42 pm

    #1986015

    The first thing that you should do is to get out of the view designer and use the query editor.
    Then write the query as you want it in a normal SELECT statement. Once it's correct, just append the ALTER or CREATE VIEW statement before the query and run it.

    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

  • April 9, 2018 at 1:29 pm

    #1986025

    Taking your advice I have moved to the query editor and just trying to converter 1 value for now there is still something I am not doing correctly. In this case the value I want to convert is underlined in red. I tried enclosing it in single quotes and double quotes but no luck.

  • April 9, 2018 at 1:36 pm

    #1986026

    ordnance1 - Monday, April 9, 2018 1:29 PM

    Taking your advice I have moved to the query editor and just trying to converter 1 value for now there is still something I am not doing correctly. In this case the value I want to convert is underlined in red. I tried enclosing it in single quotes and double quotes but no luck.

    If DISP is supposed to be a string, it should be surrounded by quotes.

    ,CASE  WHEN EmployeeTypeAbbr = 'DISP' THEN 1
           WHEN EmployeeTypeAbbr = 'EXP' THEN 2
           ELSE 3 END

    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

  • April 10, 2018 at 2:34 pm

    #1986227

    ordnance1 - Monday, April 9, 2018 12:01 PM

    If you get a chance, why don't you learn the ISO 11179 data element naming rules and basic data modeling? A column is nothing whatsoever like a field but you don't seem to know the difference. There is no case statement in SQL; but we had a case expression (expressions return a scalar value while statements do not). Calendar cannot be a column name; just think about it! This is a measurement tool, like a ruler or speedometer. An abbreviation is that method of encoding, and not an attribute property.

    It would also help if you did not post screenshots and pictures; do you understand that people with and who are working for you for free have to copy that data into the DDL you fail to post? This is just rude.

    CREATE VIEW Something
    AS
    SELECT badge_nbr, emp_lastname, emp_firstname, emp_type, something_status, vacation_seniority,
      CASE emp_type WHEN 'DISP' THEN 2 ELSE 3 END AS foobar
    FROM Personnel; 

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • April 10, 2018 at 5:43 pm

    #1986240

    jcelko212 32090 - Tuesday, April 10, 2018 2:34 PM

    ordnance1 - Monday, April 9, 2018 12:01 PM

    If you get a chance, why don't you learn the ISO 11179 data element naming rules and basic data modeling? A column is nothing whatsoever like a field but you don't seem to know the difference. There is no case statement in SQL; but we had a case expression (expressions return a scalar value while statements do not). Calendar cannot be a column name; just think about it! This is a measurement tool, like a ruler or speedometer. An abbreviation is that method of encoding, and not an attribute property.

    It would also help if you did not post screenshots and pictures; do you understand that people with and who are working for you for free have to copy that data into the DDL you fail to post? This is just rude.

    CREATE VIEW Something
    AS
    SELECT badge_nbr, emp_lastname, emp_firstname, emp_type, something_status, vacation_seniority,
      CASE emp_type WHEN 'DISP' THEN 2 ELSE 3 END AS foobar
    FROM Personnel; 

    Still waiting for you to send me my free copies of all the standards you keep hyping.  Sorry, I am not paying for them and I know my company won't.

  • April 11, 2018 at 10:24 pm

    #1986506

    Luis has the answer for you. Just also be aware of the risk with hard coding values in the SQL code. It may be a good idea to have a table that represents these different states (DISP, EXP) with a accompanying ID column. 
    In your code you can join to this 'status' table and join on the ID . No need for case statments.

     
    Select t1.statusID , t2.statusName
    FROM myBaseTable t1
    JOIN myStatusTable t2
    ON t1.statusID=t2.statusID

    THat way if later your status name get s a better name (disp --> displaced) for clarity you dont need to change anything in your code

    ----------------------------------------------------

  • April 24, 2018 at 10:36 am

    #1988076

    MMartin1 - Wednesday, April 11, 2018 10:24 PM

    Luis has the answer for you. Just also be aware of the risk with hard coding values in the SQL code. It may be a good idea to have a table that represents these different states (DISP, EXP) with a accompanying ID column. 
    In your code you can join to this 'status' table and join on the ID . No need for case statments.

     
    Select t1.statusID , t2.statusName
    FROM myBaseTable t1
    JOIN myStatusTable t2
    ON t1.statusID=t2.statusID

    THat way if later your status name get s a better name (disp --> displaced) for clarity you dont need to change anything in your code

    Just be aware that such a table will need maintenance to be sure that new values are added as needed.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • April 24, 2018 at 10:56 am

    #1988079

    sgmunson - Tuesday, April 24, 2018 10:36 AM

    MMartin1 - Wednesday, April 11, 2018 10:24 PM

    Luis has the answer for you. Just also be aware of the risk with hard coding values in the SQL code. It may be a good idea to have a table that represents these different states (DISP, EXP) with a accompanying ID column. 
    In your code you can join to this 'status' table and join on the ID . No need for case statments.

     
    Select t1.statusID , t2.statusName
    FROM myBaseTable t1
    JOIN myStatusTable t2
    ON t1.statusID=t2.statusID

    THat way if later your status name get s a better name (disp --> displaced) for clarity you dont need to change anything in your code

    Just be aware that such a table will need maintenance to be sure that new values are added as needed.

    Fortunately, you can create the front-end with access to the people in charge of giving the appropriate maintenance.

    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

  • April 24, 2018 at 11:08 am

    #1988083

    Luis Cazares - Tuesday, April 24, 2018 10:56 AM

    sgmunson - Tuesday, April 24, 2018 10:36 AM

    MMartin1 - Wednesday, April 11, 2018 10:24 PM

    Luis has the answer for you. Just also be aware of the risk with hard coding values in the SQL code. It may be a good idea to have a table that represents these different states (DISP, EXP) with a accompanying ID column. 
    In your code you can join to this 'status' table and join on the ID . No need for case statments.

     
    Select t1.statusID , t2.statusName
    FROM myBaseTable t1
    JOIN myStatusTable t2
    ON t1.statusID=t2.statusID

    THat way if later your status name get s a better name (disp --> displaced) for clarity you dont need to change anything in your code

    Just be aware that such a table will need maintenance to be sure that new values are added as needed.

    Fortunately, you can create the front-end with access to the people in charge of giving the appropriate maintenance.

    And maintaining values in one table is a little more orderly than maintaining the hard coding in who knows how many SQL scripts.

    ----------------------------------------------------

  • April 24, 2018 at 12:59 pm

    #1988103

    MMartin1 - Tuesday, April 24, 2018 11:08 AM

    Luis Cazares - Tuesday, April 24, 2018 10:56 AM

    sgmunson - Tuesday, April 24, 2018 10:36 AM

    MMartin1 - Wednesday, April 11, 2018 10:24 PM

    Luis has the answer for you. Just also be aware of the risk with hard coding values in the SQL code. It may be a good idea to have a table that represents these different states (DISP, EXP) with a accompanying ID column. 
    In your code you can join to this 'status' table and join on the ID . No need for case statments.

     
    Select t1.statusID , t2.statusName
    FROM myBaseTable t1
    JOIN myStatusTable t2
    ON t1.statusID=t2.statusID

    THat way if later your status name get s a better name (disp --> displaced) for clarity you dont need to change anything in your code

    Just be aware that such a table will need maintenance to be sure that new values are added as needed.

    Fortunately, you can create the front-end with access to the people in charge of giving the appropriate maintenance.

    And maintaining values in one table is a little more orderly than maintaining the hard coding in who knows how many SQL scripts.

    And if it is a static table, the values in it can also be stored in your choice of version control software.

Viewing 11 posts - 1 through 10 (of 10 total)

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