database software - Online Tech Support

How To Use Oracle DECODE Function

online tech support Comments Off

This page is based on examples to be easier to follow. The Oracle DECODE allows you to compare the input value with the following ones and every comparable value should have a returning value. Among of all values even empty (null) values are included to the list. Function Decode in oracle can be used in themselves as many times you want, but we would not to recommend to do so and instead of you could use Oracle CASE function. The main reason for that is that your code will be more readable, using Oracle Decode function in another Decode will be difficult to follow and Oracle Case function is more clear in this case.

The syntax is following:

SELECT DECODE(<input_value>,
              <value_to_compare_1>,<result_1>,
              <value_to_compare_1>,<result_1>,..,<default_result>)
 FROM <table_name> ;

The first example is showing the DUAL table content and this one-row-table’s value is “X“. See at the output below.

SELECT * FROM DUAL;

P.S. The DUAL table is a one-row table that is present in all Oracle databases.

Using the table above Online Tech Support has prepared the next examples below with Oracle DECODE or CASE.

SELECT DECODE(DUMMY,NULL,'Empty','X','Dual Default','Unknown')
  FROM DUAL;

The query returns the “Dual Default” string because the Dummy column has value “X” and the first “NULL” condition did not match. The second value “X” did match and the Select query returned the 2nd result “Dual Default“. Oracle DECODE function takes first matching value and ignores rest, so default result “Unknown” didn’t appear either.

The 3rd example has two “X” value with different outputs. The DECODE function returns the first available value now.

SELECT DECODE(DUMMY,NULL,'Empty',
                'X','Dual Default',
                'X','Dual Default Again','Unknown')
  FROM DUAL;

As you see the same result as in the 2nd example. The reason is that Oracle DECODE function takes first matching value and ignores rest, even if the second value matching too.

When any of the values don’t match and the default value is declared then the DECODE returns the default value as on the following example.

SELECT DECODE(DUMMY,NULL,'Empty',
                'Y','Dual Default',
                'Z','Dual Default Again','Unknown')
 FROM DUAL;

As we did mention above we do not recommend to use a DECODE in another Oracle DECODE function and this is mainly about not being clear enough and it is difficult to read. As the following two examples will demonstrate the first with DECODE and the same result achieved using the Oracle CASE function.

SELECT DECODE(DUMMY,NULL,'Empty',
              DECODE(DUMMY,'X','Dual Default',
                       'X','Dual Default Again','Unknown')
              )
  FROM DUAL;

SELECT CASE DUMMY
         when NULL then 'Empty'
         when 'Y' then 'Dual Default'
         when 'X' then'Dual Default Again'
         else 'Unknown' END
  FROM DUAL;

We are thinking it’s much easier to read the SQL query when combined conditions are written within Oracle Case then with using many Oracle Decode functions.

See Also the following:
Home Oracle CASE

How To Use Oracle Sequence

online tech support Comments Off

This Online Tech Support page is based on examples to be easier to follow. The Oracle Sequence allowing to get unique integers through an independent object. The Oracle Sequence keeps track of the last number and generates a new one when to call out the NEXTVAL function. The Sequences are returning unique integers per each Oracle Sequence. They are mainly used to fill unique ID columns in Oracle tables or they can be used in applications where unique numbers are needed. To fill ID columns in tables the sequence will be much faster and safer than doing queries over the table rows and looking for the greatest ID value. We do recommend not to use ever the last query option.

The most basic syntax is :

CREATE SEQUENCE <sequence_name>
 START WITH     <number_position>
 INCREMENT BY   <number_amount>;

The first script will create a new sequence named MY_SEQUENCE into your database:

CREATE SEQUENCE my_sequence
 START WITH     1
 INCREMENT BY   1;
/

The Oracle Sequence syntax above has keyword “START WITH” and the value will be a starting point to increase the numbers. The number can be set to a higher or lower number depending on what should be the next number. The second keyword named “INCREMENT BY” will increment every next number by the amount set by the parameter. The “INCREMENT BY” value is by default “1“. When you’ll leave the “INCREMENT BY” value to “1” then the Sequence will return the following values: 2,3,4,5,.. and when it has to been set the to “2” then the Sequence will return 2,4,6,8,10,..

The first example shows how to get the next Sequence value and you can do it using a simple SQL query. The returned number “2” came from the MY_SEQUENCE object. If you’ll run the query one more time the next value will be “3” and so on.

SELECT my_sequence.nextval
 FROM dual;

To show how to use the Oracle Sequence in the Oracle insert statement we will need a table. The following script will create a new table named MY_NUMBER.

CREATE TABLE my_number
    ( ID NUMBER (17)) ;

The next example of the Oracle Insert statement will insert the next value of Sequence MY_SEQUENCE into just created table MY_NUMBER. This insert statement will enter only 1 line and with every next execution there will be one line more and with greater ID value.

INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval) ;

Let see what is inside the MY_NUMBER table. The value number “3” means the MY_SEQUENCE objects has increased its value by one more number.

SELECT *
   FROM my_number;

To use Oracle Sequence in the insert statement and once the record has been inserted you may need the ID value to store it in some other tables. The most easiest way to get the just inserted ID value is by using keyword RETURNING. We wrote this example in PL/SQL anonymous block but you can use the RETURNING keyword with the bind variables too. The Oracle Insert with the Returning keyword is following.

INSERT INTO <table_name>
    ( <table_columns> )
    VALUES
    ( <my_values>)
      RETURNING <column_names> into <variables>;

You can return any value that was just inserted with the statement but the next example returns ID value that has been generated by Oracle Sequence during the insert.

DECLARE
  v_id number;
BEGIN
 INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval)
      RETURNING ID into v_id ;

 DBMS_OUTPUT.PUT_LINE('value v_id is '||v_id);

END;
/

The ID value is stored into V_ID variable that has been declared above and after the insert procedure DBMS_OUTPUT.PUT_LINE sends text “value v_id is x” to output. Take a look at the output above and take a note the MY_SEQUENCE value has increased by 1 number more and has become number “4“.

To remove Oracle Sequence use the DROP SEQUENCE command as the following syntax:

DROP SEQUENCE <sequence_name>;

The next script will drop just created MY_SEQUENCE sequence.

DROP SEQUENCE my_sequence;
/

Also the Oracle Sequence can be used in Oracle Triggers, procedures or functions to insert the unique ID value into the Oracle tables.

See Also:
Oracle Select Oracle Insert

How To Use Oracle CASE Expression

online tech support Comments Off

This tutorial is based on examples to be easier to follow. The Oracle CASE expression allows to do comparison with values as for example IF expression does but Oracle Case would not need to be execute inside an additional procedure as it needs to be done with IF expression. The Oracle Case expression syntax are:

-- 1. simple case expression
CASE <value_to_compare> 
     WHEN <value_to_compare_with> THEN <return_value> 
   [ WHEN <value_to_compare_with_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] END

-- 2. searched case expression
CASE WHEN <the_conditions>   THEN <return_value> 
   [ WHEN <the_conditions_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] END

As you see we got two Case expressions and first of them the simple case expression lets you to do a simple conditions to find a match. The searched case expression looks almost the same as the first expression but you can use this style to write more complicated conditions and use different values to find matching.

The first Oracle Case query is written with simple case expression and the source query (SELECT 3 AS a_number FROM dual) returns only 1 row with value 3. The Case expression needs to find in the list “WHEN 3” and return text “three“.

 SELECT
    CASE a_number
        WHEN 0
        THEN 'zero'
        WHEN 1
        THEN 'one'
        WHEN 2
        THEN 'two'
        WHEN 3
        THEN 'three'
        WHEN 4
        THEN 'four'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number
          FROM dual );

