ListRest Function in SQL

Question

Hello,

I have a table with a file name like mainstreet_233232. Now I have another column in the same table as listing number. I want to replace the existing value of the listing number with the numbers in the file name. Here is my query

<cfquery name="getMLSNum" datasource="reo">
SELECT *
FROM disclosures
Where active = '1'
AND mlsNUM = '30456910'

</cfquery>

<cfoutput query="getMLSNum">
<cfquery name="updDisclosure" datasource="reo">
Update disclosures
set mlsNum = '#ListRest(GetMLSNum.file_name, "_")#'
Where RowID ='#getMLSNum.ROWID#'
AND mlsNum = '30456910'
</cfquery>
</cfoutput>

I am getting an error of "Invalid position in list"

Please help.

Thanks

voodoofrdr · Accepted Answer

Thank you for your response.

I have figured out my problem. There was a filename without '_' in my data. So i had to put that condition of #ListFind(getMLSNum.mlsNum, '_')# before I can update the table.

Dan_Bracuk · Answer

Your code looks ok, maybe it's the data. After your firstquery, do this.and see where it fails. By the way, you don't need thecfoutput tag around your update query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded