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

CSV File Lookup Example


SELECT c1, c2, c3 
FROM S1.T1 
WHERE 
Lookup ( c1 <= %1 AND c2 = %2 AND c3 >= %3,
SOURCEFILE 'C:/prod/csv/20090116.csv' DELIMITER ','
) ;
       where the CSV file 20090116.csv contains the following data:
"20071222072132021540241263",158.97,"20071223091422020670233655"
"20071218163352021130070420",12.26,"20071218163352021130070420"
"20071217125938020250714927",22.20,"20071217125938020250714927"
"20071222085214020220173289",148.91,"20071222085214020220173289"
"20071217121333021600203214",56.48,"20071223091422020670233670"

Both of the Lookup examples above are effectively identical to the following SQL statement:

SELECT c1, c2, c3
FROM S1.T1
WHERE
(c1 <= '20071222072132021540241263' AND c2 = 158.97 AND 
c3 >= '20071223091422020670233655') OR 
(c1 <= '20071218163352021130070420' AND c2 =  12.26 AND 
c3 >= '20071218163352021130070420') OR 
(c1 <= '20071217125938020250714927' AND c2 =  22.20 AND 
c3 >= '20071217125938020250714927') OR 
(c1 <= '20071222085214020220173289' AND c2 = 148.91 AND 
c3 >= '20071222085214020220173289') OR 
(c1 <= '20071217121333021600203214' AND c2 =  56.48 AND 
c3 >= '20071223091422020670233670') ;

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

CSV File Lookup Example

CSV File Lookup Example