views:

307

answers:

3

We have recently had to do some work with an OpenEdge database that a third party product uses, and today (after much hair-pulling), we finally identified why a view was returning no results. This view in question combines about 100 separate tables, and is then queried against (we have limited rights to this database). One of the fields returned by this view is a hard-coded string literal, along the lines of

'John Smith' AS TheName

We were having difficulty running queries that included this string, which we were trying to RTrim (the view returned a lot of trailing spaces) and then concatenate with another field. However, if we used RTrim on this field then, instead of returning an error message, or a null or something like that, the row simply wasn't returned. We weren't trying to use it in a WHERE clause or JOIN, this was simply part of the SELECT ... FROM VIEWNAME. After reviewing the view, it seemed that the view had erroneously detected the length of the string as 9 characters (no length was specified in the definition), and RTrim just didn't work. Now, I could understand why this might lead to an error message, or a NULL value in the SELECT, but why would the row simply not be returned at all? This doesn't seem like good SQL behaviour and I've never seen it happen with any other RDBMS.

Other info : we're test querying via ODBC and WinSQL, with a view to this being included in an existing ASP.NET app. We don't have access to the backend except via this, although we do have rights to create views.

Update : As a freaky follow-up, we have now discovered that if we attempt to query this view without any WHERE clause, no records are returned. This may have the same cause.

A: 

That sounds like very strange behavior. Just code around it, do the trim and/or string manipulation in the application and go on your way.

KM
+1  A: 

Make sure you don't have blanks. Trimming doesn't remove blanks only spaces. Blanks are also not nulls. There is a difference in the character set while it is not visibly different in your editor. I have run into this with a few databases, DBII, Oracle, PostGreSQL. Check the character set of your editor and try viewing the tables, you might see nothing or you might see big rectangles.

Stradas
+1  A: 

This sounds like it could be related to the SQL-WIDTH within the progress database. One problem with Progress is that if the content of the field exceeds the SQL-WIDTH then you will get strange SQL behaviour (sometimes the driver might fail, other times you get no results).

To identify this you need to use the dbtool command to check for SQL-WIDTH's that may be exceeded.

Brett Ryan
After reading a little, it sounds a bizarre yet plausible cause for this behaviour. Sadly I've no way of testing it for certain.
CodeByMoonlight
Do you not have access to the progress commands or a progress DBA to verify the widths for you? Progress databases are very bizarre by nature, they do not behave like other traditional SQL databases.
Brett Ryan