Informatica Interview Questions---IV

What are the joiner caches?
Ans: When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows. After building the caches, the Joiner transformations reads records from the detail source and perform joins.

What is the look up transformation?
Ans: Use lookup transformation in your mapping to lookup data in a relational table, view, and synonym. Informatica server queries the look up table based on the lookup ports in the transformation. It compares the lookup transformation port values to lookup table column values based on the look up condition.

How to find all invalid mappings in a folder
Ans: Use following query
SELECT MAPPING_NAME FROM REP_ALL_MAPPINGS WHERE SUBJECT_AREA='YOUR_FOLDER_NAME' AND PARENT_MAPPING_IS_VALIED <>1

What are the data movement modes in Informatica?
Ans: Data movement modes determines how power center server handles the character data. We choose the data movement in the Informatica server configuration settings. Two types of data movement modes available in Informatica.
1.ASCII mode
2.Unicode mode.

Can you use the mapping parameters or variables created in one mapping into another mapping?
Ans: NO. We can use mapping parameters or variables in any transformation of the same mapping or mapplet in which you have created mapping parameters or variables.

Can you use the mapping parameters or variables created in one mapping into any other reusable transformation?
Ans: Yes. Because reusable transformation is not contained with any mapplet or mapping.

How can you improve session performance in aggregator tansformation?
Ans: Use sorted input.

What is aggregate cache in aggregator transformation?
Ans: The aggregator stores data in the aggregate cache until it completes aggregate calculations. When you run a session that uses an aggregator transformation, the informatica server creates index and data caches in memory to process the transformation. If the informatica server requires more space, it stores overflow values in cache files.

What are Limitations on joiner transformation ?
Ans: 1.Both pipelines begin with the same original data source.
2.Both input pipelines originate from the same Source Qualifier transformation.
3.Both input pipelines originate from the same Normalizer transformation.
4.Both input pipelines originate from the same Joiner transformation.
5.Either input pipelines contains an Update Strategy transformation.
6.Either input pipelines contains a Sequence Generator transformation.

What will happen if we connect only current value port from seq generator to next transformation (without connecting nextval)
Ans: Each target will get the value 1.

What do you mean by code page compatibility?
Ans: When two code pages are compatible, the characters encoded in the two code pages are virtually identical which ensures no data loss. This compatibility is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. One code page can be a subset or superset of another. For proper data movement, the target code page must be a superset of the source code page.

What is difference between partitioning of relational target and partitioning of file targets?
Ans: If you partition a session with a relational target Informatica server creates multiple connections to the target database to write target data concurrently. If you partition a session with a file target the Informatica server creates one target file for each partition. You can configure session properties to merge these target files.

What are the transformations that restricts the partitioning of sessions?
Ans: Advanced External procedure transformation and External procedure transformation: This transformation contains a check box on the properties tab to allow partitioning.
Aggregator Transformation: If you use sorted ports you can not partition the associated source
Joiner Transformation: you can not partition the master source for a joiner transformation .Normalizer Transformation XML targets.

Informatica Interview Questions---III

Can you use the mapping parameters or variables created in one mapping into another mapping?
Ans: No

What is difference between direct and indirect loading options in sessions?
Ans: Use file type direct when we are loading single file into Target. Use Indirect when we want to load multiple files through single session in the mapping

When we create a target as flat file and source as oracle. How can we have first rows as column names in flat files.
Ans :We can add a union all clause in the Source Qualifier of the Relational source to add the header values in SELECT clause, so as to populate them as header.

How to capture the user information (Username) of the person, who is executing a workflow or session in workflow manager.
Ans : SELECT USER_NAME,WORKFLOW_NAME FROM REP_WFLOW_RUN;

Which ETL tool is more preferable Informatica or Data Stage and why?
Ans: preference of an ETL tool depends on affordability and functionality. It is mostly a tradeoff between the price and feature. While Informatica has been a market leader since the past many years, DataStage is beginning to pick up momentum.

What is worklet?
Ans: Worklet is an object that represents a set of tasks.

What is workflow?
Ans: A workflow is a set of instructions that tells the Informatica server how to execute the tasks.

What is session?
Ans: A session is a set of instructions to move data from sources to targets.

Why do we need SQL overrides in Lookup transformations?
Ans: In order to lookup more than one value from one table, we go for SQL overrides in Lookups.

What is the difference between static cache and dynamic cache.
Ans: Static cache: you can not insert or update the cache.
The informatica server returns a value from condition when the condition is false. This indicates that is true. When the condition is not true, informatica server returns the default value
Dynamic cache: You can insert rows into the cache as you pass to the target.
The informatica server inserts rows into cache when the condition is false. This indicates the row is not in the cache or target.
You can pass these rows to the target table. For connected transformations and null for unconnected transformations.

Which transformation should we use to normalize the COBOL and relational sources?
Ans: Normalizer Transformation.
When you drag the COBOL source in to the mapping Designer workspace, the normalizer transformation automatically appears, creating input and output ports for every column in the source.

How to join two tables, which reside in two different databases in the Source qualifier.
Ans: By using DB links in SQ ,but throughput will be very slow. Joiner will be better approach in this case

What is a code page?
Ans: A code page contains encoding to specify characters in a set of one or more languages. The code page is selected based on source of the dat.For example if source contains Japanese text then the code page should be selected to support Japanese text.
When a code page is chosen, the program or application for which the code page is set, refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.

Informatica Interview Questions---II

What are the settiings that you use to cofigure the joiner transformation?
Ans: Master and detail source Type of join Condition of the join.

What are the types of groups in Router transformation?
Ans: Input group Output group The designer copies property information from the input ports of the input group to create a set of output ports for each output group. Two types of output groups User defined.

How many ways you can update a relational source defintion and what are they?
Ans: Two ways 1. Edit the definition 2. Reimport the defintion

What are the options in the target session of update strategy transsformatioin?
Ans: Insert Delete Update Update as update Update as insert Update esle insert Truncate table
What is the default source option for update stratgey transformation?
Ans: Data driven.

Which tool would you use to create and manage sessions and batches and to monitor and stop the Informatica server?
Ans: Informatica server manager.

What are partition points?
Ans: Partition points mark the thread boundaries in a source pipeline and divide
the pipeline into stages

How can you recognize whether or not the newly added rows in the source are get inserted in the target?
Ans: In the Type2 mapping we have three options to recognize the newly added rows 1. Version number
2. Flag value
3. Effective date Range

What are two types of processes that Informatica runs the session?
Ans: Load manager Process: Starts the session, creates the DTM process, and sends post-session email when the session completes. The DTM process. Creates threads to initialize the session, read, write, and transform data, and handle pre and post-session operations.

What is parameter file?
Ans: Parameter file is to define the values for parameters and variables used in a session.A parameter file is a file created by text editor such as word pad or notepad.
You can define the following values in parameter file:-
Maping parameters
Maping variables
session parameters.

Differences between Normalizer and Normalizer transformation.
Ans: Normalizer: It is a transformation mainly using for Cobol sources,
it's change the rows into columns and columns into rows
Normalization: To remove the redundancy and inconsistency

What is the default source option for update strategy transformation?
Ans: Data driven.

What is Data driven?
Ans: The Informatica server follows instructions coded into update strategy transformations with in the session mapping determine how to flag records for insert,update,,delete or reject .If you do not choose data driven option setting, the Informatica server ignores all update strategy transformations in the mapping.

Informatica Interview Questions--I

Which tool you use to create and manage sessions and batches and to monitor and stop the Informatica server?
Ans: Informatica server manager.

Why we use partitioning the session in Informatica?
Ans: Partitioning achieves the session performance by reducing the time period of reading the source and loading the data into target.

To achieve the session partition what are the necessary tasks you have to do?
Ans:
Configure the session to partition source data.
Install the Informatica server on a machine with multiple CPU’s.

What is the default join that source qualifier provides?
Ans: Inner EquiJoin

What is Datadriven?
Ans.The Informatica server follows instructions coded into update strategy transformations within the session mapping which determine how to flag records for insert, update, delete or reject. If we do not choose data driven option setting, the Informatica server ignores all update strategy transformations in the mapping.

