Exception when executing UPDATE clause on IBM DB2

Asked

Viewed 35 times

0

Hello, I am doing an UPDATE on an IBM DB2 database, and is returning this message:

DB2 query node, error in query: Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  Não foi localizada uma linha para FETCH, UPDATE ou DELETE, ou o resultado de uma consulta é uma tabela vazia.  SQLSTATE=02000

I looked at the exception documentation at the IBM Knowledge Center, but I couldn’t find a solution. I also checked the possible causes that may be causing this Warning, but I didn’t get anywhere. I’m missing something...

The UPDATE clause I have is as follows:

UPDATE BLUADMIN."AlarmEvent" SET "NormTime" = CASE 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "TagName" = '0' AND "Array" = 0 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '0' AND "Array" = 1 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '0' AND "Array" = 2 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '0' AND "Array" = 3 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "TagName" = '0' AND "Array" = 4 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '0' AND "Array" = 5 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '16' AND "Array" = 6 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "TagName" = '0' AND "Array" = 7 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "EventValue" != '0' AND "Array" = 8 THEN 20200327065645 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "ActiveTime" != 20200327065645 AND "TagName" = '0' AND "Array" = 9 THEN 20200327065645 
END WHERE "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 8 AND "Array" IN (0,1,2,3,4,5,6,7,8,9) AND "TagName" IN ('0')

The table I am trying to update has data. I wondered if there is something wrong in the clause that may be causing the Warning mentioned above.

In this other clause, I succeed:

UPDATE BLUADMIN."AlarmEvent" SET "NormTime" = CASE 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = '0' AND "Array" = 0 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 1 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 2 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 3 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = '0' AND "Array" = 4 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 5 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 6 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = '0' AND "Array" = 7 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "EventValue" != '0' AND "Array" = 8 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = 'Inverter[15].MPPT2_Event2.b10' AND "Array" = 9 THEN 20200327065756 
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = 'Inverter[15].MPPT2_Event2.b12' AND "Array" = 9 THEN 20200327065756    
    WHEN "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "ActiveTime" != 20200327065756 AND "TagName" = 'Inverter[15].MPPT2_Event2.b13' AND "Array" = 9 THEN 20200327065756 
END WHERE "InstallationUUID" = '0D013C000000-15-000200' AND "DeviceType" = 'Inverter' AND "Index" = 15 AND "Array" IN (0,1,2,3,4,5,6,7,8,9) AND "TagName" IN ('0','Inverter[15].MPPT2_Event2.b10','Inverter[15].MPPT2_Event2.b12','Inverter[15].MPPT2_Event2.b13')

Recalling that the table has data for both Index(8 and 15).

If you have suggestions to improve the clause, I am open to criticism. I thank you!

sql sql-update db2 ibm
  • 1

    It seems to me it’s just a warning that no row of your table meets the specified criteria.

  • In fact it is only a Warning, but the update is not updating what should update.

  • I’m going to change some things on the app to do it in a better way.

  • But if no line meets the specified criteria it will update what? Perhaps the criteria have not been coded correctly.

  • In 1 of the WHEN the clause has, the conditions are true, so you should update some data from the table. I did not put the question, but are valid updates that update something, in case the 1° UPDATE is not updating, and the 2° of success.

  • 1

    It is not the WHEN clause that you should check but the WHERE clause.

  • And how would I do that? Is there an example?

Show 2 more comments
No answers

Browser other questions tagged sql sql-update db2 ibm

You are not signed in. Login or sign up in order to post.