[Info-Ingres] Ingres equivalent of InNumeric()
Richard Harden
Greetings All,
I have a ‘non-ingres’ sql script that I am converting to ingres, and it uses a function called IsNumeric( value ) which returns true if the data can be converted to numeric data
Which is ideal for my issue.
I have two fields, job_code1 varchar(4) and job_code2 also varchar(4)
The first may contain HOUR, WKS, MTHS,YEAR
And the second a multiplier
1,10,100,1000,10K – yes dammit, thats a K in the field, representing 1000’s
The script I am converting uses
CASE JobCode1
WHEN ‘WKS’ THEN
CASE
WHEN IsNumeric(job_code2) then cast(job_code2 as Integer) * 7
Else
0
... you get the picture, and I am writing this from memory (a bad one)
And I need to replicate the functionallity into an ingres script
I thought of WHEN job_code2 IS INTEGER
Perhaps to test to see if the value can be converted to an integer, but that doesn’t appear to work, and I get error on INTEGER in that line
In fact I cannot get the IS INTEGER to work at all, even when I use select (12 IS INTEGER)
Is there a way in ingres to do what I want?
PS env is Ingres 9,2SP1 on windows 32bit
Currently trying in an isql session, but when successful it will be scripted \g
TIA
Richard
/***************************\
| New Zealander, leading the world |
\***************************/
Kim Ginnerup
What about making a case that check to see if the last char is a: ”K”
Using the right() function
Kim Ginnerup
Martin Bowes
Hi Richard.
There are quite a few SQL predicates which are often overlooked.
The one you want is ‘IS INTEGER’, there is also ‘IS DECIMAL’ or ‘IS FLOAT’.
For example…
* select case when 9 is integer then 1 else 0 end\g
Executing . . .
┌──────┐
│col1 │
├──────┤
│ 1│
└──────┘
(1 row)
continue
* select case when 'a' is integer then 1 else 0 end\g
Executing . . .
┌──────┐
│col1 │
├──────┤
│ 0│
└──────┘
(1 row)
continue
*
Martin Bowes
Ingres Forums
This works:
1> select if('123' is integer, 1, 0)
+------+
|col1 |
+------+
| 1|
+------+
(1 row)
2> select if('abc' is integer, 1, 0)
+------+
|col1 |
+------+
| 0|
+------+
(1 row)
--
Bodo
------------------------------------------------------------------------
Bodo's Profile: http://community.actian.com/forum/member.php?userid=16
View this thread: http://community.actian.com/forum/showthread.php?t=14702
Richard Harden
Thanks Kim, that’s a thought too,
One other I had thought of was to use a view that filtered out the ‘%K%’ values so I wouldn’t have to test for them.
I will also try Martin’s syntax
Cheers
Richard
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Kim Ginnerup
Sent: Wednesday, 12 September 2012 6:27 p.m.
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Ingres equivalent of InNumeric()
What about making a case that check to see if the last char is a: ”K”
Using the right() function
Kim Ginnerup
Fra: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] På vegne af Richard Harden
Sendt: 12. september 2012 06:39
Richard Harden
Appreciated All !
I shall be trying all suggestions at work tomorrow
Cheers
Richard
From: Martin Bowes [mailto:martin...@ctsu.ox.ac.uk]
Sent: Wednesday, 12 September 2012 8:19 p.m.
To: Richard Harden
Subject: RE: [Info-Ingres] Ingres equivalent of InNumeric()
select job_code1, job_code2,
case job_code1 when 'wks'
then case when job_code2 is integer then integer(job_code2) * 7
else 0 end
else -1 end /* Note that this also has to be an integer or null */
from x
From: Richard Harden [mailto:richard...@orcon.net.nz]
Sent: 12 September 2012 09:02
To: Martin Bowes
Subject: RE: [Info-Ingres] Ingres equivalent of InNumeric()
Thanks Martin
I was actually trying to use the ‘IS INTEGER’, I just could not get it to work in an isql window
Something along the lines of
Case job_code2
When job_code2 IS INTEGER then cast(job_code2 as integer) * 7 else 0 end \g
May be its just me getting the syntax wrong. I’ll try again tomorrow.
Cheers
Richard
From: Martin Bowes [mailto:martin...@ctsu.ox.ac.uk]
Sent: Wednesday, 12 September 2012 7:26 p.m.
To: Ingres and related product discussion forum
From: Richard Harden [mailto:richard...@orcon.net.nz]
Richard Harden
Thank you for all those replies / suggestions.
I now have a script that runs successfully in Ingres
Cheers
Richard.
Ingres Forums
Ingres has three different functions: IS INTEGER, IS DECIMAL & IS FLOAT
that can be used to determine if a particular result can be assigned to
a column of the given data type.
From the documentation:
x IS INTEGER
i
s true if x is integer in form.
If x is a string, then this will be true if the value is a number where
any fractional digits are zero and the signed result can be stored in an
int8.
Any leading or trailing white space is ignored.
If x is a DECIMAL or a FLOAT value, then this predicate is true if the
value can be stored in an int8 column and any fractional digits are
zero.
x IS DECIMAL
is true if x is decimal in form.
If x is a string, then this is true if the value is a number that can be
stored as a decimal data type with no loss of accuracy.
Any leading or trailing white space is ignored.
If x is a FLOAT value then this predicate is true only if the number can
be stored in an decimal column without loss of precision of overflow.
x IS FLOAT
is true if x is float in form. If x is a string, then this is true if
the value is a number that can be stored as a float data type.
All integers and decimals can be represented as float but there may be
some loss of accuracy.
Any leading or trailing white space is ignored.
Here is an example:
CREATE TABLE mytab (id INTEGER, zip VARCHAR(8), city varchar(32));
INSERT INTO mytab VALUES (1, '60389', 'Frankfurt'), (2, 'D-60389',
'Frankfurt');
SELECT id, CASE WHEN zip IS INTEGER THEN zip ELSE 'FALSE' END, ort FROM
mytab;
CREATE TABLE mytab (id INTEGER, zip VARCHAR(8), city varchar(32));
INSERT INTO mytab VALUES (1, '60389', 'Frankfurt'), (2, 'D-60314',
'Frankfurt');
SELECT id, CASE WHEN zip IS INTEGER THEN zip ELSE 'FALSE' END, city
FROM mytab;
--
myguest
------------------------------------------------------------------------
myguest's Profile: http://community.actian.com/forum/member.php?userid=8254
Mark Piniarski
We have an application that allows users to select data from database
tables, including being able to do the various joins. While we of
course have built in as many common sense precautions as we can think
of, ultimately it's certainly possible for a user to set off a cartesian
product of sorts.
Is there a way to pre-obtain how many result records there are going to
be from the query in a fairly quick/efficient manner, without actually
executing the full query and making the user wait forever until either
some ridiculously-sized output is generated...or II_WORK fills up, or
some other bad thing happens that aborts the query?
I know all about 'set qep / set optimizeonly'...that's a nice way to see
if a query is out of control without running it...but that approach
won't work for this, where you have users dynamically creating and
setting off whatever select stms they want.
Thanks for any advice!
Mark
Bodo
Anyway, have a look at "set maxrow ..." statement.
Bodo.
Roy Hann
> Your question doesn't seem to have anything to do with this thread (Ingres equivalentof IsNumeric).
question.
> Anyway, have a look at "set maxrow ..." statement.
http://docs.actian.com/ingres/9.2/sql-reference-guide/2105-database-privileges
Note that either way, the query is aborted preemptively based on the
row/page/IO estimates, and returns no rows (because it never starts
running).
--
Roy
UK Actian User Association Conference 2012 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.
Bodo
Ingres Forums
Is this a known issue:-
E is classed as an integer (both in lowercase and uppercase)
2> select if('e' is integer, 1, 0)
┌──────┐
│col1 │
├──────┤
│ 1│
└──────┘
(1 row)
End of Request
Anyone know of a work around?
Cheers
--
AgamiHeron
------------------------------------------------------------------------
AgamiHeron's Profile: http://community.actian.com/forum/member.php?userid=121854