nbcit
asked on
NOT IN give varchar to bigint error
I have 2 queries one that generates a list of userids to exclude from the second select. I thought I could use NOT IN like this
WHERE gl_users.userid NOT IN (85, ...
but am getting the error
Error converting data type varchar to bigint.
PHP is converting the output of the first query into an array which is then imploded to a comma separated list to use in the second query. It looks OK to me, any ideas what I'm doing wrong?
WHERE gl_users.userid NOT IN (85, ...
but am getting the error
Error converting data type varchar to bigint.
PHP is converting the output of the first query into an array which is then imploded to a comma separated list to use in the second query. It looks OK to me, any ideas what I'm doing wrong?
SELECT distinct gl_courselog.userid FROM gl_courselog INNER JOIN gl_courseofferings ON gl_courselog.courseofferingid=gl_courseofferings.courseofferingid INNER JOIN gl_users ON gl_users.userid=gl_courselog.userid INNER JOIN gl_status ON gl_status.statusid=gl_courselog.statusid INNER JOIN gl_segments ON gl_segments.segmentid=gl_courseofferings.segmentid INNER JOIN gl_courses ON gl_courseofferings.courseid=gl_courses.courseid INNER JOIN gl_users_statuses ON gl_users.usersstatusid=gl_users_statuses.usersstatusid INNER JOIN gl_graderecipients on gl_users.graderecipientid = gl_graderecipients.graderecipientid AND gl_segments.locationid='2' AND gl_users.advisorid='22' AND gl_courses.campusid='1' AND gl_courselog.iscounted=1 AND gl_status.showindash=1 AND gl_segments.segmentid IN (291,11056,11063,11070,11092)
SELECT gl_users.firstname, gl_users.mi, gl_users.lastname, gl_users.userid as userid, gl_users.email, gl_users_phones.phonenum, gl_phone_locations.imgfilename, coalesce((SELECT u2.userid FROM gl_users AS u2 WHERE u2.userid = gl_users.advisorid),'No Advisor') AS advisorid, coalesce((SELECT u2.nickname FROM gl_users AS u2 WHERE u2.userid = gl_users.advisorid),'') AS advisornickname, coalesce((SELECT u2.firstname FROM gl_users AS u2 WHERE u2.userid = gl_users.advisorid),'') AS advisorfirstname, coalesce((SELECT u2.lastname FROM gl_users AS u2 WHERE u2.userid = gl_users.advisorid),'') AS advisorlastname, coalesce((SELECT u2.email FROM gl_users AS u2 WHERE u2.userid = gl_users.advisorid),'') AS advisoremail FROM gl_users LEFT OUTER JOIN gl_users_phones ON gl_users.userid=gl_users_phones.userid LEFT OUTER JOIN gl_phone_locations ON gl_phone_locations.phonelocationid=gl_users_phones.phonelocationid WHERE gl_users.userid NOT IN (85, 106, 130, 141, 211, 219, 224, 228, 249, 270, 285, 294, 298, 299, 325, 345, 352, 353, 365, 383, 391, 417, 434, 456, 115912, 115931, 116004, 116009, 116031, 116038, 116056, 116085, 116104) ORDER BY gl_users.lastname,gl_users.firstname,gl_users.userid
grendel777
Hi nbcit, it sounds like your PHP is storing the values as strings and SQL is expecting bigints. Can you recast the array as integer before SQL gets a hold of it?
The column gl_users.userid contains a non numeric value that cannot be converted to a bigint.
ASKER
I've tried echoing the array to the screen and copying it to Studio to run. It shouldn't matter then what PHP is producing, right? Studio should only see the numbers I think but thats where i get the error message.
If I remove NOT it runs fine, giving me the wrong results of course, but it runs
WHERE gl_users.userid IN (85, ...
gl_users.userid is only numbers and is type bigint. I think the fact it runs when the NOT isn't there rules out that as the issue.
wierd huh!
I've upped the points as I'm back at work and could use help on this as soon as..
If I remove NOT it runs fine, giving me the wrong results of course, but it runs
WHERE gl_users.userid IN (85, ...
gl_users.userid is only numbers and is type bigint. I think the fact it runs when the NOT isn't there rules out that as the issue.
wierd huh!
I've upped the points as I'm back at work and could use help on this as soon as..
>>gl_users.userid is only numbers and is type bigint. <<
Than the error is coming from somewhere else. Specifically I would focus on the data types iscounted, showindash and/or segmentid.
On an unrelated note, you really should fix that SQL Syntax, it cannot be very performant to do 5 subqueries when a simple LEFT OUTER JOIN would suffice. Also, you should be using a WHERE clause in your first query instead of using the INNER JOIN to filter out scalar values. Let me know, if you would like a better version.
Than the error is coming from somewhere else. Specifically I would focus on the data types iscounted, showindash and/or segmentid.
On an unrelated note, you really should fix that SQL Syntax, it cannot be very performant to do 5 subqueries when a simple LEFT OUTER JOIN would suffice. Also, you should be using a WHERE clause in your first query instead of using the INNER JOIN to filter out scalar values. Let me know, if you would like a better version.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the example, that really is an improvement. It made it clear that this was the problem
ISNULL(u2.userid, 'No Advisor') advisorid,
Doh!
Originally i had No Advisor for each one so of course I was getting multiple outputs of it. I removed all but one and left in the only one that wont allow it!!
How do you mean use a table. Like a temporary table? If so I've no idea how to do that.
ISNULL(u2.userid, 'No Advisor') advisorid,
Doh!
Originally i had No Advisor for each one so of course I was getting multiple outputs of it. I removed all but one and left in the only one that wont allow it!!
How do you mean use a table. Like a temporary table? If so I've no idea how to do that.
ASKER
My second query returns 100 rows, yours returns 14905 rows. I need both student and advisor info which is in the same table, gl_users. The users tables holds all users students,admins, instructors etc. In this query I need a list of student from their ids and also their advisors name and email.
Can you left join to the same table you're querying?
Can you left join to the same table you're querying?
ASKER
Sorry my error. I had move on to selecting IN not NOT IN so the results /are/ the same. So thanks for that. Another thing learned that'll change how I do things... I hope!
I'd still be interested to see what you mean about using a table.
I'd still be interested to see what you mean about using a table.
If the selection is always hard coded with:
85, 106, 130, 141, 211, 219, 224, 228, 249, 270, 285, 294, 298, 299, 325, 345, 352, 353, 365, 383, 391, 417, 434, 456, 115912, 115931, 116004, 116009, 116031, 116038, 116056, 116085, 116104)
Than create a table (UserIDs) and add these values to a column called UserID. Make sure it is indexed you can then modify your query as follows:
85, 106, 130, 141, 211, 219, 224, 228, 249, 270, 285, 294, 298, 299, 325, 345, 352, 353, 365, 383, 391, 417, 434, 456, 115912, 115931, 116004, 116009, 116031, 116038, 116056, 116085, 116104)
Than create a table (UserIDs) and add these values to a column called UserID. Make sure it is indexed you can then modify your query as follows:
SELECT u.firstname,
u.mi,
u.lastname,
u.userid as userid,
u.email,
up.phonenum,
pl.imgfilename,
ISNULL(u2.userid, 'No Advisor') advisorid,
ISNULL(u2.nickname, '') advisornickname,
ISNULL(u2.firstname, '') advisorfirstname,
ISNULL(u2.lastname, '') advisorlastname,
ISNULL(u2.email, '') advisoremail
FROM gl_users u
LEFT JOIN gl_users_phones up ON u.userid = up.userid
LEFT JOIN gl_phone_locations pl ON pl.phonelocationid = up.phonelocationid
Left Join gl_users u2 On u.advisorid = u2.userid
Left Join UserIDs ud On u.UserID = ud.UserID
WHERE ud.UserID Is Null
ORDER BY
u.lastname,
u.firstname,
u.userid