The CASE statement returns any datatype such as a string, numeric, date, etc. If all conditions are not the same datatype, an ORA error will be returned. If all results are not the same datatype, an ORA error will be returned. You could have omitted it. Here is an example that demonstrates how to use the CASE statement to compare different conditions:.
I want to return a value based on the combinations in two different fields. Answer: Yes, below is an example of a case statement that evaluates two different fields. While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. Don't know if it is optimal though. There is another workaround you can use to update using a join.
This example below assumes you want to de-normalize a table by including a lookup value in this case storing a users name in the table. The update includes a join to find the name and the output is evaluated in a CASE statement that supports the name being found or not found.
The key to making this work is ensuring all the columns coming out of the join have unique names. In the sample code, notice how b.
Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Collectives on Stack Overflow. Learn more. Asked 10 years, 10 months ago. Active 6 years, 3 months ago. Viewed k times. Improve this question. Hector 6 6 bronze badges. That update should work just fine in Oracle too. Do you get an error? What exactly is your problem? Does it update the wrong rows?
Sorry for beeing so inaccurate. The data type is Char. My bad. My problem is exactly what Adomar and Thomas is disussing below. Add a comment. Active Oldest Votes. Improve this answer. Thomas Thomas That would work, but the question seems to suggest that the OP has no control over the in string — Andomar. Andomar - Presumably, the OP has control over the case expression otherwise, how can they possibly fix it?
If that's the case, then it seems logical that they would have the ability to reformat the In clauses before they get put into the Case expression. If budgpost is really a character column as is stated, then we have to assume that a non-numeric value might make its way into one of the In clauses.
Thomas: It's not uncommon for a DBA to have no control over the arguments passed by clients. In this case the client might pass ,, or something. Not sure, the question can be interpreted either way. Andomar - But then how would they fix the Case expression? If they can change the Case expression, then they should be able to correct the values passed to the Case expression even if they cannot change which values are passed to it.
Andomar - Said another way, if I'm only able to correct the procedure which is passed an argument with values like 1,2,3.
0コメント