SQLbase data not numeric

Question

I have a query that runs in Excel, that will need to show data from an SQLbase database where I only have read-access. I cannot modify the database in any way. The column I need to be able to filter on is a varchar column, but it's values are only numbers. I need to be able to filter with between in Excel, so I've tried to do column + 0 so Excel picks it up as a number. This worked before, but now it fails on some numbers.

The number 704977990024 works, but 991098000265 doesn't. With the second one I get Data is not numeric.

This is the query:

SELECT (TABLE1.COLUMN + 0) AS "Artikelnummer"
FROM DB.TABLE TABLE1 
LEFT JOIN DB.OTHERTABLE TABLE2
ON TABLE1.COLUMN = TABLE2.COLUMN

How can I make sure I get it as a number in Excel? If not in the query, then how in Excel itself? When I set the column to number in excel, it still displays as text and will not be numeric unless I try to edit the cell.

ErikE · Answer 1 · 2012-12-21T09:19:47.880

0

It's probably overflowing. An alternate way to get what you need Is to pad the numbers with leading zeroes. This will avoid the overflow and let you use a range. I don't know the syntax... something like where Right('000000000000000' + nu, 17) between ....

edited Dec 21 '12 at 09:19

answered Dec 21 '12 at 09:14

ErikE

48,881
23
151
196

I have found a PDF with the functions from SQLbase [here](http://enos.itcollege.ee/~priit/(I216)%20Ab-de%20administreerimine/SQLBase%20admin%20juhendid/LANG.PDF). Unfortunately, there is no function to pad it. Also, when I try to do just `'0000000000' + number` it still says `Data is not numeric`. – Deep Frozen Dec 21 '12 at 09:25

score 0 · Accepted Answer · answered Dec 28 '12 at 09:26

I have not found a solution to this, but I did find a manual workaround.

You select the whole column and cut&paste is somewhere else. You cut it because the colum needs to be empty when putting the data back. After that, you select your newly created column, and copy or cut it. Then you select the column where the data needs to be (the one you just emptied), and press paste > paste special. Then you select multiply (or any other) and press ok. That way the columns will be numeric.

It might be a manual thing, but it was the one solution I've found to this problem.

SQLbase data not numeric

2 Answers2