ANSI SQL-92 --> Is it practical??

Hi Everyone.

Most RDBMS's support ANSI SQL92.
For example, Oracle does support ANSI SQL92, and allows you to set a FIPS flagger to prevent you from using any Oracle specific extensions.

Sounds great - but is it really?

For example, I am currently working on a project where this portability is crucial. So, take for example a pretty simple searching requirement - the user enters the customer name [ first name and lastname] in a single name field, and hits the Find" button. Typically, we may want to run a criteria such as :

select * from customer
where upper(firstname || lastname) = replace(Upper('sEarCh Ctieria'), ' ', '');

i.e. I'm removing spaces from the user input, and converting everything to uppercase. Ignore wild cards.
Works fine, and Oracle8i will allow functional indexing so that I can safely get performance even though I am using functions.

But, if you turn on FIPs flagger - this is the error:

ORA-00097: use of Oracle SQL feature not in SQL92 Entry Level PLS-01452: This function is not part of the ANSI standard

So my questions:

In this very simple case, how could I achieve the same results using one or multiple statements that satisfy SQL92?

Would I have to change my design to faciliate this?

Has anyone developed a full commercial system that actually complied with SQL-92?

If so, what environment and what were the issues?

Did it increase the development effort substantially?

Any good articles on the practicalities of doing this? web sites? etc

I look forward to your responses.

Thanks

Coakleyj

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CDT