13 Replies Latest reply on Oct 6, 2015 12:15 PM by 2Charlie

    CFFunction Select Where clause not working correctly

    2Charlie Level 1

      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>