...
This operator imports data from MSSQL, ORACLE or ODBC data sources
Version 01 | Works with the keyword dal. |
Version 03 | Understand different keywords – e.g. for the use of parameters. |
...
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 2nd 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.
|
|
Anchor |
---|
| _Different_SQL-Keywords_from |
---|
| _Different_SQL-Keywords_from |
---|
|
Anchor |
---|
| _Dealing_with_different |
---|
| _Dealing_with_different |
---|
|
Anchor |
---|
| _Toc341960086 |
---|
| _Toc341960086 |
---|
|
Different SQL-Keywords from MS-SQL and Oracle
...
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 |
Code Block |
---|
| > set DateStyle='DMY, German';
SET
> show datestyle;
┌─────────────┐
│ DateStyle │
├─────────────┤
│ German, DMY │
└─────────────┘
> select '01.12.2017'::timestamp;
┌─────────────────────┐
│ timestamp │
├─────────────────────┤
│ 01.12.2017 00:00:00 │
└─────────────────────┘
> select now()::timestamp without time zone::text;
┌────────────────────────────┐
│ now │
├────────────────────────────┤
│ 19.05.2017 14:17:38.455419 │
└────────────────────────────┘
> reset datestyle;
RESET
> show datestyle;
┌───────────┐
│ DateStyle │
├───────────┤
│ ISO, MDY │
└───────────┘
> select '01.12.2017'::timestamp;
┌─────────────────────┐
│ timestamp │
├─────────────────────┤
│ 2017-01-12 00:00:00 │
└─────────────────────┘
> select now()::timestamp without time zone::text;
┌────────────────────────────┐
│ now │
├────────────────────────────┤
│ 2017-05-19 14:17:38.455419 │
└────────────────────────────┘ |
Code Block |
---|
title | LC_NUMERIC (hier müsste man Postgres auf DB8 restarten, damit man auf de_DE.utf8 zugreifen kann) |
---|
| > set lc_numeric='de_DE.utf8';
SET
> show lc_numeric;
┌────────────┐
│ lc_numeric │
├────────────┤
│ de_DE.utf8 │
└────────────┘
> select 1.234::text;
┌───────┐
│ text │
├───────┤
│ 1.234 │
└───────┘
> select to_char(1.234,'9D999');
┌─────────┐
│ to_char │
├─────────┤
│ 1,234 │
└─────────┘
> reset lc_numeric;
RESET
> show lc_numeric;
┌─────────────┐
│ lc_numeric │
├─────────────┤
│ en_US.UTF-8 │
└─────────────┘
> select 1.234::text;
┌───────┐
│ text │
├───────┤
│ 1.234 │
└───────┘
> select to_char(1.234,'9D999');
┌─────────┐
│ to_char │
├─────────┤
│ 1.234 │
└─────────┘ | | | |
|
Anchor |
---|
| _Trouble-Shooting |
---|
| _Trouble-Shooting |
---|
|
Anchor |
---|
| _Avoid_Empty_Tables |
---|
| _Avoid_Empty_Tables |
---|
|
Anchor |
---|
| _Toc341960088 |
---|
| _Toc341960088 |
---|
|
Avoid Empty Tables
Task | Steps | Notes |
Avoiding empty Tables | As of version 5.8 empty tables can be avoided by selecting the option. In case an empty table would be returned a defined warning appears instead of the table.
 | |
Access to [TIS] system's database
Task | Steps | Notes |
System's database | Use keyword "dal " for the system's database connection. Data source setting is irrelevant. 
| |
Troubleshooting
Log message | Description | Resolution |
---|
XimesDAL.SQLImportConnectionInternal - Specified cast is not valid. | some data types cannot be cast | cast to a compatible data type - datetime
- int, double, string
|
Example: ...
Situation | ... |
---|
Settings | ... |
---|
Result | ... |
---|
Project-File | ... |
---|
...
Expand |
---|
|
Example 1: ...... Calculation method | Settings | Result |
---|
... | | |
|
|
|
Expand |
---|
|
Problem | Frequent Cause | Solutions |
---|
Can't find the data | Somewhere in the chain access is not given | Steps: - Test the TCP/IP Connection to the server
- Check whether the database is visible to the database server(e.g. for oracle check the OracleNet settings)
- Check User-Dsn & System DSN
- Check whether the server process (e.g. IIS_WPG) has the right to read,RXL (Read, Execute und List)</… in the Orcale-Client folder: C:\Oracle\ … her are the drivers.
- Reset the IIS-Server if updates do not get through (but make sure not to interrupt other persons work)
| Error-Message Import: Sorry, currently import from TIS File System is not possible! | The data-source is used by another process and cannot be accessed. | Wait until the other process releases the data-set or file or consider a redesign. | Connection String does not work | Some ODBC-Drive to not work with blanks in the filename. | Write the name in squared brackets [ODBC with blanks.mdb]. |
| File-names … | Some drivers prefer ' instead of " to delimit texts |
| Access problems | Check whether the Web-Server has access rights to the file you want to read (might have been lost when copying). | Import does not work | The datatype is not correct. E.g. the database provides numeric data but TIS expects it to either floating point or integer, or database export Date but TIS expects DateTime. | Use a convert command in the SQL string – see Dealing with different SQL-Keywords from MS-SQL and Oracle. | Connection string does not work anymore | It could be caused by a change of the server from 32bit to 64bit and no update of ODBC-drivers. | Talk with your system administrator for updates of corresponding ODBC-drivers. | Cannot find file I uploaded to the TIS-data management | Import SQL cannot access these files | Put the files on the server. | Sum or Average does not work | Sometimes the database as difficulties with large values | Transformation helps cast(avg(C40) as float(53)) as Hour | Maximum Query Run Time was exceeded | Although a maximum runtime was set for the query, the query need more time. | Not all SQL provider support the maximum runtime feature. | Indexing | How to build an index for a table | create index <ix> on <table> (c0,c1,c2,.....); The column names in the tables are always Cxxxxx. Speaking names are defined only in the views. If possible, you should take care that you do not insert sorted, otherwise the tree degenerates. If this is not possible, you can make a reindex at the end of large inserts to balance the tree again. |
|
Related topics
- Get the information which database is actually in use via XIH Functions in the Formula operator
...