No announcement yet.

SELECT DISTINCT not working in Grid

  • Filter
  • Time
  • Show
Clear All
new posts

  • SELECT DISTINCT not working in Grid

    I'm using the following sql statement in a grid to try to display one instance of unique information:


    My table has eid's:

    1, 2, 3, 12, 12, 12, 15

    When I run this code in the sqlbuilder in scriptcase it shows me 1, 2, 3, 12, 15 which is correct. However, when the sql code is implemented in a grid I get 1, 2, 3, 12, 12, 12, 15. Is this a bug or am I doing something incorrect? I'm running Scriptcase v7.1, mySql 5.5.24 and Apache 2.2.22 on Windows 7. Thanks!!

  • #2
    Go to the grid application and go to application -> settings, scroll down to errorsettings and set script error and debug mode to on. Then regenerate and run your application. It will show the generated sql before running. Does it show the distinct here?
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development /


    • #3
      Hi I have the same problem, follow the steps above and get the following:

      1) Original SQL: 'Select DISTINCTRDW tipo_documento.Detalle, usuarios.NDocumento ...'

      2) Debug mode I get this: '(mysqlt): SELECT usuarios.NDocumento as usuarios_ndocumento, usuarios.Nombres usuarios_nombres as ...'

      It's like that omitted plaicacion DISTINCTRDW

      Any Idea???
      Leandro Carames


      • #4
        No case, try several options, these things are what one it discourage developing in sc, any trick to emulate the function DISTINCTROW????
        Leandro Carames


        • #5
          Originally posted by lcarames View Post
          1) Original SQL: 'Select DISTINCTRDW tipo_documento.Detalle, usuarios.NDocumento ...'
          Two problems:

          Your sql code is wrong. When you use DISTINCT it would only work correct, when you select one field ... (SELECT DISTINCT tipo_documento.Details FROM ...)

          Second: SC generated wrong code (my select: SELECT DISTINCT DebitorMatchcode FROM Zeitnachweis). Debug output is:

          (mysqlt): select count(*) from (select DISTINCT DebitorMatchcode from Zeitnachweis ) nm_sel_esp  
          (mysqlt): SELECT DebitorMatchcode from Zeitnachweis LIMIT 0,32
          @Lisa.C: Try using a view, that should work.

          @NetMake: fix this ...
          Best regards: - Reinhard -

          I use ScriptCase 8 Enterprise Edition, Version 8.(latest)


          • #6
            Sorry I did not understand well, you you say that is a problem of sc?
            If I execute this instruction in MySQL

            Select Distinct usuarios.NDocumento, usuarios.Nombres, usuarios.Apellidos

            or failing

            Select DISTINCTROW usuarios.NDocumento, usuarios.Nombres, usuarios.Apellidos

            Both work well and I repeated records show

            If I run the application in debug mode shows me the same instruction that

            (mysqlt): select count(*) from (select Distinct tipo_documento.Detalle, usuarios.IdUsuario, usuarios.NDocumento, usuarios.Nombres, usuarios.Apellidos, usuarios.FechaNacimiento, from inscripcion_alumnos Inner Join usuarios On inscripcion_alumnos.IdUsuario = usuarios.IdUsuario Inner Join tipo_documento On usuarios.IdTipoDoc = tipo_documento.idTipoDoc Inner Join centro_codigo_curso On inscripcion_alumnos.IdCtrCdCurso = centro_codigo_curso.IdCtrCdCurso Inner Join centro_habilitacion_modulos On centro_codigo_curso.IdCtrHbModulo = centro_habilitacion_modulos.IdCtrHbModulo ) nm_sel_esp

            Thank you for your kind reply
            Leandro Carames


            • #7
              Hi Lisa,

              you can make a view in Mysql with the dictinct word, then in Sc simply bind the grid to this view



              • #8
                I think I know where is the problem, the issue is as follows:

                1) If you create the Grid and at the time of creation, use Distinct or DISTINCTROW, sc works without problem.

                1) Now if you create the Grid and at the time of the creation NOT used or DISTINCTROW Distinct but then modified the SQL and add this sentence, sc do anything.

                Try this I say and you will see.

                Note to People Netmake: fix this.

                Leandro Carames


                • #9

                  I will contact our bugs team regarding this issue.

                  Bernhard Bernsmann


                  • #10
                    I usually do tests all the codes in SQL database itself when certify that the SQL code is correct I'm going to SC.