The second Oracle Case query is with searched case expression and the condition is containing different value pairs as for example a_number is 1 and text should be “MySQL“. In this time the source query (SELECT 3 AS a_number, ‘Oracle’ AS text FROM dual) returns two value a number and a text and they are 3 and “Oracle“. The Case expression returns “The number is three and the text is Oracle” as this is the returning value for the second condition (WHEN a_number = 3 AND text = ‘Oracle’).

 SELECT
    CASE
        WHEN a_number = 1 AND text = 'MySQL'
        THEN 'The number is one and the text is MySQL'
        WHEN a_number = 3 AND text = 'Oracle'
        THEN 'The number is three and the text is Oracle'
        WHEN a_number = 10 AND text = 'MsSQL'
        THEN 'The number is ten and the text is MsSQL'
        ELSE 'Unknown number and text'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number, 'Oracle' AS text
          FROM dual );

In this following example we would like to show what happens when two values are correct in the Case conditions. The “source” query returns the same value (number 3) as in the first example but we do have set two “WHEN 3” conditions. First of them returns text “one” and the second “three“. Take a look at the example below output returns on the “one” text and ignores the “three” text. The reason is in the Oracle Case expression that takes first matching value from up to down and ignores rest of values.

 SELECT
    CASE a_number
        WHEN 0
        THEN 'zero'
        WHEN 3
        THEN 'one'
        WHEN 2
        THEN 'two'
        WHEN 3
        THEN 'three'
        WHEN 4
        THEN 'four'
        ELSE 'unknown'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number
          FROM dual );

As you can see on the last output Oracle Case takes only the first matching condition and ignores the rest of values, so when you did a mistake in the value declaration the Case would not raise an error.

See Also:
Home Oracle SELECT

How To Use Oracle TO_NUMBER Data Conversion

online tech support Comments Off

This tutorial is based on examples to be easier to follow. The Oracle To_Number data conversion allows doing explicit data conversions and this means in other words that it does force your value to become a number type. You will need this function when your numeric values are stored into a text column as for example VARCHAR2. When you are using two different data type in a join condition or in Oracle Union operator there is a change they will fail and your SQL query will finish with an error. It is safer to convert all values in explicit way to same data type than to let Oracle do implicit data conversion and hope it does it in correct way. The syntax of Oracle TO_NUMBER is:

TO_NUMBER (<your_value>)

You are able to convert to number only the following other data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT and BINARY_DOUBLE.

The following example does convert the Rownum pseudocolumn to a number. The function returns always number values there shouldn’t be any difficulties to convert it to a number.

 SELECT TO_NUMBER (rownum)
   FROM dual;

The output above shows a number 1 since we do have only one row. In the next Online Tech Support example we are going to use the Dummy column from the Oracle Dual table. The column value is char “X” then that should quite difficult to make a number. Take a look at the example below.

 SELECT TO_NUMBER (rownum)
   FROM dual;

As we did mention above the conversion to a number will be quite difficult or in other words impossible. The function couldn’t turn the “X” to a number and the SQL query raised error ORA-01722: invalid number. You can only convert numbers saved into a text or other data types to number types.

The following Select statement is showing the data we are going to use in our third example below. The query returns 10 lines and every 4th line returns the “X” char from the Dummy column.

 SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

This example below should show us what happens with the SQL query that has mixed value types and we are going convert them all to numbers. To see the see the data please have a look at the last example above.

 SELECT TO_NUMBER (qry.ID)
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

The Oracle Select returned error ORA-01722: invalid number again. This is not a common practice, but sometimes the data does come from other sources in a mixed way and we would need to take out only number values when they are available. To do so our computer technician has written a simple function.

 CREATE FUNCTION get_only_numbers (P_TEXT VARCHAR2)
  RETURN NUMBER
 IS
 BEGIN
   RETURN TO_NUMBER(P_TEXT);

 EXCEPTION when VALUE_ERROR
           then RETURN null;
 END get_only_numbers;

