4 Replies Latest reply on Jan 4, 2008 10:05 AM by (shane_mosier)

    ERROR: Filters in dynamic lists doens't work with alias from QuB?

      Hi, i've created a dynamic list with filters using a rescordset constructed in the QuB! Everything works fine but when i try to filter a field called SERVICO_ID_1 (Alias for servico.SERVICO_ID) it gives me this error!"Unknown column 'SERVICO_ID_1' in 'where clause'". This means it can't find that field in the DB right? but why?

      Here is the query!

      $query_servicos = sprintf("SELECT historico.IDHIST, servico.SERVICO_ID AS SERVICO_ID_1, historico.SERVICO_ID, historico.ESTADO, servico.CLIENTE_ID, servico.NOME_REM, servico.LOCALIDADE_REM, servico.NOME_DEST, servico.LOCALIDADE_DEST, servico.DOC_REF_CLI, servico.DATA_SERV FROM (servico LEFT JOIN historico ON historico.SERVICO_ID=servico.SERVICO_ID) WHERE servico.CLIENTE_ID=%s AND {$NXTFilter_servicos} ORDER BY {$NXTSort_servicos} ", GetSQLValueString($KTColParam1_servicos, "int"));

      Please help, this is very important!!
      Thanks in advance!
        • 1. Re: ERROR: Filters in dynamic lists doens't work with alias from QuB?
          Level 1
          Andre,

          Is there an important reason your are using an alias for that field? Have you tried it without?

          I'm not a pro at complex querys like you have here, but from looking at this query I don't see why you need an alias.

          From what I know, alias are good for the following reasons:

          Avoiding any reserved (used by MySQL) words.
          Allowing Multiple Joins to the same table
          Allowing Self-Joins
          Assigning the result of MySQL function to a temporary column name.

          Doing a little googling got me this page:

          http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

          According to that page:

          "Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined."

          So I think you'll have to skip using the alias and try something else.

          Shane
          • 2. Re: ERROR: Filters in dynamic lists doens't work with alias from QuB?
            Level 1
            Thanks for your help Shane! I'm far away from beeing a pro too so i'm using QueryBuilder...and i have to connect two fields (SERVICO_ID) from two tables...and when i do that QuB automaticaly creats an alias for on of them!! How can i not use alias? :o
            • 3. Re: ERROR: Filters in dynamic lists doens't work with alias from QuB?
              glensbo Level 1
              Andre
              I have used the following query to get data from to tables - following this model. Will that be of any help?

              $query_alpharmaActive = "SELECT alph_authuser.uname, alph_authuser.passwd, alph_signup.fname, alph_signup.lname, alph_signup.email, alph_signup.country, alph_signup.zipcode, alph_authuser.id FROM alph_authuser, alph_signup WHERE alph_signup.id >'0' AND alph_authuser.status='active' AND

              alph_authuser.id=alph_signup.id

              ORDER BY alph_signup.zipcode";

              Regards
              • 4. Re: ERROR: Filters in dynamic lists doens't work with alias from QuB?
                Level 1
                Andre,

                I think you would need to manually edit the query. Try this and see if it works.

                Change the following:

                servico.SERVICO_ID AS SERVICO_ID_1

                to:

                servico.SERVICO_ID

                So just delete " AS SERVICO_ID_1" and see what that does.

                I think the query builder is adding the alias so that you don't confuse "servico.SERVICO_ID" and "historico.SERVICO_ID". Because they are from different tables though there won't be any issue.

                Try that and see if it works.

                Shane