What is polling
Ans: It displays the updated information about the session in the monitor window. The monitor window displays the status of each session when you poll the Informatica server.

What are the types of mapping wizards that are provided in Informatica?
Ans: The designer provide two mapping wizard.
Getting Started Wizard creates mapping to load static facts and dimension tables as well as slowly growing dimension tables.
Slowly Changing Dimensions Wizard, creates mappings to load slowly changing dimension tables based on the amount of historical dimension data we want to keep and the method we choose to handle historical dimension data.

Explain reference cursor? (PL/SQL)
Ans: Cursor doesn't have a return type but A Reference Cursor have a return type and it as 2 type one is Strongly Typed Cursor and Weakly Typed Cursor

What is a source qualifier? What do you mean by Query Override?
Ans: Source Qualifier represents the rows that the PowerCenter Server reads from a relational or flat file source when it runs a session. When the definition of the relational or flat file is added to mapping then it is connected to Source Qualifier transformation. The default query is SELECT statement containing all the source columns. Source Qualifier has capability to override this default query by changing the default settings of the transformation properties.

Can you generate reports in Informatica?
Ans: Yes. By using Metadata reporter we can generate reports in Informatica.

What is metadata reporter?
Ans: It is a web based application that enables you to run reports against repository metadata. With a Meta data reporter you can access information about your repository with out having knowledge of sql, transformation language or underlying tables in the repository.

Define mapping and sessions?
Ans: Mapping: It is a set of source and target definitions linked by transformation objects that define the rules for transformation.
Session: It is a set of instructions that describe how and when to move data from source to targets.

Collection OF 28 FAQ Oracle SQL Language Basics

A collection of 28 FAQs on Oracle SQL language basics. Clear answers are provided with tutorial exercises on data types, data literals, date and time values, data and time intervals, converting to dates and times, NULL values, pattern matches. Topics included in this FAQ are:

1. What Is SQL?
2. How Many Categories of Data Types?
3. What Are the Oracle Built-in Data Types?
4. What Are the Differences between CHAR and NCHAR?
5. What Are the Differences between CHAR and VARCHAR2?
6. What Are the Differences between NUMBER and BINARY_FLOAT?
7. What Are the Differences between DATE and TIMESTAMP?
8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
9. What Are the Differences between BLOB and CLOB?
10. What Are the ANSI Data Types Supported in Oracle?
11. How To Write Text Literals?
12. How To Write Numeric Literals?
13. How To Write Date and Time Literals?
14. How To Write Date and Time Interval Literals?
15. How To Convert Numbers to Characters?
16. How To Convert Characters to Numbers?
17. How To Convert Dates to Characters?
18. How To Convert Characters to Dates?
19. How To Convert Times to Characters?
20. How To Convert Characters to Times?
21. What Is NULL?
22. How To Use NULL as Conditions?
23. How To Concatenate Two Text Values?
24. How To Increment Dates by 1?
25. How To Calculate Date and Time Differences?
26. How To Use IN Conditions?
27. How To Use LIKE Conditions?
28. How To Use Regular Expression in Pattern Match Conditions?

Sample scripts used in this FAQ can be executed with SQL*Plus to any Oracle database server.

What Is SQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

How Many Categories of Data Types?

Oracles supports the following categories of data types:

• Oracle Built-in Datatypes.
• ANSI, DB2, and SQL/DS Datatypes.
• User-Defined Types.
• Oracle-Supplied Types.

What Are the Oracle Built-in Data Types?

There are 20 Oracle built-in data types, divided into 6 groups:

• Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
• Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
• Long and Row Datatypes - LONG, LONG RAW, RAW
• Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
• Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
• Row ID Datatypes - ROWID, UROWID

What Are the Differences between CHAR and NCHAR?

Both CHAR and NCHAR are fixed length character data types. But they have the following differences:

• CHAR's size is specified in bytes by default.
• NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
• NCHAR stores characters in Unicode.

What Are the Differences between CHAR and VARCHAR2?

The main differences between CHAR and VARCHAR2 are:

• CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
• VARCHAR2 stores values in variable lengths. Values are not padded with any characters.

