Solved

QUERY CASE WHEN PROBLEM

Posted on 2013-02-02
2
361 Views
Last Modified: 2013-02-02
Hi All,

I have query below, and I get compile error.

 SELECT
 A.*
, CASE CAST(A.MasaKerja AS MONEY)
      WHEN < 2 THEN 10000
      WHEN < 3 THEN 20000
      WHEN < 4 THEN 30000
      WHEN < 5 THEN 40000
      WHEN > 5 THEN 50000
ELSE 0
 END AS NilaiTunjangan
FROM
(
SELECT
EmpCode
, CASE WHEN RESIGNDATE IS NULL
THEN DATEDIFF(DAY, CONVERT(CHAR(8), EmployDate, 112), '20130131') / 365.0
ELSE DATEDIFF(DAY, CONVERT(CHAR(8), EmployDate, 112), CONVERT(CHAR(8), ResignDate, 112)) / 365.0
END AS MasaKerja
FROM THI_PAYROLL.dbo.TMEMPLOYEE
WHERE  (RESIGNDATE IS NULL OR
CONVERT(CHAR(8), ResignDate, 112) > '20130101')
) A
WHERE MasaKerja >= 1

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'A'.

What's wrong with the code ?

Thank you.
0
Comment
Question by:emi_sastra
2 Comments
 
LVL 16

Accepted Solution

by:
kmslogic earned 500 total points
ID: 38847761
If you aren't testing for a specific value you'll have to put the condition on each when line like

CASE 
      WHEN CAST(A.MasaKerja AS MONEY) < 2 THEN 10000
      WHEN CAST(A.MasaKerja AS MONEY) < 3 THEN 20000
      WHEN CAST(A.MasaKerja AS MONEY)< 4 THEN 30000
      WHEN CAST(A.MasaKerja AS MONEY)< 5 THEN 40000
      WHEN CAST(A.MasaKerja AS MONEY)> 5 THEN 50000
ELSE 0
 END AS NilaiTunjangan 

Open in new window

0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 38847782
Got it.

Thank you very much for your help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now