Re: I ma stuck here . In coldfusion query
> That wouldn't work though, because menuId is going to be a single > number.. even if casted to a string. foundOn is a string containing a > comma separated list of values. You're right. I even went to the length of creating a table on my db server to prove I was right and I was wrong. Understand, I had to name my table testing and the join is U G L Y but here's how I got the results erik's looking for: select DISTINCT t1.menuId, t1.label, t1.foundOn from testing t1 inner join testing t2 ON t2.foundon LIKE '%,' + CAST(t1.menuId AS VarChar(10)) + ',%' OR t2.foundon LIKE CAST(t1.menuId AS VarChar(10)) + ',%' OR t2.foundon LIKE '%,' + CAST(t1.menuId AS VarChar(10)) OR t2.foundon = CAST(t1.menuId AS VarChar(10)) where t2.menuid = 6 order by t1.menuId You would need to replace "testing" with "menu" and the 6 with your Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299557 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
On 2/20/08, C. Hatton Humphrey <[EMAIL PROTECTED]> wrote: > Actually he can > > select menuId, label, foundOn > from dbo.menu > where CAST(menuId AS VarChar(10)) IN ( >select foundOn >from dbo.menu >where menuId=#form.selTopLevel2#) > order by menuId > > Again, it's a messy messy solution and linking tables are called for. > That said, given what he's got to work with That wouldn't work though, because menuId is going to be a single number.. even if casted to a string. foundOn is a string containing a comma separated list of values. Let's say foundOn has three values in three rows: "1,5,10" "2,6,7" and "3,4,8,9" ... "1" will never be "in" that set of values. Rick -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299556 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
Yes your problem is definitely a string to integer problem.
When you
select foundOn
from dbo.menu
where menuId=#form.selTopLevel2#
you are getting back a string. So when the rest of the statement is compiled
it would be as though you were doing this...
select menuId, label, foundOn
from dbo.menu
where menuId IN ('11, 12, 13')
order by menuId
when what you want is
select menuId, label, foundOn
from dbo.menu
where menuId IN (11, 12, 13) -- no quotes
order by menuId
You could try doing it in two steps:
1. query for the found_on and assign to a variable.
SELECT foundOn
FROM dbo.menu
WHERE menuId=#form.selTopLevel2#
2. build the sql statement
3. exec the statement.
exec (#ssql#)
My string build and exec statements may not be quite right because I tend to do
things in stored procs. Here is how I would do it.
First create a stored proc in TSQL.
CREATE PROCEDURE dbo.usp_getMenus
@selTopLevel2 varchar(75)
AS
DECLARE @SSQL VARCHAR(1000)
DECLARE @foundOn varchar(75)
SELECT @foundOn = foundOn
FROM dbo.menu
WHERE menuId = @selTopLevel2
SELECT @SSQL = 'SELECT menuId, label, foundOn
FROMdbo.menu
WHERE menuId IN (' + @foundOn + ') ORDER BY menuID'
EXEC (@SSQL) -- PARENS ARE NECESSARY IN TSQL
GO
Then call
Then use the results of query2 in your page.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299530
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
Actually he can select menuId, label, foundOn from dbo.menu where CAST(menuId AS VarChar(10)) IN ( select foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by menuId Again, it's a messy messy solution and linking tables are called for. That said, given what he's got to work with Hatton On Wed, Feb 20, 2008 at 3:56 PM, Rick Root <[EMAIL PROTECTED]> wrote: > Definately can't use the IN clause for this. That's not what it does. > > If you insist on not changing your data structure you could try > something like this. > > where ','+foundOn+',' LIKE '%,#value#,%' > > But.. dear god, I'm going to be struck by lightning for even suggesting that! > > FORGIVE ME SQL GODS > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299519 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
Definately can't use the IN clause for this. That's not what it does. If you insist on not changing your data structure you could try something like this. where ','+foundOn+',' LIKE '%,#value#,%' But.. dear god, I'm going to be struck by lightning for even suggesting that! FORGIVE ME SQL GODS ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299518 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
If you are using foundOn as a list of menuId's, I would strongly suggest that you reconsider your db layout. I would, instead, use what is generally called a "join table" or "link table" that allows you to do basically the same thing, but in a cleaner, more normalized way that doesn't require special tricks. If you need more of a pointer than that, Google the terms first. It's a pretty basic db concept. --Ben Doom erik tom wrote: >> You are trying to select where menuId = foundOn. I'm guessing foundOn >> >> is a text field. Check your subselect. >> >> --Ben Doom >> >> erik tom wrote: >>> I have a column datatype text. And when i try to run the query I get >> the error >>> [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: >> text is incompatible with int >>> The query is >>> select menuId,label,foundOn from dbo.menu where menuId IN (select >> foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by >> menuId >>> > > You are correct foundOn is text datatype so how can I convert the subquery so > it becomes int datatype > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299515 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
Rick Root wrote:
> On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote:
>
>> The query is
>> select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn
>> from dbo.menu where menuId=#form.selTopLevel2#) order by menuId
>>
> You're basically saying this:
>
> select menuId, label, foundOn from dbo.menu where menuId IN ('foo')
>
> What are the possible values of foundOn?
My earlier reply was truncated. foundOn is a poor implimentation of a
join table it contains a list of child menu ID of a top level menu ID.
I.E. select menuID, label, foundON from dbo.menu where muneID IN
(12,17,19,31).
This is the problem with storing a list of values in a single field
rather then properly normalizing your database and creating a join table.
'foundOn' is a list of values I.E. 1,6,13,21 this means it is not a
number or int field but rather some type of character or text field.
menuID is an INT field I presume.
INT(14) can not be matched to (1,6,13,21).
To do what you are trying to do here will need to be done in two steps.
First query the database for the list, then query the database for the
match of that list. This allows you to pass in the character list
through CF's loose typing and the database does not complain about you
trying to match an int field to a character field.
select foundOn from dbo.menu where menuId=#form.selTopLevel2#
select menuId,label,foundOn from dbo.menu where menuId IN
(#theList.foundOn#)
Or better yet, normalize your database. Create a join table for foundOn
then you could do this much simplier join.
SELECT menuID,label,foundON
FROM dbo.menu JOIN dbo.joinTBL ON db.menu.menuID = dbo.joinTBL.childID
WHERE dbo.joinTBL.parentID = #form.selTopLevel#
This also has the advantage to not be limited to a single sub level, but
can have as many parent-child levels as you care to have.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299512
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
> On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote:
> > The query is
> > select menuId,label,foundOn from dbo.menu where menuId IN (select
> foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by
> menuId
>
> So you're expecting "foundOn" to be equal to menuID - if that's the
> case, why is foundOn a text datatype?
>
> You're basically saying this:
>
> select menuId, label, foundOn from dbo.menu where menuId IN ('foo')
>
> What are the possible values of foundOn?
>
> --
> Rick Root
> New Brian Vander Ark Album, songs in the music player and cool behind
> the scenes video at www.myspace.
com/brianvanderark
here the result
11 ProgramsPrograms0 NULL
12 Reports Reports 0 NULL
8 Lessons Lessons 0 NULL
13 Resources Resources 0 NULL
14 test1 test2 0 NULL
49 test5 test5 8,11,12 NULL
52 test10 test10 8,11NULL
53 test11 test11 8,11,12,13 NULL
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299511
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
The problem is that your database is not normalized. You need to break your table down and add another many-to-many table. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299510 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
> > You are trying to select where menuId = foundOn. I'm guessing > foundOn > > > > is a text field. Check your subselect. > > > > --Ben Doom > > > > erik tom wrote: > > > I have a column datatype text. And when i try to run the query I > get > > the error > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: > > > text is incompatible with int > > > The query is > > > select menuId,label,foundOn from dbo.menu where menuId IN (select > > > foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by > > menuId > > > > > > > > You are correct foundOn is text datatype so how can I convert the > subquery so it becomes int datatype Can you run the query in SQL Analyzer? If so, create a stored proc and call that. SQL provides capabilities for CASTing types. I do not understand your query, however. Looking at your subquery alone, it appears that you will return foundOn for the given menu id. The outer query then joins foundOn with MenuId. Are you saying the list returned in foundOn will contain the specified MenuId along with others? Is this meant to be some sort of recursive relationship (if so, a poor way to model it, IMHO)? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299508 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote:
> The query is
> select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn
> from dbo.menu where menuId=#form.selTopLevel2#) order by menuId
So you're expecting "foundOn" to be equal to menuID - if that's the
case, why is foundOn a text datatype?
You're basically saying this:
select menuId, label, foundOn from dbo.menu where menuId IN ('foo')
What are the possible values of foundOn?
--
Rick Root
New Brian Vander Ark Album, songs in the music player and cool behind
the scenes video at www.myspace.com/brianvanderark
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299507
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
erik tom wrote: > I have a column datatype text. And when i try to run the query I get the error > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: text is > incompatible with int > The query is > select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn > from dbo.menu where menuId=#form.selTopLevel2#) order by menuId > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299506 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
> You are trying to select where menuId = foundOn. I'm guessing foundOn > > is a text field. Check your subselect. > > --Ben Doom > > erik tom wrote: > > I have a column datatype text. And when i try to run the query I get > the error > > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: > text is incompatible with int > > The query is > > select menuId,label,foundOn from dbo.menu where menuId IN (select > foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by > menuId > > > > You are correct foundOn is text datatype so how can I convert the subquery so it becomes int datatype ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299504 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
> On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote: > > I have a column datatype text. And when i try to run the query I get > the error > > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: > text is incompatible with int > > The query is > > select menuId,label,foundOn from dbo.menu where menuId IN (select > foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by > menuId > > Can you share the full table description for dbo.menu? > > Also, change #form.selTopLevel2# to value="#form.selTopLevel2#"> - where XXX is the appropriate type for > the menuId field... ie... cf_sql_integer > > Rick > > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind > the scenes video at www.myspace. com/brianvanderark the full table desc is [MenuId] [int] IDENTITY(1,1) NOT NULL, [label] [varchar](100) NOT NULL, [description] [text] NOT NULL, [foundOn] [text] NOT NULL, [link] [text] NULL ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299503 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
You are trying to select where menuId = foundOn. I'm guessing foundOn is a text field. Check your subselect. --Ben Doom erik tom wrote: > I have a column datatype text. And when i try to run the query I get the error > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: text is > incompatible with int > The query is > select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn > from dbo.menu where menuId=#form.selTopLevel2#) order by menuId > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299502 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
>Looking at the query: > >select menuId, label, foundOn >from dbo.menu >where menuId IN ( > select foundOn > from dbo.menu > where menuId=#form.selTopLevel2#) >order by menuId > >would indicate that FoundOn is a varchar type field and menuid is an >integer. Perhaps you rewrite the query to match where foundOn IN () >though to be honest I'm not sure why you're doing a subquery. > >Hatton FoundOn is text datatype. The reason i doing this way because foundOn contains the list of menuId and i need to select only those where menuId is equal 52. So how can i convert the subquery which will retrieve the int datatype ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299501 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: I ma stuck here . In coldfusion query
On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote: > I have a column datatype text. And when i try to run the query I get the error > [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: text is > incompatible with int > The query is > select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn > from dbo.menu where menuId=#form.selTopLevel2#) order by menuId Can you share the full table description for dbo.menu? Also, change #form.selTopLevel2# to - where XXX is the appropriate type for the menuId field... ie... cf_sql_integer Rick -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299500 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: I ma stuck here . In coldfusion query
Looking at the query: select menuId, label, foundOn from dbo.menu where menuId IN ( select foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by menuId would indicate that FoundOn is a varchar type field and menuid is an integer. Perhaps you rewrite the query to match where foundOn IN () though to be honest I'm not sure why you're doing a subquery. Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299499 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
I ma stuck here . In coldfusion query
I have a column datatype text. And when i try to run the query I get the error [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: text is incompatible with int The query is select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn from dbo.menu where menuId=#form.selTopLevel2#) order by menuId ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299497 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
