Help Constructing an Oracle SQL with Condition

Question

Hi. I need some help in creating an Oracle SQL which I will execute in .NET.

I need to update a column in a table but the value to update the same would be dependent on two different values. To give an example:

Dim sqlcmd as String Dim collCmd as Collection For x = 1 to intCount

sqlcmd = "Update tableA Set col1 = :val1, col2 = :val2 Where...."

collcmd.add(sqlcmd)


SELECT col1, col2
FROM tableA 
Where .....

If col1 = 0 and col2 = 0 then
 sqlcmd = "Update tableB 
 Set col1 = :value
 Where...."
Else
 sqlcmd = "Update tableB
  Set col1 = :value
  Where.."
End If

collcmd.add(sqlcmd)
Next

'Perform the update with transaction here for the collcmd collection.

Apparently, I need to place the update in one sql where the condition is met. Kindly advise. I cannot do a one time execute non query here since if one of the update fails, then I would need to perform a transaction rollback. I am placing all the update statement in one collection and performing the update in one transaction. But the value for the tableA may be different on the next iteration.

Kindly take note that I cannot place the same inside a stored proc since there are other sql commands which are executed prior to the statements above.

Is there a way to create an SQL where the update would go something like:

sqlcmd = "UPDATE tableB b
          IF select a.col1 = 0 and select a.col2 = 0 from tableA a
          SET b.col1 = "this value"
          ELSE
          SET b.col1 = "other value"
          WHERE...."

Thanks.

Answer 1

Why not write whatever logic you need in a stored procedure/package and call that from .NET?

Answer 2

You need to use the CASE expression, like so:

  UPDATE tableB
     SET col1 = 
         CASE WHEN (0, 0) = (SELECT col1, col2 
                               FROM tableA 
                              WHERE <tableA constraints>
                            )
              THEN "this value"
              ELSE "that value"
         END
   WHERE <tableB constraints>;

Note also the case is using the (a,b,c) = (select A, B, C ...) syntax, which is a handy, but underused sql feature = you can compare not only single values (scalars) but multiple values (vectors).

Answer 3

The best thing to do this is with stored procedures. If you have other sql commands or what ever, then use transactions: run as stored procedures as you want intercalated with data that does not come from database, the commit (or rollback).

If something changes the stored procedures becomes invalid on text-sql does not happens, you have to wait to unit-tests o runtime error. We use generated code that makes very easy to call stored procedures (class.method).

DISCLAIMER: I'm not an stored procedure fanatic. I use Stored Procedures and ORM as best fits on each moment. It depends on the case.