SELECT statement without the FROM clause

When using the WITH clause, I accidentally found it to execute even though the FROM clause was omitted from the SELECT statement. Of course, only constants are output. It's the same as SQL Server, PostgreSQL and MySQL/MariaDB. (Is there any more?) It is available from 14.10.xC1 version. However, I was unable to find anything specified in the Knowledge Center for this feature. [informix@db2 ~]$ onstat - IBM Informix Dynamic Server Version 14.10.FC1DE -- On-Line -- Up 00:02:48 -- 599688 Kbytes [informix@db2 ~]$ dbaccess stores_demo -Database selected. > select 1; (constant) 1 1 row(s) retrieved. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------ #Informix

Yes. New feature for compatibility with the others.

Hi I found this in the knowledge center. " Every SELECT statement requires the FROM clause, whether or not any data source is required. " https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0160.htm#ids_sqs_0160 As a result, this feature was not included in the documentation. Good to know that it has been introduced. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Gábor : Yes. Perhaps the documentation hasn't been updated. Thanks for sharing the link. I still used this function usefully only in WITH statement, but I will think about other uses such as in routine...etc. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Typically this is used like the Oracle construct: select 7 from dual;Or the Informix constructs: select 7 from sysmaster:sysdual; select 7 from systables where tabid = 1;Note that the new construct, without a FROM clause, is faster than the other two options in Informix. Witness these runs in sqlcmd in benchmark mode. I have highlighted the runtimes in RED: SQL[27]: select 7 from systables where tabid = 1; 2020-03-17 11:18:12 + select 7 from systables where tabid = 1; 7 Time: 0.002461 SQL[28]: select 7 from sysmaster:sysdual; 2020-03-17 11:18:20 + select 7 from sysmaster:sysdual; 7 Time: 0.001722 SQL[29]: select 7; 2020-03-17 11:18:24 + select 7; 7 Time: 0.000245 Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

So this is (probably) the quickest way to get current time, which is useful select current; 2020-03-17 12:28:25.000 Cheers Paul > Typically this is used like the Oracle construct: > > select 7 from dual;> Or the Informix constructs: > > select 7 from sysmaster:sysdual; > select 7 from systables where tabid = 1;> Note that the new construct, without a FROM clause, is faster than the > other two options in Informix. Witness these runs in sqlcmd in benchmark > mode. I have highlighted the runtimes in RED:SQL[27]: select 7 from > systables where tabid = 1; > 2020-03-17 11:18:12 > + select 7 from systables where tabid = 1; > 7 > Time: 0.002461 > SQL[28]: select 7 from sysmaster:sysdual; > 2020-03-17 11:18:20 > + select 7 from sysmaster:sysdual; > 7 > Time: 0.001722 > SQL[29]: select 7; > 2020-03-17 11:18:24 > + select 7; > 7 > Time: 0.000245 > > Art > Art S. Kagel, President and Principal ConsultantASK Database > Managementwww.askdbmgt.com #Informix

You can also use the construct for more interesting and useful constants like: Current time: > select current; (expression) 2020-03-17 11:27:25.983 1 row(s) retrieved. Translating a partnum from an onstat report: > select dbinfo( 'dbspace', '0x00212345'); (constant) plogdbs 1 row(s) retrieved. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Yep, really giving examples of why this format is useful Cheers Paul > You can also use the construct for more interesting and useful constants > like: > Current time: >> select current; > > (expression) > > 2020-03-17 11:27:25.983 > > 1 row(s) retrieved. > > Translating a partnum from an onstat report: >> select dbinfo( 'dbspace', '0x00212345'); > > (constant) > > plogdbs > > 1 row(s) retrieved. > > ArtArt S. Kagel, President and Principal ConsultantASK Database > Managementwww.askdbmgt.com #Informix

It's simple to use and its performance is even better than before! When using sysdual or systables, the number of isreads in the sysptprof and syssesprof tables increases. However, when the SELECT statement without the from clause is executed, there is no change. After enabling SQLTRACE, I compared the use of sysdual with a select statement without the from clause with the 'onstat -g his' command. Statement # 22: @ 0x4a2f20f0 Database: 0x100004 Statement text: select currentIterator/Explain ================ ID Left Right Sender Next Est Cost Est Rows Num Rows Partnum Type 1 0 0 0 0 1 1 1 0 Coll Scan Statement information: Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ 286 1001 SELECT 01:09:18 0.0000 9f7e7f5 0 Statement Statistics: Page Buffer Read Buffer Page Buffer Write Read Read % Cache IDX Read Write Write % Cache 0 0 0.00 0 0 0 0.00 Lock Lock LK Wait Log Num Disk Memory Requests Waits Time (S) Space Sorts Sorts Sorts 0 0 0.0000 0.000 B 0 0 0 Total Total Avg Max Avg I/O Wait Avg Rows Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec 1 0.0000 0.0000 0.0000 0.000000 0.000000 22702.0926 Estimated Estimated Actual SQL ISAM Isolation SQL Cost Rows Rows Error Error Level Memory 1 1 1 0 0 CR 12360 Statement # 36: @ 0x4a2f2160 Database: 0x100004 Statement text: select current from sysdualIterator/Explain ================ ID Left Right Sender Next Est Cost Est Rows Num Rows Partnum Type 1 0 0 0 0 2 1 1 92 Seq Scan Statement information: Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ 287 1001 SELECT 01:15:03 0.0001 9f7e84b 0 Statement Statistics: Page Buffer Read Buffer Page Buffer Write Read Read % Cache IDX Read Write Write % Cache 0 0 0.00 0 0 0 0.00 Lock Lock LK Wait Log Num Disk Memory Requests Waits Time (S) Space Sorts Sorts Sorts 0 0 0.0000 0.000 B 0 0 0 Total Total Avg Max Avg I/O Wait Avg Rows Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec 1 0.0001 0.0001 0.0001 0.000000 0.000000 14865.4207 Estimated Estimated Actual SQL ISAM Isolation SQL Cost Rows Rows Error Error Level Memory 2 1 1 0 0 CR 11240 Below is the query plan. QUERY: (OPTIMIZATION TIMESTAMP: 03-18-2020 01:43:45) ------ select currentEstimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.unnamed_tbl_0: COLLECTION SCAN Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 unnamed_tbl_0 type table rows_prod time ------------------------------ clscan t1 1 00:00.00 QUERY: (OPTIMIZATION TIMESTAMP: 03-18-2020 01:44:56) ------ select current from sysdualEstimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.sysdual: SEQUENTIAL SCAN Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 sysdual type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 1 1 1 00:00.00 2 ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------