SQLTeam.com | Weblogs | Forums

Query is not working and I don't know why?

I am fairly new to SQL and am doing a project with mySQL. Here is the my sql statement. As you can see from the attached spreadsheet, column F is zero and column G (titled END) shouldn't even be there. What am I doing wrong? I am trying to set qualified_amount=w2_wages times (either 0, .5 or 1).

Blockquote
SELECT e.employee, e.employee_title, e.w2_wages, t.risk, e.role, e.w2_wages*risk as qualified_amount,
CASE risk
WHEN 'low' THEN 1.0
WHEN 'med' THEN 0.5
ELSE 0.0
END
FROM employees as e, titles as t
WHERE e.email='$email' AND e.campaign='$campaign'
AND t.email='$email' AND t.campaign='$campaign'
AND e.employee_title=t.title
ORDER BY e.employee_title

Welcome!

Be aware that this forum is for SQL Server, so many of us don't know MySQL at all.

I agree -- I don't see you could ever get the results shown from the query you posted, if it were running in SQL Server. Either something else must be going, or MySQL labels a column automatically and shows a decimal value as an integer.

Risk is a character value and you have e.w2_wages*risk which doesn't make sense.
You are checking for risk = 'med' whereas it is 'medium'
Do you mean something like
e.w2_wages * CASE risk
WHEN 'low' THEN 1.0
WHEN 'medium' THEN 0.5
ELSE 0.0
END as qualified_amount,
CASE risk
WHEN 'low' THEN 1.0
WHEN 'medium' THEN 0.5
ELSE 0.0
END as RiskVal

Try changing the case statement

CASE
WHEN risk = 'low' THEN 1.0
WHEN risk = 'med' THEN 0.5
ELSE 0.0
END as RiskAssessment

got it working. thx