5 Replies Latest reply on Jun 5, 2008 11:00 PM by Günter Schenk

    SQL query alias names errors with dynamic lists

      Hullo,

      Problem:
      - Made a query form book database. Have authors in separate table with unique ID's referring to them on book table.
      - Query fetches author name in "Surname, Firstname initial.Secondfirstname initial" format, --> like "Bukowski, H.C" and giving that combo alias name in SQL like "AS 'author'"...
      - Dynamic list works fine at start, but when ordered from detail table back to the mainlisting, it does make an error with SQL stating "unknown column 'author' in where clause"

      That must be because the script looks for "author" column from the original table yeah, but how can I solve this? Any ideas? Does this mean that Alias names for columns are not possible or is there something i've missed?

      Any ideas, suggestions?

      Thanks,

      KimmoK
        • 1. Re: SQL query alias names errors with dynamic lists
          Günter Schenk Level 4
          Hi Kimmo,

          ADDT´s dynamic lists can handle alias columns fine to my experience, but I reckon that you most probably applied the "CONCAT(...) AS author" query modification after having generated the list, and the list´s internal WHERE clause seems to have no clue of this alias column.

          You can get an idea about using alias columns in Dynamic Lists in my tutorial "Dynamic Lists: exploring the Filter Conditions": http://www.guenter-schenk.com/tutorials/tutorial.php?id=6

          Can you please post the list´s code on your server as text file (e.g. code.txt) and provide a link to this file ?

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: SQL query alias names errors with dynamic lists
            Level 1
            Günter,

            thanks for quick reply. I used CONCAT yeah. So that is the problem...

            Will look at the link you sent. I need to make new approach to the authorname then. If you have suggestions, let me know, And if you still think the code helps you in some way, I'll post it later.

            Thanks.

            Kimmo K
            • 3. Re: SQL query alias names errors with dynamic lists
              Level 1
              ADDT uses a relatively simplistic SQL syntax(no escaping) w/ PHP (I do no know about other languages).

              You will simply have to parse the input in the receiving code or add a layer of code to parse the input in between.

              You can also clean up the ADDT generated code by hand. But only do that if you are using version control as files are easily overwritten.

              some thoughts,

              Sean
              • 4. Re: SQL query alias names errors with dynamic lists
                Level 1
                Couple of more questions, Not having had time to delve into this any deeper yet.

                Sean: I'm not that experienced with PHP, so if you have time, please give me a bit more specific information.

                Günther: Do you think the generated list would understand the CONCAT modofication, if I started generating the list with the full query including the CONCAT. (I'm not fully sure if I added that afterwards or at the start). I shall test that possibility when I get time, but meanwhile, if you know the answer, please do post it.
                • 5. Re: SQL query alias names errors with dynamic lists
                  Günter Schenk Level 4
                  Hi Kimmo,.

                  ------
                  Do you think the generated list would understand the CONCAT modofication, if I started generating the list with the full query including the CONCAT
                  ------

                  what I know, is, that the List Wizard picks up alias column from existing queries just fine -- don´t know if it does the same with predefined CONCATs, so please give this a try and tell us how it worked.

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver