How to use SQL select result in a 4GL procedure

[2m.lt]

The reason of using it is the following:

I have a table in an Oracle DB. This table has indexed customer_id CHAR(20) field. The problem is writing queries using these CHAR fields.

For example the following three statements do not return anything:

FOR EACH phone WHERE customer_id = "1" NO-LOCK: DISP phone.customer_id.
END.

FOR EACH phone WHERE customer_id = "1 " NO-LOCK:
DISP phone.customer_id.
END.

SELECT customer_id FROM phone WHERE customer_id = "1".

It seems progress trims strings in the WHERE condition, so no results are returned. However this statement returns two rows, which is the correct behavior:

SELECT customer_id FROM phone WHERE customer_id = "1 ".

This is why I wanted to put SQL SELECT statement results to temp-table.

Well, for now I think I will use stored procedure to get the same effect:

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO bufHandle (SUBST("
SELECT customer_id FROM phone
WHERE customer_id = '&1'
", customerId)).


Notice that I do not need to use padding when executing queries straight in the Oracle DB server, which is of course how you would normally write SQL queries which use CHAR field.

 

[tamhas]

I believe that this behavior is a known issue with the Oracle DataServer. The correct solution is not to cobble together some dubious SQL to get around the problem, but to ensure the cleanliness of your data in the first place. It is a trivial exercise to write little data cleaning routines to deal with existing data, but, of course, a bigger job to scrub the application to make sure it doesn't happen again ... nevertheless, it is the right thing to do since you will only have lasting grief from leaving the data in this state.

 

[RealHeavyDude]

That I can only agree with. Trying to solve questionable data quality in your data retrieval and CRUD logic will only introduce more pain. The best approach is to solve the underlying data quality issue in the first place and ensure that the quality won't drop again. Even for non-Progress databases it is bad practice to clutter indexed columns with trailing blanks and all other sorts of funky characters.

Heavy Regards, RealHeavyDude.