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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top