Back quote in list

Forum|Forum|17 years ago
July 17, 2008
5 replies
935 views

I'm using the ValueList function below to turn a column in a first query into a comma delimited list for the next query that I will then join. Is there a way to change that code to include a back quote before and after every result for MySql db? The client has set up his tables as 12-345 instead of 12_345 and CFSQL doesn't like it.

Thanks!

Getting started

This topic has been closed for replies.

N

Newsgroup_User

Inspiring

are you sure list="yes" is going to work, Dan? won't
value="#valuelist(getPad.WellID)#" already cause the error?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: Newsgroup User
are you sure list="yes" is going to work, Dan? won't
value="#valuelist(getPad.WellID)#" already cause the error?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

The only time it doesn't work for me is when I forget the parentheses or when I'm using Oracle and the list is too long.

D

Dan_Bracuk

Inspiring

So what you are saying is that GetPad.WellID is a character field with values such as 12-345?

Ian and Azadi both gave accurate answers but I'm surprised that neither recommended using cfqueryparam list="yes"

N

Newsgroup_User

Inspiring

use QuotedValueList() instead

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

N

Newsgroup_User

Inspiring

The function you are looking for is listQualify() which allows you to
specify a character, such as a single quote ['], that will be placed
around every element in the list.

I

InkfastAuthor

Inspiring

ListQualify is working with this code, but it's only returning the first row of the table.

<cfset WellList=ListQualify(GetPad.WellID, "`", ",", "all")>

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: InkFasT!
ListQualify is working with this code, but it's only returning the first row of the table.

<cfset WellList=ListQualify(GetPad.WellID, "`", ",", "all")>

If the list had more than one item, your query should not have returned any records. How many records did GetPad return?

And, just out of curiousity, are the two tables in different databases? If not, why are you doing this at all when you can simply join the tables.