What Are the Differences between NUMBER and BINARY_FLOAT?

The main differences between NUMBER and BINARY_FLOAT are:

• NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
• BINARY_FLOAT stores values as single precision floating-point numbers.

What Are the Differences between DATE and TIMESTAMP?

The main differences between DATE and TIMESTAMP are:

• DATE stores values as century, year, month, date, hour, minute, and second.
• TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?

The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:

• INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
• INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.

What Are the Differences between BLOB and CLOB?

The main differences between BLOB and CLOB are:

• BLOB stores values as LOB (Large OBject) in bitstreams.
• CLOB stores values as LOB (Large OBject) in character steams.

What Are the ANSI Data Types Supported in Oracle?

The following ANSI data types are supported in Oracle:

• CHARACTER(n) / CHAR(n)
• CHARACTER VARYING(n) / CHAR VARYING(n)
• NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
• NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n)
• NUMERIC(p,s)
• DECIMAL(p,s)
• INTEGER / INT
• SMALLINT
• FLOAT
• DOUBLE PRECISION
• REAL

How To Write Text Literals?

There are several ways to write text literals as shown in the following samples:

SELECT 'FYICenter.com' FROM DUAL -- The most common format

FYICenter.com

SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped

It's Sunday! 

SELECT N'Allo, C''est moi.' FROM DUAL -- National chars

Allo, C'est moi.

SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter

It's Sunday!

How To Write Numeric Literals?

Numeric literals can coded as shown in the following samples:

SELECT 255 FROM DUAL -- An integer

255

SELECT -6.34 FROM DUAL -- A regular number

-6.34

SELECT 2.14F FROM DUAL -- A single-precision floating point

2.14 

SELECT -0.5D FROM DUAL -- A double-precision floating point

-0.5 

How To Write Date and Time Literals?

Date and time literals can coded as shown in the following samples:

SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format

03-OCT-02

SELECT TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL

31-JAN-97 09.26.50.124000000 AM

-- This is ANSI format

How To Write Date and Time Interval Literals?

Date and time interval literals can coded as shown in the following samples:

SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH

  FROM DUAL 

  -- 123 years and 2 months is added to 2002-10-03

03-DEC-25

SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL

  -- 123 years is added to 2002-10-03

03-OCT-25

SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL

  -- 299 months years is added to 2002-10-03

03-SEP-27 

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL

04-FEB-97 02.39.00.346000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL

04-FEB-97 02.38.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL

07-MAR-98 02.26.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '400' DAY(3) FROM DUAL

07-MAR-98 09.26.50.124000000 AM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL

31-JAN-97 08.39.00.346222200 PM 

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 

  + INTERVAL '30.12345' SECOND(2,4) FROM DUAL

31-JAN-97 09.27.20.247500000 AM  

How To Convert Numbers to Characters?

You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(4123.4570) FROM DUAL

123.457

SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL

     $4,123.46

SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL

 -4.12E+03

How To Convert Characters to Numbers?

You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples:

SELECT TO_NUMBER('4123.4570') FROM DUAL

4123.457

SELECT TO_NUMBER('     $4,123.46','$9,999,999.99') FROM DUAL

4123.46

SELECT TO_NUMBER(' -4.12E+03') FROM DUAL

-4120

How To Convert Dates to Characters?

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;

  -- SYSDATE returns the current date

07-MAY-2006 

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;

2006/05/07 

SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;

MAY       07, 2006

SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL;

May 7, 2006

SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL;

SUNDAY, MAY 7, 2006 

How To Convert Characters to Dates?

You can convert dates to characters using the TO_DATE() function as shown in the following examples:

SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL;

07-MAY-06

SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL;

07-MAY-06

SELECT TO_DATE('MAY       07, 2006', 'MONTH DD, YYYY')

  FROM DUAL;

07-MAY-06

SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL;

07-MAY-06

SELECT TO_DATE('SUNDAY, MAY 7, 2006', 

  'fmDAY, MONTH DD, YYYY') FROM DUAL;

07-MAY-06 

How To Convert Times to Characters?

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

04:49:49

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL;

  -- Error: SYSDATE has no fractional seconds

SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL;

16:52:57.847000000                     

SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;

  -- Seconds past midnight

69520

How To Convert Characters to Times?

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 

  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

  -- Default date is the first day of the current month

01-MAY-2006 04:49:49

SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 

  'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9') 

  FROM DUAL;

01-MAY-2006 16:52:57.847000000

SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 

  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

01-MAY-2006 19:18:40

What Is NULL?

NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most opertations has special rules when NULL is involved. The tutorial exercise below shows you some examples:

SET NULL 'NULL'; -- Make sure NULL is displayed

SELECT NULL FROM DUAL;

N

-

N

U

L

L

SELECT NULL + NULL FROM DUAL;

 NULL+NULL

----------

NULL

SELECT NULL + 7 FROM DUAL;

    NULL+7

----------

NULL

SELECT NULL * 7 FROM DUAL;

    NULL*7

----------

NULL

SELECT NULL || 'A' FROM DUAL;

N

-

A

SELECT NULL + SYSDATE FROM DUAL;

NULL+SYSD

---------

NULL

How To Use NULL as Conditions?

If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples:

SELECT 'A' IS NULL FROM DUAL;

  -- Error: Boolean is not data type. 

  -- Boolean can only be used as conditions

SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

FALSE

SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

TRUE

SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

FALSE

SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

TRUE

SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

  -- Do not use "="

FALSE

SELECT CASE WHEN 'A' <> NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

  -- Do not use "<>"

FALSE

SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END

  FROM DUAL;

  -- Do not use "="

FALSE

How To Concatenate Two Text Values?

There are two ways to concatenate two text values together:

• CONCAT() function.
• '||' operation.

Here is some examples on how to use them:

SELECT 'FYI' || 'Center' || '.com' FROM DUAL;

FYICenter.com 

SELECT CONCAT('FYICenter','.com') FROM DUAL;

FYICenter.com 

How To Increment Dates by 1?

If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:

SELECT TO_DATE('30-APR-06') + 1 FROM DUAL;

  -- Adding 1 day to a date

01-MAY-06 

SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06')

  FROM DUAL;

  -- Taking date differences

1

SELECT SYSTIMESTAMP + 1 FROM DUAL;

  -- The number you add is always in days.

08-MAY-06

SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3')

  FROM DUAL;

-- Error: Adding 1 to a timestamp makes it a date. 

How To Calculate Date and Time Differences?

If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:

SELECT

  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')

   - 

   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))

  YEAR(4) TO MONTH FROM DUAL;

  -- 109 years and 3 months

109-3  

SELECT 

  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')

   - 

   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))

  DAY(9) TO SECOND FROM DUAL;

  -- 39901 days and some seconds

39901 7:26:7.0  

SELECT 

  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',

  'DD-MON-YYYY HH24:MI:SS.FF3') - 

   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',

  'DD-MON-YYYY HH24:MI:SS.FF3'))

  YEAR(4) TO MONTH FROM DUAL;

  -- 109 years and 3 months

109-3  

SELECT 

  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',

  'DD-MON-YYYY HH24:MI:SS.FF3') - 

   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',

  'DD-MON-YYYY HH24:MI:SS.FF3'))

  DAY(9) TO SECOND

  FROM DUAL;

  -- 39901 days and some fractional seconds

39901 7:26:7.723000000 

How To Use IN Conditions?

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.

Some examples are given in the script below:

SELECT CASE WHEN 3 IN (1,2,3,5) THEN 

  'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE 

SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN 

  'TRUE' ELSE 'FALSE' END FROM DUAL;

FALSE   

SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN 

  'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

How To Use LIKE Conditions?

LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:

• '_' is used in the pattern to match any one character.
• '%' is used in the pattern to match any zero or more characters.
• ESCAPE clause is used to provide the escape character in the pattern.

The following script provides you some good pattern matching examples:

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%' 

  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE   

SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%' 

  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

  -- Case sensitive by default

FALSE

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com' 

  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE   

SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\' 

  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE   

How To Use Regular Expression in Pattern Match Conditions?

If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().

The following script provides you some good examples:

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*fyi.*',

  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',

  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')

  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;