conversion failed on nested query

Question

Post reply

conversion failed on nested query

cajun_sql

SSCommitted

Points: 1842
More actions
August 24, 2015 at 4:15 pm

#319984

Hi,
I'm working with a 'nested query,' which is needed to only select the non-top IDs of a resultset having more than one row with an ID in common.
In other words, from the 'table' below, I want to get the 'non max' IDs of UserNames having more than one entry, in which also, IsCurrent=1. Below, it would return IDs: 1,2,3.
tp_ID | UserName | tp_IsCurrent
_______________________
1 tsmith 1
2 tsmith 0
3 tsmith 1
4 tsmith 1
Here is the code I've been using (below). I've been getting the error
"Conversion failed when converting the nvarchar value .... to data type int."
Thanks for any input.
select [tp_ID] from AllUserData
where [tp_ID] not in
(
select Max([tp_ID]) from AllUserData
where [tp_ID] in
(
select [tp_ID] from AllUserData where
(tp_IsCurrent='1' )
AND
([tp_ID] in
(
select UserName
from AllUserData
Group by UserName
Having (Count(*) > 1)
))
)
)

Viewing 3 posts - 1 through 2 (of 2 total)

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

Roland C Hall of Fame Points: 3473 More actions · Answer 1

cajun_sql (8/24/2015)
...
([tp_ID] in
(
select UserName
)
...

Maybe tp_ID is an integer, and UserName a varchar 😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

cajun_sql (8/24/2015)
Hi,
I'm working with a 'nested query,' which is needed to only select the non-top IDs of a resultset having more than one row with an ID in common.
In other words, from the 'table' below, I want to get the 'non max' IDs of UserNames having more than one entry, in which also, IsCurrent=1. Below, it would return IDs: 1,2,3.
tp_ID | UserName | tp_IsCurrent
_______________________
1 tsmith 1
2 tsmith 0
3 tsmith 1
4 tsmith 1
Here is the code I've been using (below). I've been getting the error
"Conversion failed when converting the nvarchar value .... to data type int."
Thanks for any input.
select [tp_ID] from AllUserData
where [tp_ID] not in
(
select Max([tp_ID]) from AllUserData
where [tp_ID] in
(
select [tp_ID] from AllUserData where
(tp_IsCurrent='1' )
AND
([tp_ID] in
(
select UserName
from AllUserData
Group by UserName
Having (Count(*) > 1)
))
)
)

In other words, from the 'table' below, I want to get the 'non max' IDs of UserNames having more than one entry, in which also, IsCurrent=1. Below, it would return IDs: 1,2,3.

ID 2 has IsCurrent=0

It's not entirely clear where exactly you wish to apply the IsCurrent filter, but I'm sure a variation on this should be easier to code and easier to follow:

IF OBJECT_ID('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample

CREATE TABLE #Sample (tp_ID INT, UserName VARCHAR(20), tp_IsCurrent TINYINT)

INSERT INTO #Sample (tp_ID, UserName, tp_IsCurrent)

VALUES

(1,'tsmith',1),

(2,'tsmith',0),

(3,'tsmith',1),

(4,'tsmith',1),

(1,'tjones',1),

(2,'tjones',0),

(3,'tjones',1)

SELECT

tp_ID, UserName, tp_IsCurrent,

rn = ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY tp_ID DESC)

FROM #Sample

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden