DNN Forums

Ask questions about your website to get help learning DNN and help resolve issues.
  • Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Development and Extensions SQL and SQL Server

Help with SQL Query

 5 Replies
 4 Subscribed to this topic
 19 Subscribed to this forum
Sort:
Author
Messages
amurraygbhu
Growing Member Send Private Message
Posts: 30
Growing Member
8/9/2022 8:39 AM

I am using DNN Platform and I need help with an SQL query I am trying to run using the DNN SQL Console.

I need to change a small block of code that is used in several locations throughout the website. So I am trying to write an SQL query that searches the htmltext table for modules that still contain the block of code in the content field as I have changes it in a few locations already. Here is the query

Select moduleid from htmltext where content like 'codeblock'

The problem is that the table htmltext keeps the historical versions of the module. So if I already changed it in module 600 and module 600 has 5 total versions (versions 1-5) it will show module 600 4 times (version 1-4). None of these are the current version (version 5) of the module though.

So I need to change the query to only search the latest version of the modules in the htmltext table.

Anyone know how to change the query to accomplish this?

James Clarkson
Growing Member Send Private Message
Posts: 82
Growing Member
8/9/2022 8:45 AM
try GROUP BY to group by the module id and then HAVING version=MAX(version)
or something along those lines - I would check this for you if I had more time just now - hopefully this gets you on the right lines
Sebastian Leupold
Veteran Member Send Private Message
Posts: 546
Veteran Member
8/9/2022 8:47 AM

Select T.moduleID
FROM HTMLText T
JOIN (SELECT ModuleID, max(versionID) as latestVersion FROM HTMLText GROUP BY ModuleID) AS S ON T.ModuleID = S.ModuleID AND T.Version = S.Version
WHERE T.content like 'codeblock'

Michael Tobisch
Veteran Member Send Private Message
Posts: 1182
Veteran Member
8/9/2022 10:27 AM

In addition to Sebastian (and correcting one or two typos in his script): If you use the SQL Console in DNN, you might need the "DNN-SQL-Syntax", which is then:

SELECT T.ModuleID
FROM {databaseOwner}{objectQualifier}HtmlText T
INNER JOIN (SELECT ModuleID, MAX([Version]) as LatestVersion FROM {databaseOwner}{objectQualifier}HtmlText GROUP BY ModuleID) AS S ON T.ModuleID = S.ModuleID AND T.Version = S.LatestVersion
WHERE T.Content LIKE '%codeblock%'

Please keep in mind that the content is stored HTML-encoded - so replace < by <, > by >, " by " etc.

Happy DNNing!
Michael

 

Michael Tobisch
DNN★MVP

DNN Connect
1
liked by Members
amurraygbhu
Growing Member Send Private Message
Posts: 30
Growing Member
8/9/2022 10:41 AM

Great thanks for the help. Worked great.

amurraygbhu
Growing Member Send Private Message
Posts: 30
Growing Member
5/5/2023 8:33 AM
I was able to get the query I needed with the help provided above. Works great now.

These Forums are dedicated to the discussion of DNN Platform.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. If you have (suspected) security issues, please DO NOT post them in the forums but instead follow the official DNN security policy. 
  2. No Advertising. This includes the promotion of commercial and non-commercial products or services which are not directly related to DNN.
  3. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  4. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  5. No Flaming or Trolling.
  6. No Profanity, Racism, or Prejudice.
  7. Site Moderators have the final word on approving / removing a thread or post or comment.
  8. English language posting only, please.

Would you like to help us?

Awesome! Simply post in the forums using the link below and we'll get you started.

Get Involved