Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Unable to use query in table filter

info's profile image info posted 7 years ago in Running SQL scripts Permalink
I failed to run the following query in "tbl_access_pc" while it works fine in "Query" tab!

May you please help me to modify the following query to be able to use it in table filter

SELECT * FROM 
(
SELECT 
access_id,
access_db,
access_company,
access_br,
access_desc,
access_pc,
access_mac,
access_data,
DATE_ADD(access_last, INTERVAL 8 HOUR) access_time,
TIMEDIFF(NOW(),access_last) time_diff,
DATE_ADD(NOW(), INTERVAL 8 HOUR) time_now, 
access_last,
access_enabled,
access_live,
access_new,
access_error_time, 
access_error_last,
access_note
FROM 
tbl_access_pc
WHERE 
access_db<>5 and 
access_db<>-1 
ORDER BY access_last DESC) tmp
GROUP BY access_db,access_br
HAVING access_last < DATE_SUB(NOW(),INTERVAL 60 MINUTE)
kalvaro's profile image kalvaro posted 7 years ago Permalink
A filter needs to be an expression for the WHERE clause, not a full fledge query.

How exactly could you use a data grid as filter? Can you share an example?
info's profile image info posted 7 years ago Permalink

How exactly could you use a data grid as filter?


I want to filter the below table with the following sql:

SELECT * FROM 
(
SELECT 
access_id,
access_db,
access_company,
access_br,
access_desc,
access_pc,
access_mac,
access_data,
DATE_ADD(access_last, INTERVAL 8 HOUR) access_time,
TIMEDIFF(NOW(),access_last) time_diff,
DATE_ADD(NOW(), INTERVAL 8 HOUR) time_now, 
access_last,
access_enabled,
access_live,
access_new,
access_error_time, 
access_error_last,
access_note
FROM 
tbl_access_pc
WHERE 
access_db<>5 and 
access_db<>-1 
ORDER BY access_last DESC) tmp
GROUP BY access_db,access_br
HAVING access_last < DATE_SUB(NOW(),INTERVAL 20 MINUTE)


Can you share an example?


The structure of "tbl_access_pc" table.

-- --------------------------------------------------------

-- Host:                        localhost

-- Server version:               5.5.42-cll - MySQL Community Server (GPL)

-- Server OS:                    Linux

-- HeidiSQL Version:             9.3.0.4984

-- --------------------------------------------------------


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping database structure for database_config

CREATE DATABASE IF NOT EXISTS `database_config` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `database_config`;
-- Dumping structure for table database_config.tbl_access_pc

CREATE TABLE IF NOT EXISTS `tbl_access_pc` (
`access_id` int(11) NOT NULL AUTO_INCREMENT,
`access_db` int(11) NOT NULL DEFAULT '0',
`access_company` varchar(100) DEFAULT '',
`access_br` int(11) NOT NULL DEFAULT '0',
`access_desc` varchar(100) DEFAULT '',
`access_pc` varchar(100) DEFAULT '',
`access_mac` varchar(100) NOT NULL DEFAULT '',
`access_data` varchar(100) NOT NULL DEFAULT '',
`access_last` datetime DEFAULT NULL,
`access_enabled` tinyint(1) DEFAULT '0',
`access_live` tinyint(1) DEFAULT '0',
`access_new` tinyint(1) DEFAULT '1',
`access_error_time` datetime DEFAULT NULL,
`access_error_last` varchar(255) DEFAULT NULL,
`access_note` varchar(255) DEFAULT NULL,
`access_version` varchar(10) DEFAULT NULL,
PRIMARY KEY (`access_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- Data exporting was unselected.

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
kalvaro's profile image kalvaro posted 7 years ago Permalink
:-?

I think what you mean is that you want to use arbitrary SQL code to obtain a data grid of arbitrary rows and columns. That's exactly what the Query tab is for—use the Query tab!

Trying to use filters in the Data tab to run random SQL makes as little sense as trying to run queries from the "Comment" box of Session Manager.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.