The customised Oracle function has input text variable named P_TEXT and it returns only numbers. Inside the function we will try to do conversion with function TO_NUMBER and when it fails the function raises the build-in VALUE_ERROR error. The last part of function the VALUE_ERROR error will be caught by the EXCEPTION when clause and it returns NULL (empty) value. This function allows us to filter out only number values and to avoid the ORA-01722: invalid number error. Now has left to see how the customised function with our Select statement.

 SELECT get_only_numbers(qry.ID) only_numbers
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

The SQL query returned only numbers and works without raising errors. As we did already mention above this function is not very common practice but sometimes we do have some extreme needs and this Oracle function will help you out in this case.

See Also:
Home

How To Use Oracle LIKE Conditions

online tech support Comments Off

This web page is based on examples to be easier to follow. The Conditions Oracle LIKE, LIKEC, LIKE2 and LIKE4 allow to match text value using pattern matching method. The condition returns values who’s pattern is similar or when using NOT condition then not similar. The Oracle Like syntax is:

'<you_value1>' [NOT] LIKE '<your_value2>' [ ESCAPE '<your_escape_char>' ]

This SQL query is the most simple Like condition where it is looking for a string starting with “o“. Since the word on another side of the condition is “oracle” and that is a positive match the query returns the “oracle” word.

 SELECT 'oracle'
  FROM dual
 WHERE 'oracle' LIKE 'o%';

The second SQL query has still the “oracle” word on the left side but the Like condition is looking for a string starting with “r“. Since the word doesn’t match the Select statement doesn’t return anything.

 SELECT 'oracle'
  FROM dual
 WHERE 'oracle' LIKE 'r%';

The following SQL query is showing the data we are going to use for the following Oracle Like examples and the Select statement is used as a “source” .

 SELECT 'ABC'||rownum||'def' as text
   FROM dual
    CONNECT BY rownum < 11;

In this example below we will find all texts starting with ABC so the condition looks “ABC%“. The “%” character sets a condition to Like to ignore the characters after ABC string.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE 'ABC%';

The output is not so different for the first query because all texts are starting with ABC letters. Lets amend the Oracle Like conditions and set “%1def“. This condition is looking for strings ending with “1def” and as we are using the “%” character in the beginning it doesn’t matter how the string starts or how long it is. The only thing that matters is the end “1def” and no more a letter after.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE '%1def';

The SQL query returned only 1 lines that ends with “1def“.

The fifth example with Like in Oracle has set rules to the beginning and ending parts. The string have to start with “ABC1” and it should end with “def“. Please take a look at the SQL statement the “%” character is in the middle now. This way set rule says: it is not important how many letters are long the text or what are the letter while the string starts with ABC1 and ends with def it will be suitable for us.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE 'ABC1%def';

The output has two lines and both has start and end as we set in the rule. Now if we want to be more specific and set the rule as the string should start with “ABC1″ and end with “def“, but it should have only 1 letter between them. The letter could be any possible letter but it should be only 1 letter be between them then the condition looks “ABC1_def“. With this condition the string cannot be longer than 8 characters and the query found only 1 line. See the result below.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE 'ABC1_def';

Lets try another example with the strict Oracle Like condition as we had the last one. On this example below the text should start with “ABC” and end with “def” but it shouldn’t be longer than 7 characters.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE 'ABC_def';

This output returned almost all lines except the 1 string that is 8 characters long.

The following examples are with Oracle Like and the escape character. To bring out the meaning of the escape character we will need to change a bit the “source” query. Please take a look at the new output below. There are two lines ending with “%” character and with the Like conditions above you wouldn’t be able to set those character into your condition. They would be treated as any character.

 SELECT 'ABC'||rownum||'def'||DECODE (MOD (rownum,4),0,'%') AS text
   FROM dual
    CONNECT BY rownum < 11;

