Hi

I'm new at this progress stuff so I'm hoping someone can help me.

In MS SQL server we have a functions called IsNumeric and IsDate.
These allow you to evaluate data before processing it.

We have a DB where everything is stored as VarChar fields (I'm told
this is for good reason)

What I'm trying to do is convert these VarChars to their correct data
types when I select them. In SQL Server I'd do this:

Select Case When IsNumeric(myVarCharField)= 1 then Cast(myVarCharField
as Decimal(11,3) ) else Cast(Null as Decimal(11,3) ) end As
MyDcecimalField From MyTable.

We're using Progress 9, can anyone tell me how to convert my MS SQL so
that it works with this version please.

Thanks

Andy

In article <1139231409.515693.310900 (AT) z14g2000cwz (DOT) googlegroups.com>,
"AndyW" <AndrewMWalker (AT) totalise (DOT) co.uk> wrote:

They lied. You're stuck working with screwy data so prepare to deal
with screwy ways to work with it.

You got to table scan it and then decide if you want it or not.

FOR EACH MyTable NO-LOCK:
IF YourFunction(MyTable.A) THEN DO: ...
END. /* FOR EACH MyTable */

I don't think so dude. There are resources to change it into MySQL and
PostgreSQL databases though.

You should put down what version of Progress you are using also as well
as operating system.

--

Thanks Scott,

I may have found an alternative way of doing this, how does this look:

Select case when MyVarCharField is not null and MyVarCharField <> ''
then Cast(MyVarCharField as Decimal(11,3)) else cast(null as
decimal(11,3)) end as MyDecimalField From MyFunkyTable

I'm trying to do this blind as I don't have full time access to the
Progess server (the joys of this strange contract). This works with
the data I have in a MS SQL server DB, I'm assured that the data will
"always" be Null as Zero Lenght String or a number in this column.

Thanks for your help on this...

Andy



Scott Auge wrote:

Were you assured of this by the same people who said storing date fields in
character fields is a good thing?

The best way to access a Progress database is to use the Progress 4GL.

"AndyW" <AndrewMWalker (AT) totalise (DOT) co.uk> wrote

Thanks Steve,

Yep, that's the same people.

To be honest the cut of the DB I pulled into SQL Server would seem to
indicate that the data matches their rules. The data is all VarChar
because they have developed a system that allows users to define their
own screens, the "data types" are applied through the application
logic.

I was about to say Sadly I can't use 4GL, but to be honest I've got too
much stuff to learn without having to get to grips with that.

I'll keep plugging away with my plan until I can get onto a progress
box and test my code.

Cheers

Andy