odbc IsNumeric function

Posted by scottemick on 22-Jul-2014 12:01

I am writing reports against the SQL Engine for 10.1C and have a situation where some money amounts are stored in a varchar field instead of a numeric field and when I try to convert this a numeric, I get an error because there is something non-numeric somewhere. I tried the to_number function and also the odbc {fn convert} and {fn cast} functions, all give me the same error.  I cannot find any built-in isnumeric function in either progress or odbc. I did find an isnumeric.p abl file that I was able to compile and can use inside progress iteself, but I cannot figure out how to call it from odbc.  Any information on how I can check for numerics or do a trycast, or run abl from sql would be greatly appreciated.

Posted by Thomas Mercer-Hursh on 22-Jul-2014 12:21

You can't call ABL from SQL.  For future reference for ABL, you might be interested in www.oehive.org/.../isNumber

One thing you might do is to write a small ABL program that loops though the field and tests for being numeric and report the ones that are not.  That might give you or us a clue of what to do to get around the problem.

All Replies

Posted by Thomas Mercer-Hursh on 22-Jul-2014 12:21

You can't call ABL from SQL.  For future reference for ABL, you might be interested in www.oehive.org/.../isNumber

One thing you might do is to write a small ABL program that loops though the field and tests for being numeric and report the ones that are not.  That might give you or us a clue of what to do to get around the problem.

Posted by scottemick on 22-Jul-2014 12:24

Thanks I might have to do that in ABL, great idea.  (I am going to try that now to identify what needs cleaned up if anything).  The isNumber you reference is the one I found as well.  Thanks for the prompt response.

Posted by Thomas Mercer-Hursh on 22-Jul-2014 12:35

And, of course, using the same program to clean up the dubious entries, if that is indicated, should be simple once you know what is there.

Posted by Roberta Miller on 22-Jul-2014 13:29

As an alternative to the ABL suggestions Dr. Mercer-Hursh provided, you might also consider implementing your own "isnumeric" as a Java stored procedure. For more information, see OpenEdge Data Management: SQL Development, Chapter 11, "Stored Procedures and Triggers".

Posted by scottemick on 23-Jul-2014 07:42

I checked and the only data in the netsales column I am working with is numeric, commas, and periods.  Yet if I use the progress replace function or even the odbc replace function to remove the commas, I still get the Invalid number string(7498) error after some row retrieval has occurred.  

If I do the same select to progress using odbc linked server in mssql the select works because presumably sql server is downloading all the rows to a temp table and then applying the replace and then cast to numeric itself.  This implies something is wrong with progress itself.  Why can't progress do a simple replace?  And yes I checked for nulls...Could there be embedded special characters?

Posted by Youssif Shanshiry on 23-Jul-2014 08:46

Could it be you are running into bug OE00167739?  If yes, you need to use 10.2C02 or later otherwise please open a case with Technical Support.

Posted by scottemick on 23-Jul-2014 09:41

No I am not running into that bug, it fails on a simple select with no grouping, aggregates, or anything really...I am starting to think I might need to run dbtool.  I ran into character width violations and wonder if there could be anything similar with the varchar netsales.  

Posted by Thomas Mercer-Hursh on 23-Jul-2014 09:53

It might help to see the actual SQL code that you are using.

Which said, one of the questions to address here is what are your requirements long term.   Having commas in there is going to be a thorn in your side for any future dealing with this field.  If this is a one time requirement, anything that works might be OK, but if it is on-going, you might want to consider cleaning up the data.  The actual cleanup should be trivial, but then you also need to fix the code that is creating it in that form.  That may not be as simple.

Posted by scottemick on 23-Jul-2014 10:22

Thanks....I think the customer's ultimate goal is to get off progress and go to SQL Server entirely, but for now my company is generating some reports for them in SSRS.  I just got involved as a DBA because the programmer that's been working with them needed extra help to speed things up.  I have at least 3 main choices for that we want to do.  1) Replicate the Data in SQL Server and report against that.  2) Use the progress odbc data source in SSRS 3) Use the linked servers in SQL Server to pull the data from.  

I was trying to go with #2 because that would be the quickest and most up-to-date data.  #1 would require that I fix some of their tables up properly to have primary keys or else determining which rows need inserted, updated, and deleted gets messy. #3 works no problem if I force SQL Server in some cases to download the data to table variables and perform some of work locally like the cast to numeric for example.

Here are some examples.  (For the ODBC Selects I am using SqlDbx, a wonderful product).

SELECT {fn replace(Netsales,',','')}  Netsales
FROM PUB.eDB_MajorGroupTotals T 

This was failing with error 7498.  Amazing after I just ran dbtool option 6 and option 2, this now runs!

this simple select was failing too:

SELECT * FROM PUB.Entity


because of a 7684 error....This now runs too!

So I think I found my problem....I am on day 3 now of working with progress.  I have about 15 years with MSSQL, MySQL, DB2, Access and a little Informix, FilePro, and many other db products...I had just never come across progress until now.  

Posted by scottemick on 23-Jul-2014 10:26

I wasn't paying attention I removed the cast from the SQL above, still getting the 7489 error :(

Posted by scottemick on 23-Jul-2014 10:30

SELECT {fn CAST({fn replace(Netsales,',','')} AS NUMERIC(18,2))} Netsales
FROM PUB.eDB_MajorGroupTotals T 

is not working and

SELECT to_number(Netsales)
FROM PUB.eDB_MajorGroupTotals T 

is not working either I get
Invalid number string (7498) 

SELECT to_number(replace(Netsales,',','')) Netsales
FROM PUB.eDB_MajorGroupTotals T 


doesn't work either...

