Unable convert nvarchar to int

Question

Post reply

Unable convert nvarchar to int

bjmendez

SSC Enthusiast

Points: 164
More actions
October 7, 2009 at 4:17 pm

#211863

Hi all, i'm new in the forum and i need your help.
I have two tables
tbl_fails
-------------------
id_fail ------ (int)
fail ------ (nvarchar)
tbl_house_fails
------------------
id_house_fail ------ (int)
id_fails ------ (nvarchar)
date_fail ----- (nvarchar)
some data for the tables can be
tbl_fails
---------
1 - fail1
2 - fail2
3 - fail3
tbl_house_fails
---------------
1 - 1 - 07/10/2009
2 - 2 - 07/10/2009
3 - 3 - 07/10/2009
4 - 1,2,3 - 07/10/2009
5 - 2,3 - 07/10/2009
i have this query:
SELECT
id_house_fails,
(SELECT fail FROM tbl_fails WHERE id_fail in (id_fails) as Fails,
date_fail
FROM tbl_house_fails
I want something like
1 - fail1 - 07/10/2009
2 - fail2 - 07/10/2009
3 - fail3 - 07/10/2009
4 - fail1,fail2,fail3 - 07/10/2009
5 - fail2,fail3 - 07/10/2009
how can obtain this data because whit the query that i'm doing i get the error:
error converting data nvarchar '1,2,3' to int
What do i need for to get that result?
some advice?........ thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The problem is your data, such as 1,2,3. This can't be converted to an int.

LutzM SSC Guru Points: 107049 More actions · Answer 2

Easiest thing would be to change your data to be normalized:

Instead of

tbl_house_fails

---------------

4 - 1,2,3 - 07/10/2009

insert the values as separate rows. It's a lot easier to work with...

4 - 1 - 07/10/2009

4 - 2 - 07/10/2009

4 - 3 - 07/10/2009

A little more complicated would be using a split string function. Please search this site for samples (since I don't recommend using it if the option described above is an alternative, I don't feel like posting the code...) 😉 .

Edit: Sorry, Lynn! I'm not stalking.... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bjmendez SSC Enthusiast Points: 164 More actions · Answer 3

i thought in that but i'm searching another solution, thanks.

If really it isn't possible then i will change all

LutzM SSC Guru Points: 107049 More actions · Answer 4

Like I stated above:

If you insist on using your data structure you might want to search for "split string function" on this site. It'll help you for now but it's calling for pain in the future (either performance wise or in terms of data size: how large are you planning the column [id_fails] ------ (nvarchar) to be? How many values can you store before exceeding the limit? What if it does? - Stuff like that...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bjmendez SSC Enthusiast Points: 164 More actions · Answer 5

Sorry for my insistence, i'm like my table...... failing 🙁 trying to do this.

Thanks a lot for the asistence.

Best regards.....

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

rekreativo2003 (10/7/2009)
Sorry for my insistence, i'm like my table...... failing 🙁 trying to do this.
Thanks a lot for the asistence.
Best regards.....

Jeez... I really know you like your table but it's truely a horrible practice to store CSV data in a column. Think about it... look at the problem you're having with it right now. You really need to reconsider.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jeff Moden (10/7/2009)
rekreativo2003 (10/7/2009)
Sorry for my insistence, i'm like my table...... failing 🙁 trying to do this.
Thanks a lot for the asistence.
Best regards.....
Jeez... I really know you like your table but it's truely a horrible practice to store CSV data in a column. Think about it... look at the problem you're having with it right now. You really need to reconsider.

I think you misread this post, Jeff. Read it again, but change your emphasis. His table is failing and so is he.

😉