On this example below we are going to set “\” as the escaping character. This character is important to let the Like condition to know that a character after the escape character should be treated as a literal character and even it may have set a special meaning in Oracle system use it as a plain character. The following example would be possible without the escape character since the “%” character means ignore rest of the text and we got 2 lines ending with the “%” character. The rule we have set in the SQL query (‘ABC%\%’ ESCAPE ‘\’) is looking for string that starts with “ABC” letters and ends with the “%” character.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def'||DECODE (MOD (rownum,4),0,'%') AS text
       FROM dual
        CONNECT BY rownum < 11)
  WHERE text LIKE 'ABC%\%' ESCAPE '\';

The output contains the two only lines that started with ABC and had the “%” character in the end.

See Also:
Home

How To Use Oracle MERGE Statement

online tech support Comments Off

This tutorial is based on examples to be easier to follow. The Oracle Merge Statement allows you use more than one source and combine different operations in the same time. Using Oracle Merge you can do Insert, Delete and Update in the same SQL statement. Since the Merge statement is deterministic you cannot update the same line more than 1 time. The Oracle Merge syntax is following:

MERGE INTO <oracle_object>
   USING (your_select_statement)
   ON (your_join_condition)
   [ WHEN MATCHED THEN UPDATE SET <your_update_condition> ]
   [  DELETE WHERE (<your_delete_condition>) ]
   [ WHEN NOT MATCHED THEN INSERT (<object_columns_for_insert>)
     VALUES (<values_for_insert>)
     WHERE (<your_insert_condition>) ];

To continue with the Merge statement examples we would need a table whose data we will amend. To keep this example as simple as possible we are creating the table from the Select statement and the table has 3 columns. The first column is ID and it contains unique numbers. The second column name is REPEATING_NUMBERS and as the name says we are repeating three numbers (0, 1, 2) over all lines. The third column is MY_TEXT and it contains text “Oracle Merge” with the ID number.

 CREATE TABLE my_merge_example AS
 SELECT rownum AS ID,
    mod (rownum,3) AS repeating_numbers,
    'Oracle Merge '||rownum AS my_text
   FROM dual
    CONNECT BY rownum < 11;

The table has filled with lines because we did use the Select statement in its creation and the lines are looking as following.

 SELECT *
   FROM my_merge_example mme
  ORDER BY mme.id;

Now before we will run the Oracle Merge statement we need to take a look at the SQL query used to update and insert the MY_MERGE_EXAMPLE table. The query has the same three columns as the table but the ID value doesn’t start from 1 it does from 5 and it ends with 11th so we wouldn’t have exact matching of lines. The REPEATING_NUMBERS has five numbers repeating – 0,1,2,3 and 4. The MY_TEXT column works the in same way as in the table and the values wouldn’t match because if the ID value. Please take a look at the lines below.

 SELECT rownum + 4 AS ID,
    MOD (rownum,5) AS repeating_numbers,
    'New text '|| TO_CHAR(rownum + 4) AS my_text
   FROM dual CONNECT BY rownum < 11;

We had a look at the table data and at the Select statement output, so we are ready to execute the Oracle Merge statement. The statement may look quite huge but let’s see the most important parts. We are joining the MY_MERGE_EXAMPLE table with the SQL query by using ID values (mme.id = qry.id). To update all matching lines it is using the same ID condition and all ID lines who has a match will be updated with columns MY_TEXT (mme.my_text = mme.my_text ||’-‘|| qry.my_text) and REPEATING_NUMBERS (mme.repeating_numbers = mme.repeating_numbers + qry.repeating_numbers). Also we will delete all lines from the MY_MERGE_EXAMPLE table who has matching ID and in SQL query the REPEATING_NUMBERS are greater than 2 (qry.repeating_numbers > 2). The Merge statement found only two lines with this condition ID 7 and 8. Since we do know our maximum ID in the table is 10 so we do the insert statement a bit faster and set the condition on ID – insert all lines from the SQL query that has ID value greater than 10 (qry.ID > 10).

 MERGE INTO my_merge_example mme
   USING ( SELECT rownum + 4 AS ID,
                  MOD (rownum,5) AS repeating_numbers,
                 'New text '|| TO_CHAR(rownum + 4) AS my_text
             FROM dual CONNECT BY rownum < 11) qry
      ON (mme.id = qry.id)
   WHEN MATCHED THEN
          UPDATE SET mme.my_text = mme.my_text ||'-'|| qry.my_text,
          mme.repeating_numbers = mme.repeating_numbers + qry.repeating_numbers
     DELETE WHERE (qry.repeating_numbers > 2)
   WHEN NOT MATCHED THEN INSERT (mme.id, mme.repeating_numbers, mme.my_text)
     VALUES (qry.id, qry.repeating_numbers, qry.my_text)
     WHERE (qry.ID > 10);

