• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 810
  • Last Modified:

Cast () Failure on blank field?

Here's my SQL statement

SELECT     KPI.[Column 2] AS Store, KPI.[Column 3] AS Date, CAST(KPI.[Column 5] AS numeric(12, 2)) - CAST(TestKPI.[Column 5] AS numeric(12, 2)) AS Difference,
                      KPI.[Column 5] AS Production, TestKPI.[Column 5] AS Test
FROM         KPI INNER JOIN
                      TestKPI ON KPI.[Column 2] = TestKPI.[Column 2] AND KPI.[Column 3] = TestKPI.[Column 3]
ORDER BY Date

The syntax is correct(I used it with different fields without any issue), but for some reason the cast() function is choking on blank fields?  I'm not sure how to compenstate for this, but I'm pretty sure it must be an esay fix that I'm missing.  I could do it is access without an issue, but SQL is tripping me up.

Thanks in advance.

Cal
0
Calvin Brine
Asked:
Calvin Brine
  • 5
  • 2
  • 2
  • +2
1 Solution
 
wstuphCommented:
You mean it's choking on nulls?  Or are the fields actually blank?  What do you want it to display if there's no data there?  
0
 
Calvin BrineAuthor Commented:
I'm using SQL server 2005, and running the query through it's query tool.  When I query the field without the cast involved it shows an empty field(Doesn't say Null, which I belive SQL does when it encounters a null).  So I'm thinking it's just a blank field, that cast is trying to change to a numeric value, which is where is fails.

If no data exists, I fine with just returning a  zero.

Cal
0
 
folderolCommented:
Hi Cal,

This is interesting, cast(col1 as numeric) generates an error if col1 contains non-numeric characters, but Cast(col1 as float) tolerates spaces and emptiness.

So Cast(' ' as float) and cast('' as float) both return zero, while cast(' ' as numeric) is an error.

So,

cast(CAST(KPI.[Column 5] AS float) - CAST(TestKPI.[Column 5] AS float) as numeric(12,2)) AS Difference

would fix this.  It will return NULL if either column is null.  You can handle that with

cast(CAST(coalesce(KPI.[Column 5],0) AS float) - CAST(coalesce(TestKPI.[Column 5],0) AS float) as numeric(12,2)) AS Difference


Tom
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
...same as before...,

CASE WHEN LTRIM(KPI.[Column 5]) = '' THEN 0 ELSE
CAST(KPI.[Column 5] AS numeric(12, 2)) END -
CASE WHEN LTRIM(TestKPI.[Column 5)] = '' THEN 0 ELSE
CAST(TestKPI.[Column 5] AS numeric(12, 2)) END AS Difference,

...same as before...
0
 
Calvin BrineAuthor Commented:
folderol,
  I tried yours and got this error.  The fields need a 2 decimal point percision, which I didn't mention and is the cause of the problem

Conversion failed when converting the nvarchar value '853.74' to data type int.


ScottPletcher,
  I tried yours and got an error on something near the "=", everything looked logical though.

Here's the revised statement with your CASE in it.

SELECT     KPI.[Column 2] AS Store, KPI.[Column 3] AS Date,
CASE WHEN LTRIM(KPI.[Column 5]) = '' THEN 0 ELSE
CAST(KPI.[Column 5] AS numeric(12, 2)) END -
CASE WHEN LTRIM(TestKPI.[Column 5)] = '' THEN 0 ELSE
CAST(TestKPI.[Column 5] AS numeric(12, 2)) END AS Difference,  
KPI.[Column 4] AS Production, TestKPI.[Column 4] AS Test
FROM         KPI INNER JOIN
                      TestKPI ON KPI.[Column 2] = TestKPI.[Column 2] AND KPI.[Column 3] = TestKPI.[Column 3]
ORDER BY Date

Cal
0
 
ptjcbCommented:
cast(CAST(KPI.[Column 5] AS float(12,2)) - CAST(TestKPI.[Column 5] AS float(12,2)) as numeric(12,2)) AS Difference
0
 
Calvin BrineAuthor Commented:
ptjjcb,
  Ran into this with you statement.

Msg 291, Level 16, State 1, Line 1
CAST or CONVERT: invalid attributes specified for type 'float'
Msg 291, Level 16, State 1, Line 1
CAST or CONVERT: invalid attributes specified for type 'float'

Cal
0
 
folderolCommented:
select cast(cast('853.74' as float) as numeric(12,2))

I am not converting anything to int, so I suspect another row in your query generates the error.

"float(12,2)"   is unnecessary.  Numeric(12,2) will round up or down appropriately.  If you must round before subtracting then

round(CAST(KPI.[Column 5] AS float),2)

is the appropriate form.

Tom
0
 
Scott PletcherSenior DBACommented:
OOPS, sorry, I got the ) and the ] backwards:

...
CASE WHEN LTRIM(TestKPI.[Column 5]) = '' THEN 0 ELSE
...
0
 
Calvin BrineAuthor Commented:
Scott,
  I ended up using ADO and VBA in Excel to complete the validation process I was doing.  I had about 270 fields to compare in a test and production table, and no way to automate it using SQL alone.  Your revised statement did work for the question I asked, so I'm assigning the points to you.

Thanks
Cal
0
 
Calvin BrineAuthor Commented:
PS-  Thanks to everyone else for their efforts as well!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now