<< Click to Display Table of Contents >> Navigation:  Welcome to Maxim Help Articles > EXTENDX > ExtendXsch Star-Schema Builder

Range Expressions

 

 

 

Dates

 

Select transactions for the past 60 days.

Trandate >=curdate()-60

 

Current date is represented as curdate()

 

Date format is 'YYYY-MM-DD' ie, trandate between '2012-08-01' and '2012-08-31'

 

 

Star-Schema Builder

 

SQL Syntax

To get a full acctg period (201306) from year and period columns.

 

In the Expression column:

(cast (..year as integer) * 100 + ..period)

 

convert (x, sql_double)

convert (x, sql_integer)

cast (x as integer)

 

 

StockNbr stored as Alpha Account

convert (account,sql-integer.)

 

 

Dates

Don't use date or Date as a SQLName

 

Monthname(CPPCC_TranDate) returns January

Year (CPPCC_TranDate) returns 2013

Month (CPPC_TranDate) returns 1

Week (Trandate) returns 1-52

Concat (monthname (date), cast (Year (date) as Char (4))))

 

 

AcctgPeriod + ShortName

convert (period, sql-chr) +' ' + GLPER_shortname

 

 

Table / View Structure does not match

Must clear Table Setup

If a column is added as Output and is not added to the bottom, or an Output column is deleted that is not from the bottom, the tables's ExtendX metadata must be dropped and reselected.

If you change a Column Name, add or delete columns then you have to update ExtendX Metadata - table must be dropped and reselected.

 

 

Sub Query

If you need to test if a certain transaction exists use a subquery as an Expression:           criteria = True or Not = True

Using subqueries to select a value

It seems that the selected column type needs to be converted.  If you want decimals use sql_double, if you want an integer use sql_integer.

 

(Select Top 1 convert (i.IVTRN_UnitCost, sql_double) As UnitCost From IVTRN_InventoryTrans i where i.IVTRN_Company = l.IVLOC_Company And i.IVTRN_StockNbr = I.IVLOC_StockNbr And i.IVTRN_Location = l.IVLOC_Location And i.IVTRN_SupplierLoc = I.IVLOC_SupplierLoc)

 

 

String Concatenation

rtrim (Field1) + ', ' + rtrim (Field2) + ', ' + rtrim (Field3) . . . .

 

Use if (Field4 <> ", ', ' , ") to condition