Highlighted

CFFunction Select Where clause not working correctly

Engaged ,
Oct 05, 2015

Copy link to clipboard

Copied

I have the following function and in the WHERE clause, the s.subsiteURL no like '/training%' is working or something. It's still selected a sub site with URL starting with /training. Is there a way to output the result of this function to the browser? I have tried the <cfdump var="#qryCSPages#"> but it's not dumping anything on the screen.

<cffunction name="getCommonSpotPages" access="public" returnType="query">

  <cfset var qryCSPages = queryNew("")>

  <cftry>

  <!--- // get CommonSpot site URLs --->

  <cfquery name="qryCSPages" datasource="#application.sitemap.sDSN#">

  select p.ID, s.subsiteURL, p.filename, p.DateContentLastModified, u.publicFileName, p.uploaded as bIsUploaded, s.uploadURL,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.nPriorityFieldID# and dfv.versionState = 2 ) as nPriority,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.sFrequencyFieldID# and dfv.versionState = 2 ) as sFrequency

  from SitePages p

  join SubSites s

  on p.SubSiteID = s.id

  left join UploadedDocs u

  on p.id = u.pageID

  where (p.expDate is null

  OR p.expDate > getdate())

  and p.pageType = 0 <!--- uploaded documents and content pages only --->

  and p.approvalStatus <> 1 <!--- // exclude inactive pages --->

  and s.siteState <> 0 <!--- // exclude inactive subsites --->

  and s.subsiteURL not like '/training%' <!--- specify all pages inside of the subsite to be exlcuded from the sitemap --->

  and s.ID not in (<cfqueryparam value="#application.sitemap.lstExcludeSubsites#" cfsqltype="CF_SQL_INTEGER" list="yes" /> ) <!--- // exclude particular subsites --->

  order by p.ID desc

  </cfquery>

  <cfcatch><cfdump var="#cfcatch#"></cfcatch>

  </cftry>

  <cfdump var="#qryCSPages#">

  <cfreturn qryCsPages>

</cffunction>

Views

374

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CFFunction Select Where clause not working correctly

Engaged ,
Oct 05, 2015

Copy link to clipboard

Copied

I have the following function and in the WHERE clause, the s.subsiteURL no like '/training%' is working or something. It's still selected a sub site with URL starting with /training. Is there a way to output the result of this function to the browser? I have tried the <cfdump var="#qryCSPages#"> but it's not dumping anything on the screen.

<cffunction name="getCommonSpotPages" access="public" returnType="query">

  <cfset var qryCSPages = queryNew("")>

  <cftry>

  <!--- // get CommonSpot site URLs --->

  <cfquery name="qryCSPages" datasource="#application.sitemap.sDSN#">

  select p.ID, s.subsiteURL, p.filename, p.DateContentLastModified, u.publicFileName, p.uploaded as bIsUploaded, s.uploadURL,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.nPriorityFieldID# and dfv.versionState = 2 ) as nPriority,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.sFrequencyFieldID# and dfv.versionState = 2 ) as sFrequency

  from SitePages p

  join SubSites s

  on p.SubSiteID = s.id

  left join UploadedDocs u

  on p.id = u.pageID

  where (p.expDate is null

  OR p.expDate > getdate())

  and p.pageType = 0 <!--- uploaded documents and content pages only --->

  and p.approvalStatus <> 1 <!--- // exclude inactive pages --->

  and s.siteState <> 0 <!--- // exclude inactive subsites --->

  and s.subsiteURL not like '/training%' <!--- specify all pages inside of the subsite to be exlcuded from the sitemap --->

  and s.ID not in (<cfqueryparam value="#application.sitemap.lstExcludeSubsites#" cfsqltype="CF_SQL_INTEGER" list="yes" /> ) <!--- // exclude particular subsites --->

  order by p.ID desc

  </cfquery>

  <cfcatch><cfdump var="#cfcatch#"></cfcatch>

  </cftry>

  <cfdump var="#qryCSPages#">

  <cfreturn qryCsPages>

</cffunction>

Views

375

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 05, 2015 0
Advocate ,
Oct 05, 2015

Copy link to clipboard

Copied

