MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

RE: SQL: how to filter out spaces in fields defined as packed numeric



fixed

Yikes there are over 100 columns in this file. Any way to accomplish WITHOUT naming almost all columns?

Also I need to copy each record AS IS, without dropping any columns. I want to drop entire records with any bad data, but other records must retain all columns.

Thanks




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Monday, February 11, 2013 10:45 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to filter out spaces in fields defined as packed numeric

Joel,
Try this:
SELECT f1.f2.etc. from Datafile where substr(OrderQty,1,1) = x'40'
Replace the f1,f2,etc with field names from the file, but not the OrderQty
which SQL will see as invalid data.



On Mon, Feb 11, 2013 at 11:34 AM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

I have a file with hex '40' spaces in a field OrderQty defined to DDS as
pack numeric.

I am using SQL to read the file and SQL croaks when it reads these nasty
invalid records.

Is it possible to filter out these records in SQL?

For example something like:

SELECT * from Datafile where left(OrderQty,1) = *blanks


Here is the SQL error:
Data mapping error on member SQLTEMP1.
A data mapping error occurred on field OrderQty in


1 -- There is data in a decimal field that is not valid.

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.









Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact