Q

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.


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close