This craziness works in sql server against the linked server:

create procedure drs.WeekToDateComparableSalesSummary
 @datestart datetime
,@dateend datetime
AS
declare @MajorGroupTotals table(EntityId int, Netsales money);
insert into @MajorGroupTotals
select T.EntityId, Cast(replace(Netsales,',','') as numeric(18,2)) Netsales from CHESTNUTLAND.CORPDB2.PUB.eDB_MajorGroupTotals T 
where  T.dt_StartBusDate BETWEEN @datestart AND @dateend;
declare @Entity table(EntityId int,RegionId int,Name varchar(100));
insert into @Entity
select E.EntityId, E.RegionId, E.Name FROM CHESTNUTLAND.CORPDB2.PUB.Entity E;
declare @Region table(RegionId int, Name varchar(100));
insert into @Region 
select R.RegionId, R.Name from  CHESTNUTLAND.CORPDB2.PUB.Region R;


SELECT 
  R.RegionId
, R.Name Region
, E.EntityId
, E.Name Location
, sum(T.Netsales) Netsales
FROM @Entity E
INNER JOIN @MajorGroupTotals T ON E.EntityID = T.EntityID
INNER JOIN @Region R ON E.RegionID = R.RegionID
GROUP BY   R.RegionId , R.Name  , E.EntityId , E.Name 
ORDER BY R.RegionID, E.EntityID

exec drs.WeekToDateComparableSalesSummary '07-20-14','07-26-14'




Posted by scottemick on 23-Jul-2014 10:35

here a simplified version from sql server using linked server works:

select cast(replace(T.Netsales,',','') as numeric(18,2)) from CHESTNUTLAND.CORPDB2.PUB.eDB_MajorGroupTotals T 


Posted by scottemick on 23-Jul-2014 11:26

FUNCTION fn-ISDECIMAL RETURN LOGICAL (INPUT ip_textin AS CHAR):
 DEFINE VAR va-value AS DECIMAL NO-UNDO.
 ip_textin=trim(ip_textin).
 IF ip_textin ="" OR ip_textin = ? THEN RETURN NO.
 ASSIGN va-value = DECIMAL(ip_textin) no-error.
 RETURN NOT ERROR-STATUS:ERROR.
 /*IF error-status:error THEN return NO.*/
 RETURN YES.
END FUNCTION.

FOR EACH eDB_MajorGroupTotals: 
    DEFINE VAR va-results AS LOGICAL.
    DEFIN VAR va-netsales AS DECIMAL.
    ASSIGN va-results = fn-ISDECIMAL(Netsales).
    ASSIGN va-netsales = DECIMAL(Netsales).
    DISPLAY Netsales va-results va-netsales.

END.


The able system can read them, classify them just fine in ABLE. 

Posted by steve pittman on 23-Jul-2014 23:49


There is a bug in the OE sql replace() function, recently reported by one of our support people, when the replacement string is the empty string (0 bytes string). In this bug, if the search string matches, no replacement occurs.

Apparently, this bug has existed at least back to 102b.
We cannot currently state when a fix for this problem will be delivered. It is considered a significant bug.

This bug would seem to account for some of the difficulties you are seeing in your trying to resolve your problem.

hope this helps,        ....steve pittman  [OE sql software development architect]



[collapse]On 7/23/2014 11:30 AM, scottemick wrote:
Reply by scottemick
SELECT {fn CAST({fn replace(Netsales,',','')} AS NUMERIC(18,2))} Netsales
FROM PUB.eDB_MajorGroupTotals T 

is not working and

SELECT to_number(Netsales)
FROM PUB.eDB_MajorGroupTotals T 

is not working either I get
Invalid number string (7498) 

SELECT to_number(replace(Netsales,',','')) Netsales
FROM PUB.eDB_MajorGroupTotals T 


doesn't work either...

This craziness works in sql server against the linked server:

create procedure drs.WeekToDateComparableSalesSummary
 @datestart datetime
,@dateend datetime
AS
declare @MajorGroupTotals table(EntityId int, Netsales money);
insert into @MajorGroupTotals
select T.EntityId, Cast(replace(Netsales,',','') as numeric(18,2)) Netsales from CHESTNUTLAND.CORPDB2.PUB.eDB_MajorGroupTotals T 
where  T.dt_StartBusDate BETWEEN @datestart AND @dateend;
declare @Entity table(EntityId int,RegionId int,Name varchar(100));
insert into @Entity
select E.EntityId, E.RegionId, E.Name FROM CHESTNUTLAND.CORPDB2.PUB.Entity E;
declare @Region table(RegionId int, Name varchar(100));
insert into @Region 
select R.RegionId, R.Name from  CHESTNUTLAND.CORPDB2.PUB.Region R;


SELECT 
  R.RegionId
, R.Name Region
, E.EntityId
, E.Name Location
, sum(T.Netsales) Netsales
FROM @Entity E
INNER JOIN @MajorGroupTotals T ON E.EntityID = T.EntityID
INNER JOIN @Region R ON E.RegionID = R.RegionID
GROUP BY   R.RegionId , R.Name  , E.EntityId , E.Name 
ORDER BY R.RegionID, E.EntityID

exec drs.WeekToDateComparableSalesSummary '07-20-14','07-26-14'




Stop receiving emails on this subject.

Flag this post as spam/abuse.


[/collapse]

Posted by scottemick on 24-Jul-2014 09:44

Thanks very much Steve, that explains what I'm seeing.  I have since made a query that  returns the base data but does not aggregate it.  I am allowing SSRS to convert and aggregate and that has gotten me past that problem.  Now I have another problem, but that's for another thread. 

Scott

This thread is closed