As a troubleshooting step, run the query in a query utility that comes with your database engine, for example SQL Server Management Studio for SQL Server, or Workbench for MySQL.

Naturally you will need to replace any ColdFusion  markup with valid SQL.

Let us know the result.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2015 0
Engaged ,
Oct 05, 2015

Copy link to clipboard

Copied

Thanks! Is there a way to see the valid SQL markup after ColdFusion as converted?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2015 0
Advocate ,
Oct 05, 2015

Copy link to clipboard

Copied

It is possible, but that's going about the problem the wrong way.

You should create a working query first and then use that query in ColdFusion.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2015 0
Engaged ,
Oct 06, 2015

Copy link to clipboard

Copied

Actually this was part of an ADF apps (CommonSpot CMS) created by a third party and I want to add another functionality to the function. This function has parameters that I am not sure where it came from; thus, I was wondering if I can dump the query on the browser to analyze the query to see if it make sense.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

2Charlie wrote:

This function has parameters that I am not sure where it came from;

That's all well and good, but it's best to troubleshoot one thing at a time. Let's first make sure the query is sane before troubleshooting parameters.

Remove all the ColdFusion comments from the query text, replace the two application variables with integers and replace the cfqueryParam tag with a small list of integers. That's all you need to do to get a syntactically correct query. Does that query succeed in your SQL query tool without error? Note that a zero record count is still a success.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

There in an optional result parameter in the cfquery tag. This will contain the SQL statement that was executed.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

Steve Sommers wrote:

There in an optional result parameter in the cfquery tag. This will contain the SQL statement that was executed.

That's only helpful if the query succeeds. It is also possible to see the text of the query that threw an exception, but I cannot stress enough that if one is not starting with a valid query the problems are just compounded.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Engaged ,
Oct 06, 2015

Copy link to clipboard

Copied

Here's my attempted query.

select p.ID, s.subsiteURL, p.filename, p.DateContentLastModified, u.publicFileName, p.uploaded as bIsUploaded, s.uploadURL,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = '4,6,41,262,39,241,165,26,28,166,37' and dfv.versionState = 2 ) as nPriority,

  (select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = '4,6,41,262,39,241,165,26,28,166,37' and dfv.versionState = 2 ) as sFrequency

  from SitePages p

  join SubSites s

  on p.SubSiteID = s.id

  left join UploadedDocs u

  on p.id = u.pageID

  where (p.expDate is null

  OR p.expDate > getdate())

  and p.pageType = 0

  and p.approvalStatus <> 1

  and s.siteState <> 0

  and s.subsiteURL NOT LIKE '/training%'

  and s.ID not in ('4','6','41','262','39','241','165','26','28','166','37')

  order by p.ID desc

And I kept getting this error when I run in MS SQL Server Management Studio.

Msg 245, Level 16, State 1, Line 18

Conversion failed when converting the varchar value '4,6,41,262,39,241,165,26,28,166,37' to data type int.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

I'm guessing the dfv.fieldID is a single value, so put one number without quotes.

In the number list, remove the quotes around the numbers.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

Or simply remove the single quotes in the list of numbers:

and s.ID not in (4,6,41,262,39,241,165,26,28,166,37)

(sorry, didn't read - I only saw the "one number" portion)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Engaged ,
Oct 06, 2015

Copy link to clipboard

Copied

I got the error below when I triedd: s.ID not in (4,6,41,262,39,241,165,26,28,166,37)

Msg 512, Level 16, State 1, Line 18

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
Advocate ,
Oct 06, 2015

Copy link to clipboard

Copied

Well you're proving Eddies point, there are multiple issues with your SQL statement and that should have been where to start. You're fighting with SQL coding issues here, not CF coding issues. The where clauses in the subqueries need to be adjusted to reference a single value fieldId value, most likely referencing the current row value that you are on -- but I'm not sure this is what your expecting in the results.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0
2Charlie LATEST
Engaged ,
Oct 06, 2015

Copy link to clipboard

Copied

I used a single value and the query executes fine. It gave me the expected result. However, when it builds the sitemap.xml, it's not. I'll diagnosed more on the CommonSpot ADF app. Thank you all for your help.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 06, 2015 0