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

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()#

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

Get substring from string

SUBSTR

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

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.

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