conversion error

Hi, can anyone please shed some light on this error:

[OLE DB Destination [466]] Error: There was an error with input column "Price" (518) on input "OLE DB Destination Input" (479). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The column "price" is a numeric (9)

In the flat file connection manager, the datatype for the price column is a float [dt r4]. I've also tried numeric, etc.

How do I resolve this error?

Thanks much

Don't you have a scale on that Price column? Can't a price have cents?|||

Yes, the price has cents.

|||

sadie wrote:

Yes, the price has cents.

But you said the field is NUMERIC(9). There's no scale, so the cents (decimal) can't be stored.|||

Hmm,

here's what the database says, and the data looks like: xxx.xxxxxxxxxxxx, so that's correct.

type computed length prec scale Price numeric no 9 18 12

|||Okay, so it's a NUMERIC(18,12)

Seems weird for a price field as it can only hold $999. Anyway, back to the problem at hand.... Do you have data that exceeds $999?

EDIT: I apparently can't do math, everyone. I still had "9" stuck in my head. 12 - 9 = 3. |||

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

|||

Nevermind. There is a bad row in the data file. That is what is causing the overflow error.

Thanks

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

That is correct.

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

Yes, 18 specifies how many significant digits there are, while 12 of those 18 are to the right of the decimal point. Sorry, I'm losing my math mind, apparently.|||

No problem.

Your response gave me the idea to check my data file, and that's how I found the bad rows.

CONTAINS clause with multi-word "AND" inflectional searching?? help...

What if you want to search using FTS with AND logic using the FORMSOF(inflectional,...) inside the CONTAINS() clause?

if my search phrase is "light hearted" I can easily do an OR search using the following in my where clause:
CONTAINS(Colname,'formsof(INFLECTIONAL,light,heart ed).
but the and is far more tricky...

does anyone know how to do this without having multiple Contains statements (which greatly increases overhead)?

I know that I can use AND in a straight contains like so:
CONTANS(column, '"light" AND "hearted"') but this does not allow me to explore inflectional variations on the words...

nesting multiple FORMSOF's doesn't seem to work either like so:
contains(column,'"formsof(inflectional,light)"' AND 'formsof(inflectional,hearted)"')

anyone else found how to do this?just to clarify I think 2 better search words for my ex. would have been "sport" and "award" and it's really important that I get results for Sports, Sported, Awards, and Awarded. Then I'd have any combination of the two words Inflectional variations (one from each root word) that exist in the same record returned in my resultset.