Fw: suggestion - Issue 18
Anyone have a suggestion as to how to do this?
----- Forwarded by Peter Logan/Corporate/Spartan on 03/14/2008 02:38 PM
-----
Phil Hahn/Corporate/Spartan
03/14/2008 02:18 PM
To
Peter Logan/Corporate/Spartan@SpartanStore
cc
Subject
Fw: suggestion - Issue 18
Pete,
Do you know if a similar function in INFORMIX?? See SQL below.
Here is an example MS SQL select that will retrieve only numeric values of
a field:
Select StoreId, <other fields>
From c3.stores with (nolock)
Where IsNumeric(StoreName) = 1
And <other criteria>
Phillip Hahn
Office phone 419-891-4268
Cell phone 419-787-5459
----- Forwarded by Phil Hahn/Corporate/Spartan on 03/14/08 02:17 PM -----
Rick Murak/Corporate/Spartan
03/14/08 01:56 PM
To
Phil_Hahn@spartanstores.com
cc
Arlene Early/Corporate/Spartan@SpartanStore
Subject
Fw: suggestion - Issue 18
Phil,
See Arlene's sql below on how we might be able to work around the
non-store numbers in the store name field in the store table.
Thanks Arlene.
Rick Murak
Manager, Data Management
(616)878-2853
----- Forwarded by Rick Murak/Corporate/Spartan on 03/14/2008 01:55 PM
-----
Arlene Early/Corporate/Spartan
03/14/2008 01:50 PM
To
Rick Murak/Corporate/Spartan@SpartanStore
cc
Ray Chamberlain/Corporate/Spartan@SpartanStore
Subject
suggestion - Issue 18
My suggestion is that you do not hardcode, that will be a maintenance
nightmare. Stores are added to managed, removed from corporate, etc.
fairly frequently. I discovered long ago that we had non-numeric StoreIds
in the same field we use to maintain our StoreNumbers, records that Don
entered for a specific purpose.
Here is an example MS SQL select that will retrieve only numeric values of
a field:
Select StoreId, <other fields>
From c3.stores with (nolock)
Where IsNumeric(StoreName) = 1
And <other criteria>
-Arlene
Write your own isNumeric function, eg:
create function isNumeric(storename char(20))
returning smallint;
define isNum smallint;
define myNum integer;
let isNum = 1;
begin
on exception
let isNum = 0;
end exception
let myNum = storename;
end
return isNum;
end function;
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> Peter_Logan@spartanstores.com
> Sent: Friday, March 14, 2008 2:39 PM
> To: ids@iiug.org
> Subject: Fw: suggestion - Issue 18 [11624]
>
>
> Anyone have a suggestion as to how to do this?
> ----- Forwarded by Peter Logan/Corporate/Spartan on
> 03/14/2008 02:38 PM
> -----
>
> Phil Hahn/Corporate/Spartan
> 03/14/2008 02:18 PM
>
> To
> Peter Logan/Corporate/Spartan@SpartanStore
> cc
>
> Subject
> Fw: suggestion - Issue 18
>
> Pete,
>
> Do you know if a similar function in INFORMIX?? See SQL below.
>
> Here is an example MS SQL select that will retrieve only
> numeric values of
> a field:
>
> Select StoreId, <other fields>>
> >From c3.stores with (nolock)
>
> Where IsNumeric(StoreName) = 1
>
> And <other criteria>
>
> Phillip Hahn
>
> Office phone 419-891-4268
> Cell phone 419-787-5459
> ----- Forwarded by Phil Hahn/Corporate/Spartan on 03/14/08
> 02:17 PM -----
>
> Rick Murak/Corporate/Spartan
> 03/14/08 01:56 PM
>
> To
> Phil_Hahn@spartanstores.com
> cc
> Arlene Early/Corporate/Spartan@SpartanStore
> Subject
> Fw: suggestion - Issue 18
>
> Phil,
>
> See Arlene's sql below on how we might be able to work around the
> non-store numbers in the store name field in the store table.
>
> Thanks Arlene.
>
> Rick Murak
> Manager, Data Management
> (616)878-2853
> ----- Forwarded by Rick Murak/Corporate/Spartan on 03/14/2008
> 01:55 PM
> -----
> Arlene Early/Corporate/Spartan
> 03/14/2008 01:50 PM
>
> To
> Rick Murak/Corporate/Spartan@SpartanStore
> cc
> Ray Chamberlain/Corporate/Spartan@SpartanStore
> Subject
> suggestion - Issue 18
>
> My suggestion is that you do not hardcode, that will be a maintenance
> nightmare. Stores are added to managed, removed from corporate, etc.
> fairly frequently. I discovered long ago that we had
> non-numeric StoreIds
> in the same field we use to maintain our StoreNumbers,
> records that Don
> entered for a specific purpose.
>
> Here is an example MS SQL select that will retrieve only
> numeric values of
> a field:
>
> Select StoreId, <other fields>>
> >From c3.stores with (nolock)
>
> Where IsNumeric(StoreName) = 1
>
> And <other criteria>
>
> -Arlene
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
please try this if the field could only be an integer: ... where the_field not matches '*[^0-9]*' ... gg