Version 01

Works with the keyword dal.

Version 03

Understand different keywords – e.g. for the use of parameters.

Aim

An SQL Query allows importing data from a data base of your choice (e.g. Access) or files (Excel, Text, CSV) available for the server.

Screenshot and main steps
STEP 1 - How to access data-base tables
STEP 2 - How to select data from such a table?

Critical aspects

• This feature gives access to all the data within the schema. The corresponding passwords should be handled properly.
• Please check whether external access to the website is possible. This operator could be an door for hackers, trying to access the data in the database
• Queries can cause substantial load for the database. Please check whether it is acceptable, that potentially inefficient queries are sent to the database or involve an expert.

Further aspects

• Since this is an import operation, it can only be applied to empty data nodes.
• The TIS-Server must be connected to the source via the operating system (e.g. via shared directories). If the data source is a file (e.g. Excel, Access,...) the user connected to the webserver must be able to access the file or have the necessary rights.
• MS-SQL and Oracle differ in their SQL please consider the different SQL-keywords (especially when moving from one Server to another.
• You can use Parameters and access TIS-Tables directly – see the [TIS]Board-Manual for a detailed description.

The easy way

If you want to connect to a TIS-Table or a table within the schema [TIS]Editor runs within – which is the most probable case – then it is enough to select the field data source and enter dal as information on connection.

Enter username and password

Username and password for the connection to the data source. These fields have to be filled in only if the data source requires authentication with username and password (e.g. databases typically do but files typically don't).
If you use Windows authentication then user connected to the WEB-Server is used for authentication (not the account on the client PC where session runs).

Select the Data Source

Choose from the different connection methods<br>

• MS-SQL-Server 2000
• MS-SQL-Server 2005
• Oracle (as of version 9i or higher)
• PostgresQL (version 9.4 or higher)
• ODBC-data source... ''e.g. Access, Excel, txt, or CSV files

OPTIONAL fetch additional information

• DSN: Displays data sources available for the method chosen (data bases, ODBC-sources, etc.)
• OBJECTS: Displays objects available for the method chosen (data bases, etc.)

Define the Connection String (if entering dal is not sufficient)

Check Table names and Column Name

Background

The SQL queries are sent directly to the data base and (provided you have the necessary rights) and applied. 'All SQL-language elements which are supported by the selected data source are allowed.
E.g.
select * from your_tablename

Define the max. query time

Define, when the operator shall stop the query (if not successfully completed). This avoids erroneous queries paralysing the server.

Notes including how to use Parameters in Tablename and where- statements

You can use Parameters and access TIS-Tables directly – see the [TIS]Board-Manual for a detailed description.

• E.g. accesing TIS-Tables directly - select * from #XI.TISTable('your folder name','your table name')#
• Or via Parameter select * from #XI.TISTable(#XI.TISPar('parCPath')#,#XI.TISPar('parCName')#)# where C1 >= trunc(current_date) order by C1 Select * from #XI.TISTable('Demand\All Cargo', 'my Tablename')#
• E.g. using Parameters select projectid, projectname from project where projectid=#XI.TISPar('Parameter_ID')#

Getting database independent datetime

Please consider #XI.SysDate()# to get the current Date&Time as it is independent from the database where your query will run.

Examples where you build up the SQL Statement with parameters usinf TISParSQL

Select 
 #XI.TISParSQL('STAT_Descr_MasterData_VAR1')# as VAR1, Count(*) as Nr 
 from #XI.TISTable(#XI.TISPar('TAB_Pfad')#,#XI.TISPar('TAB_Stamm')#)# 
 GROUP BY #XI.TISParSQL('STAT_Descr_MasterData_VAR1')# 
 ORDER BY #XI.TISParSQL('STAT_Descr_MasterData_VAR1')#

Good examples can be found at: http://www.w3schools.com/Sql/default.asp

Find texts

asd where column_A like '%1C%'

% is used as wild-Card for strings of any length

_ is used as wild_card for letters
if one wants to find _ then Txt LIKE '_1C%' ESCAPE '\'

Compare dates

From < to_date('20091201000000', 'YYYYMMDDHH24MISS')
From >= to_timestamp('200904010000', 'yyyymmddhh24mi')

Convert

TO_NUMBER(C3,'99999', 'NLS_NUMERIC_CHARACTERS = '',.-''')
UPPER('aabb')

Comments within

/* this is treated as comment */

Filter for NULL

Time_Col is not null

Count

Count(*)

Not in 2^nd table

select * from Table_A where Key_col not in (select Key-col from Table_B)

Group by & order by

select Divisionname, Avg(Prio), Count(*) from XY
group by Divisionname, IncType
order by Divisionname

Select only different names

SELECT DISTINCT C0, C1 FROM Table

Calculate Sums and Mittelwert (avg)

SELECT Sum(C0) FROM Table
SELECT Avg(C0) FROM table
If AVG doesn't work, type conversion often helps:
Select cast(avg(C0) as float(53)) FROM table

Select and count different values

SELECT C0 as Bereich, C2 as Monat, count(*) as Anzahl From Table GROUP BY C0, C2 ORDER BY C0,C2

Looking for many values

C1 in ('Einzeldaten', 'Summendaten') AND C2 in ('2010', '2011'AND C5 in ('Überzahlung')

Empty strings

If you need to search for empty strings in a TIS-Table use the following statement:
c0 = ''

Select data from different tables

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2
If duplicated records should be allowed use the keyword ALL:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2

Join Data from two Sources

SELECT t.title_id, t.title, s.qty FROM titles AS t, sales AS s WHERE t.title_id *= s.title_id
From the table "titles AS" the columns title_id and title are selected. From table "sales AS" the column "qty" is selected, where the columns "title" in both tables match.

Area

Oracle

MS-SQL

PostgresQL 

Get the current Date & Time

Please use instead the following command which is provided by the [TIS]
#XI.SysDate()#

Please use instead the following command
which is provided by the [TIS]
#XI.SysDate()#

 #XI.SysDate()#

Convert

TO_NUMBER(C3,'99999', 'NLS_NUMERIC_CHARACTERS = '',.-''')
TO_CHAR(DATE_VAL, 'YYYY/MM/DD')

Col1 = convert(float, Col1)
cast (INCCLOCK as date)

(Col1)::integer

(Col1)::text

(Col1)::timestamp

Cast (INCLOCK as timestamp)
https://www.postgresql.org/docs/9.4/static/datatype.html

Select first records

SELECT * FROM data WHERE ROWNUM <= 100

SELECT * FROM data TOP 100

 Select * From Data limit 100

Get substring from string

SUBSTR

SUBSTRING

 SUBSTRING

Compare Dates

From < to_date('20091201000000', 'YYYYMMDDHH24MISS')
From >= to_timestamp('200904010000', 'yyyymmddhh24mi')
TO_CHAR(DATE_VAL, 'YYYY/MM/DD')
c0 >= #XI.TISPar('parFrom')#

C0 >= convert(datetime,
'#XI.TISParSQL('DATA_SPY Von')#',104) *)
*) 104 is the code for data format

 https://www.postgresql.org/docs/9.4/static/functions-datetime.html

Calculating Seconds

(c6-c5)*24*60*60

EXTRACT(EPOCH from (C6 - C5)) as 

Datentyp des Ergebnisses einer Rechnung sehen 

 pg_typeof( EXTRACT(EPOCH from (C6 - C5)) )::text

Zahlem drekt aus Textdarstellung lesen 

cast(replace(replace('1.234,00','.',''),',','.') as float8

Getting Started

• The necessary drivers have to be installed (e.g. Text Driver)
• A dataset name (DSN) has to be defined that is then used in the import
  
  • The name should without blanks.
  • File must be reached from TIS server.
  • If several files have the same structure then it is enough to define one
  • It is much faster to import characters than floating numbers or integers (convert later!)

Example for Import Definition

Example for Creating a Statement

Select top 100 Firma, Beruf from PEP_Dateiname.txt

Note: blanks in filenames may cause difficulties

File Format for Text Files

ODBC works with out problems, if the file has a UTF-8 format and has no real UTF 8 symbols.
If there are real UTF 8 symbols, the file needs to be saved as ANSI format.

Note

Check if the right ODBC driver is installed. Corresponding your operating system the driver must be either 32 or 64 bit.

Task

Steps

Notes

How to understand the structure of a database

Integrated Security=False;User ID=NAME;Password=xxxxxxxxxxxx
ORACLE:
select table_name, column_name, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, column_id from user_tab_cols
order by table_name,column_id
List all tables and Columns of a database

 Dealing with formats in Postgresql

Task

Steps

Notes

Avoiding empty Tables

Task

Steps

Notes

System's database

XimesDAL.SQLImportConnectionInternal - Specified cast is not valid.

some data types cannot be cast

• date
• bigint

cast to a compatible data type

• datetime
• int, double, string

...

...

...

...