官术网_书友最值得收藏!

Generating constants with SELECT statements

During the development of a BI project, sometimes we need to use values which do not necessarily come from tables; these can be constants such as Data refreshed at or Today. The logical tables come in very handy for this purpose.

Getting ready

You can find the SELECT statement for this recipe in the companion code file.

How to do it...

To create a logical table that returns the current date as a constant, do the following:

  1. Go to the Schema Objects | Tables folder and right-click on an empty space in the right pane. From the context menu, choose New | Logical Table.
  2. In the SQL statement text area, paste this sentence:
    select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) DateValue
    
  3. Add one Column Object and name it DateValue, and select Date from Data Type.
  4. Click on Save and Close and name it Today.
  5. Update the schema.

How it works...

We created a very simple table with one row and one column. This table will always return the current date at midnight; it is useful to filter records based on moving dates, or simply to display it in a report header. Moreover, since the table has only one row, we can join it anywhere without creating dangerous Cartesian products. Even with a CROSS JOIN, it will always multiply the results by one.

There's more...

In SQL Server constants are generated with a SELECT statement without the FROM clause. If you want to display Eat at Joe's, it's as simple as:

select "Eat at Joe's"

(Sorry, blinking font not included)

In other RDBMS, you may find a different syntax: in Oracle, for example, the current date at midnight can be obtained with the sentence:

SELECT TRUNC(SYSDATE) DateValue FROM DUAL;

And the logical table would work just the same.

See also

  • The Attribute forms – ID and DESC recipe in Chapter 3, Schema Objects – Attributes
主站蜘蛛池模板: 宿松县| 容城县| 台北市| 秦皇岛市| 阿图什市| 漠河县| 沙雅县| 滦南县| 海丰县| 伊吾县| 太康县| 青浦区| 福泉市| 多伦县| 莱芜市| 株洲县| 潼关县| 清水河县| 孝义市| 青田县| 洛阳市| 张掖市| 桐乡市| 威信县| 曲水县| 专栏| 屯留县| 桐乡市| 通州市| 临猗县| 河北区| 门头沟区| 陇南市| 呼玛县| 梅州市| 克东县| 阳春市| 黎平县| 木兰县| 马尔康县| 宜兰县|