About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.4.4
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Example of EXPORT INTO

SELECT id, ddate, price, qty, sku FROM ssa.demossa WHERE ddate > '2004-01-01' 
  EXPORT INTO 
    PATHS='\\ALPHA1\sct ; \\BETA1\work 7\sct 
            \\DELTA1\usr\tmp'
    NOF=3
    FP='sct_export1' 
    ESC='\x5c' 
    CD='\x7c' 
    RD='\x0a' 
    NULL='\x87' ;

The above SELECT statement fetches all records from the DemoSSA table where the ddate value is after January 1, 2004, and exports this data to four flat files that will be created on the three specified computers (ALPHA1, BETA1, DELTA1). The flat files will have the names "sct_export1.000", "sct_export1.001", and "sct_export1.002". Columns in the flat files will be delimited by the pipe ("|") symbol; rows will be delimited by the linefeed character; null values will be indicated by the "double dagger" symbol ("‡"); and the escape character will be a backslash ("\").

The equivalent SELECT statement with the longer parameter aliases is as follows:

SELECT id, ddate, price, qty, sku FROM ssa.demossa WHERE ddate > '2004-01-01' 
  EXPORT INTO 
    PATHS='\\ALPHA1\sct ; \\BETA1\work 7\sct 
           \\DELTA1\usr\tmp'
    NUMBEROFFILES=3
    FILEPREFIX='sct_export1' 
    ESCAPE='\x5c' 
    COLUMNDELIMITER='\x7c' 
    ROWDELIMITER='\x0a' 
    NULL='\x87' ;

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Example of EXPORT INTO

Example of EXPORT INTO