• Login
  • Register

DNN Forums

Ask questions about your website to get help learning DNN and help resolve issues.
PrevPrev
NextNext

Help with SQL Query

You are not authorized to post a reply.
Forums > SQL and SQL Server
Sort:

amurraygbhu



New Around Here


Posts:18
New Around Here

8/9/2022 7: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


    Posts:48
    Growing Member

    8/9/2022 7: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


      Posts:530
      Veteran Member

      8/9/2022 7: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


        Posts:976
        Veteran Member

        8/9/2022 9: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 &lt;, > by &gt;, " by &quot; etc.

          Happy DNNing!
          Michael

           

          Michael Tobisch
          DNN★MVP

          dnnWerk Austria
          DNN Connect

          amurraygbhu



          New Around Here


          Posts:18
          New Around Here

          8/9/2022 9:41 AM

            Great thanks for the help. Worked great.


            amurraygbhu



            New Around Here


            Posts:18
            New Around Here

            5/5/2023 7:33 AM
              I was able to get the query I needed with the help provided above. Works great now.
              You are not authorized to post a reply.

              These Forums are dedicated to 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 send an email to [email protected]
              2. No Advertising. This includes 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