MySQL Retrieval Error

Databases
1

The below code finds duplicate records in a table for a web project. Not sure if its an error but thought I would ask anyway, the below SQL code works in Navicat;

SELECT
ID,
Entered,
Type,
Rep,
trim(	upper( Lastname )),
Time_Minutes,
Notes,
Date_Advice,
Own_Time,
Imported,
COUNT( Date_Advice ) AS Entries FROM
database WHERE
 IDCode = 101 GROUP BY
Rep,	Lastname,	Date_Advice HAVING
COUNT( Rep ) > 1 
AND COUNT( Lastname ) > 1 
AND COUNT( Date_Advice ) > 1

The same code does not work in XOJO, it does not return a recordset, unless the query is changed to rename the ā€˜Trimmed Lastname fieldā€™ to something else, like that below;

SELECT
ID,
Entered,
Type,
Rep,
trim(upper( Lastname )) as NameLast,
Time_Minutes,
Notes,
Date_Advice,
Own_Time,
Imported,
COUNT( Date_Advice ) AS Entries FROM
database WHERE IDCode = 101 GROUP BY	Rep,
Lastname,	Date_Advice HAVING	COUNT( Rep ) > 1 
AND COUNT( Lastname ) > 1 
AND COUNT( Date_Advice ) > 1

Is it just that Navicat is more forgiving, or is there a small bug in the XOJO MySQL plugin?

2

No code attached :copyright: :registered:

3

I was still editing - going to take a little while to get used to new forum :slight_smile:

4

Understandable :+1:

5

you get no recordset at all ?
i would expect a recordset with a column with a weird name for the unnamed ā€˜Trimmed Lastname fieldā€™

6

I shall check again in that case, as its stored in a session iā€™ll burrow around the code :slight_smile:

7

Norman, you are correct, it does return a recordset, it throws an error trying to display it as it doesnā€™t get the right fieldname in my code as it thinks the fieldname is that which is returned below;

trim(upper(Lastname))

I assume from this that its not a bug, but in cases using MySQL functions you must always use an alias such as below;

trim(upper(Lastname)) as NameLast

8

If you expect to use a column name to access the data then you should use the ā€œas AliasNameā€ form in the sql and then you can use that alias name to access the field

9

Why not trim the properties before hand then set to uppercase then put them in the query?

Much cleaner query, easier to debug cause of the properties shown in the debugger.

1 Like
10

Then you have to transform them on out put to mailing label, report, etc etc etc
And some you cant just apply Title case to ad get right

Like Greg Oā€™Lones last name :stuck_out_tongue:

11

Thats what I actually do now, but having to deal with some legacy data (which I could clean by hand I guess).

12

You could use IdxField to get the data without using an alias, but then your code becomes more fragile. I always use the alias.