The Dummy table in SQL is a special, often overlooked feature that can be quite useful in various scenarios. It is a handy tool for executing simple queries and performing calculations without accessing actual data.
In SuiteQL, the Dummy table is commonly referred to as a "DUAL" table. It serves a unique purpose: when you need to retrieve a calculation or the result of a function without querying a specific data table. Its existence allows users to run efficient queries without needing a substantial dataset, making it a valuable tool in the SuiteQL environment.
Here’s a simple example of how to use the DUAL table in a query:
SELECT SYSDATE as Date,TO_CHAR(SYSDATE, 'HH24:MI:SS') AS CURRENT_TIME FROM DUAL;
This query retrieves the current date and time from the database, with the date and time separated into two columns using the DUAL table.
This query returns:
- SYSDATE AS Date: This part of the query retrieves the current date and time and aliases it as "Date." The SYSDATE function returns the system's current date and time.
- TO_CHAR(SYSDATE, 'HH24:MI:SS') AS CURRENT_TIME: This part converts the SYSDATE value into a string format that only displays the time in a 24-hour format. The alias for this column is "CURRENT_TIME."
- FROM DUAL: This indicates that the query is selecting data from the DUAL table, which is a special one-row table used for such operations in SuiteQL.
The output will look like this (note: the output will depend on when you execute the query):
Date | CURRENT_TIME |
|---|
10/26/2024 | 01:23:52 |
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
How are you using DUAL Table in SuiteQL in your business? Share them in the NetSuite Admin Corner.