Q
Problem solve Get help with specific problems with your technologies, process and projects.

Assigning output with special values

I'm doing a select statement using Oracle 8.1.5 and need to assign the output based on the selection criteria in a seperate column with a special value, for example an exclamation mark or asterisk.

If the week_total_hours is greater than 45, then I need to assign an asterisk in a new column called *FLAG* (that does not exist in the table). If the week_total_hours is greater that 65, then I need to assign an exclamation mark in that new FLAG column. In addition, I need to list those records that are less than 45 with nothing in that new column. Is there any other way to do this besides the script below?

SELECT decode(SIGN(SUM(timesheetrate.week_total_hours) - 60),1, '!', '*') flag, 
SUM(timesheetrate.week_total_hours) sickhours, 
FROM TIMESHEETRATE
WHERE timesheetrate.........
GROUP BY timesheetrate....
HAVING SUM(timesheetrate.week_total_hours)> 45 
ORDER BY SUM(timesheetrate.week_total_hours) DESC
How can I embed another DECODE to include those hours that are less than 45?


If you were at 8.1.6 you could use a CASE expression, but since you're not...

First, let me address your current query and the requirements you list. The requirements you listed are:

  • If week_total_hours <= 45, then flag = null
  • If week_total_hours > 45 and <= 65, then flag = *
  • If week_total_hours > 65, then flag = !
Now, the current query: Does it work as is? You said 65 was your requirement, but use 60 in your current select statement to derive a value. It may have just been a type-o when you typed in your question, but I'm going to use 65 for now.

The easiest thing, I think, would be to create a function that does the check and simply call the function in your query. For example:

CREATE OR REPLACE FUNCTION get_flag_value (p_wk_tot_hrs IN NUMBER)
RETURN char
AS
   v_sFlag   char(1) ;
BEGIN
   IF p_wk_tot_hrs <= 45 THEN
      v_sFlag := null ;
   ELSIF p_wk_tot_hrs > 45 AND p_wk_tot_hrs <= 65 THEN
      v_sFlag := '*' ;
   ELSIF p_wk_tot_hrs > 65 THEN
      v_sFlag := '!' ;
   END IF ;
   
   RETURN v_sFlag ;
END;
/
Then your query would be:
SELECT get_flag_value(sum(timesheetrate.week_total_hours) flag, 
       sum(timesheetrate.week_total_hours) sickhours
  FROM timesheetrate
 WHERE timesheetrate. . . . . .
 GROUP BY timesheetrate . . . . 
 ORDER BY sum(timesheetrate.week_total_hours) DESC ;
If for some reason, you just don't want to write a function to do the job, you can get what you want by adding a second query and UNIONing it to the first as follows:
SELECT decode(SIGN(SUM(timesheetrate.week_total_hours) - 65),1, '!', '*') flag, 
       SUM(timesheetrate.week_total_hours) sickhours
  FROM timesheetrate
 WHERE timesheetrate.........
 GROUP BY timesheetrate....
HAVING SUM(timesheetrate.week_total_hours)> 45 
UNION
SELECT ' ' flag,
       SUM(timesheetrate.week_total_hours) sickhours
  FROM timesheetrate
 WHERE timesheetrate. . . . . .
 GROUP BY timesheetrate . . . . 
HAVING SUM(timesheetrate.week_total_hours) <= 45 
 ORDER BY sum(timesheetrate.week_total_hours) DESC ;
Personally, I think the function would be the easiest/cleanest way to go.

For More Information


Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close