Where is the MISTAKE in my CODE? 2

[Preetham16]

Hi Guru's

SELECT
convert(numeric(10),a.partner(isnull(a.partner,'0'))) as Partnernr from smokna1 as a

I am getting the following Error message -

Invalid Object name a.partner


PR

 

[ca8msm]

convert(numeric(10),[!]a.partner[/!](isnull(a.partner,'0')))

What is that bit trying to do?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Preetham16]

convert(numeric(10),a.partner(isnull(a.partner,'empty'))) as 'Partnernr',

First of all the part

(isnull(a.partner,'empty')) is checking if a.partner has any null values then isnull function is filling it with text called empty ...

Then the Convert function is converting the Text to Numeric datatype and the filed a.partner is being written as PartnerNr

Regards
pr

 

[r937]

try

coalesce(convert(numeric(10),a.partner),0) as Partnernr

r937.com | rudy.ca

 

[Preetham16]

I still have the problem in my Select it says it cannot convert the VARCHAR to NUMERIC where as when I try directly on the SQL Server it works...

PR

 

[AlexCuse]

In addition, you can't have more than one data type in the same column in your query.

So a numeric value and the text 'empty' cannot be in the same column. But its' ok to have the integer 0 as Rudy suggests. Do you see the difference?

Your isnull function needs to either return everything as varchar or numeric. And I believe that COALESCE should be used instead of ISNULL, but I am not all that well read on best practices.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Sounds like you have some bad data in your table (that can't be converted to numeric). Read this faqs -

faq183-6423

Good Luck,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[djj55]

Would this work?

convert(numeric(10),isnull(a.partner,'0')) as Partnernr

djj