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.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Examples of IN Predicates

The following is an example of an IN clause with a value list:

SELECT sno, sname
FROM supplier
WHERE sno IN ('S1', 'S2', 'S3');

All data values in the sno
column must match one of the three string constants in order for their corresponding records to be retrieved.

This query retrieves the following data values:

sno	sname
S1	SMITH
S2	JONES
S3	BLAKE

The following is an example of IN-file usage:

SELECT col2 
FROM sct1 
WHERE col1 IN { SOURCEFILE 'C:\ssa\tmp\infile.csv' DELIMITER ',' } 
OR col1 IN { SOURCEFILE '\\ALPHA\Public\in.txt' REMOTE } ;

In this example, an archived table named sct1
is queried. Each value in column col1
is compared against the set of values in the client-side file, infile.csv
, where separate values are separated by commas. The values in the same column are also checked against the contents of a remote, server-side file named in.txt
(on a machine named ALPHA), whose individual values are assumed to be delimited by newline, or newline plus carriage return, characters. The query returns the col2
value for each record that satisfies the IN-file membership testing.

The final example shows an IN clause containing a noncorrelated subquery:

SELECT SUM(S.sales) AS Total_UK_Sales 
FROM stores S 
WHERE S.storeno IN (SELECT L.storeno 
FROM locations L 
WHERE L.country = 'UK');

Here, the outer query calculates the total sales of stores whose storeno
value is contained in the result set of the inner query. The inner query (the noncorrelated subquery) returns only the storeno
values of stores located in the UK. Hence, the value returned by the overall query is the sum of sales for UK stores only.

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Examples of IN Predicates

Examples of IN Predicates