Problem while performing update and select

[Former Member]

Hi

I am using SAP DB for database.

I had rows in my table "table_name" from time 23/12/2009 12.52.07.64 (min time) to 23/12/2009 12.52.07.81 (max time) for column TIMELIMIT.

I have done following thing.

Please see the following query.

UPDATE table_name SET PROCESSED='L' WHERE NUMBER='1234567890' AND TIMELIMIT >= to_date('2009122312520764 PM','yyyymmddhhmissms AM') AND TIMELIMIT <= to_date('2009122312520781 PM','yyyymmddhhmissms AM') AND PROCESSED='U'

This query failed to update.

So I changed this query in the form of select query to check whether it is able select for given WHERE clause.

*SELECT * FROM table_name WHERE NUMBER='1234567890' AND TIMELIMIT >= to_date('2009122312520764 PM','yyyymmddhhmissms AM') AND TIMELIMIT <= to_date('2009122312520781 PM','yyyymmddhhmissms AM') AND PROCESSED='U'*

This query also failed to select any records.

Then I just tried to execute following query.

*SELECT * FROM table_name WHERE NUMBER='1234567890' AND TIMELIMIT >= to_date('2009122312520764 PM','yyyymmddhhmissms AM') AND PROCESSED='U'*

and it worked.

This means TIMELIMIT <= to_date('2009122312520781 PM','yyyymmddhhmissms AM') was causing the problem.

Please tell me what was the reason for queries failure ?

Is it possible that SAP DB will not be able to process certain time values.

Thanks,

Mayuresh Joshi

[lbreddemann]

> I am using SAP DB for database.

I hope that you're just mistaken here and that in fact you do use MaxDB meanwhile.

By the way; theres a specific forum for MaxDB related questions:,

> I had rows in my table "table_name" from time 23/12/2009 12.52.07.64 (min time) to 23/12/2009 12.52.07.81 (max time) for column TIMELIMIT.

> I have done following thing.

> Please see the following query.

>

> UPDATE table_name SET PROCESSED='L' WHERE NUMBER='1234567890' AND TIMELIMIT >= to_date('2009122312520764 PM','yyyymmddhhmissms AM') AND TIMELIMIT <= to_date('2009122312520781 PM','yyyymmddhhmissms AM') AND PROCESSED='U'

> This query failed to update.

> This query also failed to select any records.

> Then I just tried to execute following query.

> *SELECT * FROM table_name WHERE NUMBER='1234567890' AND TIMELIMIT >= to_date('2009122312520764 PM','yyyymmddhhmissms AM') AND PROCESSED='U'*

> and it worked.

> This means TIMELIMIT <= to_date('2009122312520781 PM','yyyymmddhhmissms AM') was causing the problem.

> Please tell me what was the reason for queries failure ?

Ok, you didn't show us the data that is in the table so I assume that there should be matching entries in there.

So the most likely cause for this phenomenon is that you don't really understand how to_date works in MaxDB.

See, to_date is a Oracle-specific function. It's not standard SQL.

The fact that MaxDB accepts the syntax doesn't mean that it works the same way.

Please check the documentation on this, e.g. [here|http://maxdb.sap.com/doc/7_7/1c/44a74607704a5bb9fa1d6380efd525/content.htm] or [here|http://maxdb.sap.com/doc/7_7/1c/44a74607704a5bb9fa1d6380efd525/content.htm],

> Is it possible that SAP DB will not be able to process certain time values.

Nope - once you do it right MaxDB really can handle all dates you put into it...

regards,

Lars

p.s.

you are aware of that there is a thing called "BETWEEN" statement, are you?

[Former Member]

Thank you for your reply Lars.

But previously this query was working fine.

This is the first time I have seen such a absurd behavior from SAP DB (And I am using SAP DB only and not MAX DB as it is an years old version) that's why I have raised this point here.

I'll be thankful if you provide me some more directions in this matter so that I can dig further.

Regards,

Mayuresh

[lbreddemann]

> I'll be thankful if you provide me some more directions in this matter so that I can dig further.

Ok, in that case help us to help you...

We need:

1. the exact version of SAP DB you're using

2. a reproducible demo case of the behavior.

This should include all required create table, create index, insert- and select statements to see the effect.

regards,

Lars