Unable to use query in table filter

info posted 4 years ago in Running SQL scripts
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 posted 4 years ago
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 posted 4 years ago

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 posted 4 years ago
:-?

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.