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) DESCHow 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 = !
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.