Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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*60EXTRACT(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.

NoteCheck 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
titleDateStyle
> 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
titleLC_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 messageDescriptionResolution

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


Example: ...

Situation

...

Settings...
Result

...

Project-File...

...

Expand
titleExamples

Example 1: ...

...

Calculation methodSettingsResult
... 

 





Expand
titleTroubleshooting


ProblemFrequent CauseSolutions

Can't find the data

Somewhere in the chain access is not given

Steps:

  1. Test the TCP/IP Connection to the server
  2. Check whether the database is visible to the database server(e.g. for oracle check the OracleNet settings)
  3. Check User-Dsn & System DSN
  4. 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.
  5. 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.

IndexingHow 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

...