Transduced Double in 'in' Statement

[Phillip Vector]

Can someone help me out here? :)

Select Characters.Callsign

from Characters

Where CharacterID in (Select Administration from Governments where GovernmentID = 4)

The right of the in comes out correct ('3,4,5,6,7,8')

However, I keep getting an error which transduces it to 3.

I was told that the answer is to put it in quotes, but it errors out. I also tried casting the characterID as a string (it's an bigint), but that didn't work.

I presume there is an obvious solution, but I'm not finding it. Anyone got any tips as to how I can find out if CharacterID is in the List generated by the subquerry?

[Nando Breiter]

Can you be more specific or clear what "Transduced Double" refers to? Is that phrase in the error message? You might also give more detail, such as the exact error message and stack trace, as well as the database engine in use here.

I often use IN statements and the only issue I typically run into is if the list is empty, an error is thrown.

And just to be clear, if you run the query "Select Administration from Governments where GovernmentID = 4", you get a list of integers, correct? Administration seems like a strange column name for an integer, so I thought I'd double check.

Aria Media Sagl

+41 (0)76 303 4477 cell
skype: ariamedia

--
Win a ticket to dev.objective from Lucee via Twitter, see http://bit.ly/1UbTMWj for details, good luck and see you there...
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CANmAeAyOPWrGW1v8DzUQ%2Bszoe5ATHeA4k7qK_N49SCoUVjpE_w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Phillip Vector]

It's returning a string. The list is stored as a varchar. I have an int (CharacterID). If I try to do an IN, it transduces the varchar list to the first entry.

and sorry. This is MySQL.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAGHrs%3D_yDFDJMX6hrdGbh-KaQbWYdFt43dPwB53_EPfJtpFdGA%40mail.gmail.com.

[andreas]

is this SQL-related only? Depending on the DB-Design and Primary Keys and datatypes, I would do that with an inner join... something like...

Select Characters.Callsign
from Characters
Inner Join Governments
On CharactersID=Administration
Where GovernmentID=4

--

[Phillip Vector]

Thanks for the help. I worked around it. :) 

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAA9eR12dutRDndLaAdaygRoNBuJgFmRyAWad8haLpYr9uCyk9g%40mail.gmail.com.