ORACLE DECODE FUNCTION...NEED HELP

[Stargazer77]

CREATE VIEW DECODE<br>AS SELECT STUDENT#,<br> DECODE ( GRADE,<br> 'A', '4.0'<br> 'A-', 3.67<br> 'B+', 3.33<br> 'B', 3.0<br> 'B-', 2.67<br> 'C+', 2.33<br> 'C', 2.0<br> 'C-', 1.67<br> 'D', 1.33<br> 'E', 1.0<br> 'F', 0 )<br> FROM STUDENT_SCHEDULE;<br> <br><br>This isn't working......it says it is missing a right parenthesis before the 'A'......I put one but it says it needs another one right after the 'A'...

 

[yaffle]

Try:<br><FONT FACE=monospace>CREATE VIEW GRADE_DECODE AS<br>SELECT STUDENT,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DECODE ( GRADE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A', 4.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A-', 3.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B+', 3.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B', 3.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B-', 2.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C+', 2.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C', 2.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C-', 1.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'D', 1.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'E', 1.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'F', 0 )<br>FROM STUDENT_SCHEDULE;</font><br>You need commas after each parameter to decode, and all your decoded results should be of the same type (i.e. all numeric, or all varchar2).<br>Calling the view DECODE might cause problems too.

 

[bhallar]

Calling the DECODE could not be any problem. But its a good practice not to use the reseverved word for objects.<br>Your query also create a problem as you haven't define a alias for the expression. <br>It shoould be<br>CREATE VIEW GRADE_DECODE AS<br>SELECT STUDENT,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DECODE ( GRADE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A', 4.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A-', 3.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B+', 3.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B', 3.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B-', 2.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C+', 2.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C', 2.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C-', 1.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'D', 1.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'E', 1.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'F', 0 ) myExpr<br>FROM STUDENT_SCHEDULE;<br>

 

[juls]

I'm not sure if it's required, but I always add a final default value in case none of the DECODE values match:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DECODE ( GRADE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A', 4.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'A-', 3.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B+', 3.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B', 3.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'B-', 2.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C+', 2.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C', 2.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'C-', 1.67,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'D', 1.33,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'E', 1.0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'F', 0,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 ) myExpr<br>

 

[Guest7112023]

The default value is optional - the DECODE() returns NULL if no values<br>matched.

 

[rdenison]

There are several problems with your code:-<br><br>You need commas between all arguments, so you need them at each line end in the main body of you statement.<br><br>The third argument [&nbsp;&nbsp;'4.0'&nbsp;&nbsp;] needs to be just 4&nbsp;&nbsp;&nbsp;[ decode(grade,'A',4,'A-',3.67..... ) ]<br>so that the second of each pair is of the same datatype [here you are converting<br>a series of char values to numbers].&nbsp;&nbsp;You could add a final default value to pick up stray grades, but this must also be of the NUMBER datatype to match the other returned values.<br><br>The column needs an alias for the [ create view ] to work.<br>Using DECODE as the view name is OK, but I would avoid key words as object names.<br><br>I hope this helps.