Ask the Expert

How to change return values using condition statement

Is there any way I can change return values from, for instance, 0.01 to 0? Here is my sample SQL code.

/*-------------------------------begin--------------------------------------
----------------*/
SELECT	A.COMPANY,
	A.BUDGET_COMP_NO,
	COUNT(C.EMP_NO) AS STAFF_COUNT,
	E.POS_ACCT_PERCENT AS SPLIT_PERCENT,
	(A.HRS_WEEK_FTE/COUNT(A.POSITION_NUMBER))*(E.POS_ACCT_PERCENT) AS BUDG_HRS

FROM	IDTPOS A,IRTPOS C,IDTPAD E

WHERE		C.COMPANY  = 	A.COMPANY	
	AND	E.COMPANY  =	A.COMPANY	
	AND	A.POSITION_NUMBER  = C.POSITION_CODE		
	AND	A.POSITION_NUMBER = E.POSITION_NUMBER
	AND	LEFT(TRIM(E.POSITION_NUMBER),4) =	A.BUDGET_COMP_NO
	AND	RIGHT(TRIM(E.POS_ACCT_NUMBER),4) =  A.BUDGET_COMP_NO
	AND	C.POSITION_STATUS ='A'
	AND	A.COMPANY ='100'		
	AND	LEFT(E.POS_ACCT_NUMBER,3)='100'
	
GROUP BY 	A.COMPANY,A.BUDGET_COMP_NO,E.POS_ACCT_PERCENT
ORDER BY 1, 2,3
/*-------------------------------end----------------------------------------
--------------*/
It produces the following sample result.
company	budget_comp_no      staff_count	split_percent	budg_hrs
100	6151		1		0.5		37.5
100	6680		1		0.01		0.1
100	6755		1		0.01		0.75
I want to say in the query that if split_percent = 0.01, then split_percent = 0, end if. However, I don't know how to incorporate this idea in the query.


    Requires Free Membership to View

Here is another situation where CASE comes quickly to the rescue. I'll illustrate with a simple example. Suppose we have a table which contains a column that holds values like those you've described:

create table myTable
( ID numeric(5) primary key,
  Value numeric(3,2) not null
);

insert into myTable values (1,0.60);
insert into myTable values (2,1.00);
insert into myTable values (3,0.04);
insert into myTable values (4,0.70);
insert into myTable values (5,0.01);
If we want any occurance of 0.01 to show up as zero, then we can simply apply a CASE function to the value (I've thrown in the parentheses for clarity; they're not required):
select ID, 
       ( case when Value <= .01 
              then 0 
               else Value 
         end
       ) as Value 
  from myTable;
I noticed the value in your SQL that you wanted to manipulate was more complicated: it was the product of some mathematical and aggregate operations. This doesn't affect the usefulness of the CASE statement, as substituting your expression for my VALUE column should work as expected.

For More Information


This was first published in June 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: