Miracle173, thank you for clarifying, I am using ORACLE SQL, For this particular script, I am not using SQL PLUS, just firing off a script (f5 in TOAD) and. You have tagged your query with plsql but I dont see any pl/sql. something like this should do it: select l.location_nameįrom locations l join departments d on d.location_id = l. I am trying to use DECODE in PL/SQL statement (for sample HR schema) but I got this error: The number specified in exact fetch is less than the rows returned. DECODE or CASE STATEMENT for PARAMETER in WHERE CLAUSE closed. if it was really d.department_id, then why are you joining locations and departments in the first place? Omit locations table if you don't need it.įinally, why decode? If you joined these two tables, then you have everything you need, i.e.If it's a positive number p is greater than 100. So try to subtract 100 from p and check whether it's a positive number or not. But if you can transform it into an arithmetic operation resulting into an integer value you got what you want.
because substitution variable suggests so ( &EnterLocID) - location ID, not department ID 1 If I am not wrong you can't use boolean like that.Therefore, this is what you might need to do (I don't have your tables so I can't test it, but I hope you'll get the idea): FROM locations l join departments d on d.location_id = l.location_idĪlso, as you can see, I modified the WHERE clause and used l.location_id instead of your d.department_id. However, there's a possibility that query still returns 2 or more rows which can't fit into a scalar V_DEP variable. The Decode function compares one expression to one or more other expressions and, when the search term is found, returns the match result. When i tried this way iam getting 'ORA-06553: PLS-382: expression is of wrong type'. The way I see it, you forgot to join LOCATIONS and DEPARTMENTS tables so they are cross-joined, and then you applied a filter to D.DEPARTMENT_ID. I have a scenario where i need to use a boolean function inside DECODE statement. The DECODE function can be used in Oracle/PLSQL.
0 kommentar(er)
