SELECT DISTINCT not working in Grid

Scriptcase 7 Bugs
#1

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

SELECT DISTINCT
EID
FROM
filter_inspections

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?

#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???
regards

#4

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

#5

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 …

#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, usuarios.email 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

#7

Hi Lisa,

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

Aquiles

#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.

  2. 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.

Greetings.

#9

Hello,

I will contact our bugs team regarding this issue.

regards,
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.