And here is the final output after running the Oracle Merge statement. As you see the MY_TEXT and REPEATING_NUMBERS are updated for IDs 5 to 10. Lines with ID 7 and 8 are gone and we got new lines from 11 to 14th. All this has been done with one statement.

  SELECT *
   FROM my_merge_example mme
  ORDER BY mme.id;

The Oracle Merge gives you an option to do more than one DML statement but Online Tech Support still thinks when the condition goes to complicate it would be better to do it as separate statements.

See Also:
Oracle Insert Oracle Update Oracle Delete Home

How To Use Oracle MINUS Operator

online tech support Comments Off

This tutorial page is based on examples to be easier to follow. The Oracle MINUS Operator compares both Select statement’s values and returns only unique lines from the upper SQL query that are missing from the lower query. The Oracle MINUS Operator syntax is following:

<your_1st_select_statement>
MINUS
<your_2nd_select_statement>;

The first MINUS example has two Oracle dual tables combined with the MINUS Operator and their values are hard coded as the upper query has number “1” and the lower query has “2“. Oracle MINUS returns lines only from the upper Select statement that are missing from the lower SQL, so you can see in the output number “1“.

 SELECT 1
  FROM DUAL
MINUS
SELECT 2
  FROM DUAL;

If we would do some light changes in the lower select statement and switch number “2” with “1” then the MINUS ooperator finds no different and returns nothing. The reason is that Oracle Minus is looking for lines that are missing from the lower query and in this example the both Select statements are identical. See the output below.

 SELECT 1
  FROM DUAL
MINUS
SELECT 1
  FROM DUAL;

To understand the 3rd example we would need to take a look at the following Select statement. Online Tech Support will use it as the upper query and you will see that it returns two rows – number “1” and “2“.

 SELECT rownum
  FROM DUAL
CONNECT BY rownum < 3;

On this 3rd Oracle Minus operator example as we said we will use the SQL query above. Please take a look at the following example.

 SELECT rownum
  FROM DUAL
CONNECT BY rownum < 3
MINUS
SELECT 1
  FROM DUAL;

The output returned only number “2” because both queries have common value number “1” and number “2” exists only in the upper Select statement.

To see how does return Oracle Minus operator only unique rows we need to use the following query. Take a look at the output that has repeating values of “1” and “2“. In total you can find 5 lines with number “1” and 5 lines with number “2“.

 SELECT mod (rownum,2) + 1 AS nr
   FROM DUAL
    CONNECT BY rownum < 11;

Using the SQL query above Online Tech Support will do Minus from 1 line Dual table that has value “1“. In other words, the 10 lines with repeating values of 1-s and 2-s against one line with value 1.

 SELECT mod (rownum,2) + 1 AS nr
   FROM DUAL
    CONNECT BY rownum < 11
  MINUS
 SELECT 1
   FROM DUAL;

The output returned only 1 line with number “2“. Oracle Minus returns only lines that has no value match with the Select below and that removed all number “1” values. For second the Minus operator returns only unique lines and that means Oracle Minus does grouping. All repeating number “2” values got grouped into 1 line and if there would be a second value that doesn’t group we would have in output 5 number “2” value rows.