Use of exists when I cannot update to null

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Anasi
    New Member
    • Oct 2007
    • 2
    #1

    Use of exists when I cannot update to null

    Hi,
    I have a problem with a rule that is updating a oracle 10 database. The rule is expected to check if ther is a invoice number then take the highest of them, and if there isnt any, then choose the reference number instead. The problem is, sometimes the table shipordart doesnt contains any rows and then "nothing" should happen. But now i get a error message.

    ORA-01407: cannot update ("E768"."SHIPOR D"."INVOICEN O") to NULL

    I have tried to use EXISTS but i cant get the syntax right.

    Code:
    update shipord set invoiceno = (select CASE WHEN MAX(invoicenoart) > ' ' THEN MAX(invoicenoart) ELSE (SELECT MAX(REFNO) FROM SHIPMAIN WHERE TIMEIN = #TimeIn#)  END
from shipordart where  timein = #TimeIn#
GROUP BY 1)  where  timein = #TimeIn#
    Any help is very appreciated.

    Best regards
    Anasi
    Tags: None
    • debasisdas
      Recognized Expert Expert
      • Dec 2006
      • 8119
      #2
      This problem is because your query is updating the filed to null which has not null constraint defined on it.

        Comment

        • Anasi
          New Member
          • Oct 2007
          • 2
          #3
          Yes, but how should i write if I dont want it to update at all if there isnt any rows with that timein in the table shipordart. I have tried with Exists but I cant get the syntax right. Maybe someone could help me with that or If someone have another solution to the problem i would be happy.

          Best regards
          Anasi

            Comment

            Previous template Next
            Working...