[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 961
  • Last Modified:

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?



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

Open in new window

0
nbcit
Asked:
nbcit
  • 4
  • 4
1 Solution
 
grendel777Commented:
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?
0
 
Anthony PerkinsCommented:
The column gl_users.userid contains a non numeric value that cannot be converted to a bigint.
0
 
nbcitAuthor Commented:
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..
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
The attachec code is not the solution, but shoiuld be more efficient.  Also, consider tableing the values from your WHERE      u.userid NOT IN (85, 106, 130, 141, ...

SELECT	distinct l.userid 
FROM	gl_courselog l
	INNER JOIN gl_courseofferings o ON l.courseofferingid = o.courseofferingid 
	INNER JOIN gl_users u ON u.userid = l.userid 
	INNER JOIN gl_status s ON s.statusid = l.statusid 
	INNER JOIN gl_segments m ON m.segmentid = o.segmentid 
	INNER JOIN gl_courses c ON o.courseid = c.courseid 
	INNER JOIN gl_users_statuses us ON u.usersstatusid = us.usersstatusid 
	INNER JOIN gl_graderecipients g on u.graderecipientid = g.graderecipientid 
Where	m.locationid = '2' 
	AND u.advisorid = '22' 
	AND c.campusid = '1' 
	AND l.iscounted = 1 
	AND s.showindash = 1 
	AND m.segmentid IN (291,11056,11063,11070,11092) 
 
  
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
WHERE	u.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 
	u.lastname, 
	u.firstname, 
	u.userid
 

Open in new window

0
 
nbcitAuthor Commented:
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.
0
 
nbcitAuthor Commented:
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?
0
 
nbcitAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
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:

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

Open in new window

0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now