replace integer with string in query

Question

I have searched for an answer and have not been able to find one.

I have a table that records an integer instead of text. I am trying to run a query and substitute text for a specific integer.

here are the integers in column cal_referral_type (this column is int) with the correlating text it represents:

DO NOT TRACK|1
Carey Guide |2
Education   |3
Employment  |4
Housing     |5
Medical     |6

I need to run a report to see how many referrals are going to each category but staff get confused with just the number.

If I run a query:

SELECT cal_owner,cal_title,cal_referral_type 
FROM egw_cal 
WHERE cal_referral_type = 3

It shows all referrals to educational services but the column results in a "3" instead of descriptive text "education"

I have read that I can use a CASE Statement such as:

SELECT cal_owner,cal_title, CASE cal_referral_type
WHEN 1 THEN 'NA' ELSE 'other' FROM `egw_cal`

Each time I twist the query a different way, it just errors.

So, is there a way I can have the results of the query show "education" each time the integer "3" is identified? I will expand to include all categories once I can get one to work.

Harper Shelby · Accepted Answer · 2014-07-10 17:11:16Z

1

A CASE statement should work, but a better/more flexible approach is to have a lookup table with the integer values and descriptive text, and then join to that table and pull the text out. This makes changes easier to implement, as you update/add to the lookup table instead of altering code. It also helps implement user-friendly interfaces in a cleaner way if you need to do that.

answered Jul 10, 2014 at 17:11

Harper Shelby

16.6k2 gold badges47 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Detox Over a year ago

Harper, Thanks for the response. Would you have any idea why my query will not work? I am not sure if I can create another table without the developer's permission. That is why I was hoping to substitute a descriptive word for the integer that is in the table when I run a query

Harper Shelby Over a year ago

The biggest thing I can see in your posted code is the missing "END CASE" (e.g. SELECT cal_owner, cal_title, CASE cal_referral_type WHEN 1 THEN 'NA' ELSE 'other' END CASE AS referral_type FROM 'egw_cal'. The reference page is at dev.mysql.com/doc/refman/5.0/en/case.html

Detox Over a year ago

Thanks again! When I use your query above this is the error I receive:

Detox Over a year ago

When I use your query above this is the error I receive: Error SQL query: SELECT cal_owner, cal_title, CASE cal_referral_type WHEN 1 THEN 'NA' ELSE 'other' END CASE AS referral_type FROM 'egw_cal' LIMIT 0 , 30 MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE AS referral_type FROM 'egw_cal' LIMIT 0, 30' at line 1

Detox Over a year ago

I found the solution. The complete query adds in date. Here is how it looks: SELECT cal_owner, cal_title, FROM_UNIXTIME(range_start, "%Y-%m-%d")AS Date, CASE cal_referral_type WHEN 1 THEN 'NoReferral' WHEN 2 THEN 'CareyGuide' WHEN 3 THEN 'Education' WHEN 4 THEN 'Employment' WHEN 5 THEN 'Housing' WHEN 6 THEN 'Medical' ELSE 'NA' END AS referrals FROM egw_cal There were some grave typo's that were the main problems Thanks for your input as it get me going in the right direction

|

Collectives™ on Stack Overflow

replace integer with string in query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related