Randomly Sample Data
One can use the TABLESAMPLE schema to randomly sample rows for subsequent analysis.
Table Sample Syntax
SELECT ... FROM table name
Sample 5% of the rows in the staff table. Get the same result each time:
Sample rows in two tables
Sample 18% of the rows in the employee table and 25% of the rows in the employee-activity table, then join the two tables together. Because each table is sampled independently, the fraction of rows that join will be much less either sampling rate:
Sample a declared global temporary table, and also apply other predicates:
Sample Views used in Join Examples
Convert Character to Numeric
The DOUBLE, DECIMAL, INTEGER, SMALLINT, and BIGINT functions call all be used to convert a character field into its numeric equivalent:
Convert Character to Numeric - SQL
Not all numeric functions support all character representations of a number. The following table illustrates what's allowed and what's not:
Acceptable conversion valuesINPUT STRING COMPATIBLE FUNCTIONS
Checking the Input
There are several ways to check that the input character string is a valid representation of a number - before doing the conversion. One simple solution involves converting all digits to blank, then removing the blanks. If the result is not a zero length string, then the input must have had a character other than a digit:
Checking for non-digitsWITH temp1 (c1) AS (VALUES ' 123','456 ',' 1 2',' 33%',NULL)
One can also write a user-defined scalar function to check for non-numeric input, which is what is done below. This function returns "Y" if the following is true:
Now for the code:
Check Numeric function, part 1 of 2
Check Numeric function, part 2 of 2IF SUBSTR(instr,ctr,1) <> ' ' THEN
The CHAR and DIGITS functions can be used to convert a DB2 numeric field to a character representation of the same, but as the following example demonstrates, both functions return problematic output:
CHAR and DIGITS function usageSELECT d_sal
The DIGITS function discards both the sign indicator and the decimal point, while the CHAR function output is (annoyingly) left-justified, and (for decimal data) has leading zeros. We can do better.
Below are three user-defined functions that convert integer data from numeric to character, displaying the output right-justified, and with a sign indicator if negative. There is one function for each flavor of integer that is supported in DB2:
User-defined functions - convert integer to character
Each of the above functions works the same way (working from right to left):
The next example uses the first of the above functions:
Creating a similar function to handle decimal input is a little more tricky. One problem is that the CHAR function adds leading zeros to decimal data, which we don't want. A more serious problem is that there are many sizes and scales of decimal data, but we can only create one function (with a given name) for a particular input data type.
Decimal values can range in both length and scale from 1 to 31 digits. This makes it impossible to define a single function to convert any possible decimal value to character with possibly running out of digits, or losing some precision.
NOTE: The fact that one can only have one user-defined function, with a given name, per DB2 data type, presents a problem for all variable-length data types - notably character, varchar, and decimal. For character and varchar data, one can address the problem, to some extent, by using maximum length input and output fields. But decimal data has both a scale and a length, so there is no way to make an all-purpose decimal function.
Despite the above, below is a function that converts decimal data to character. It compromises by assuming an input of type decimal(22,2), which should handle most monetary values:
User-defined function - convert decimal to character
The function works as follows:
Below is the function in action:
Convert DECIMAL to CHAR
Floating point data can be processed using the above function, as long as it is first converted to decimal using the standard DECIMAL function.
The next function converts decimal input to character, with embedded comas. It first coverts the value to character - as per the above function. It then steps though the output string, three bytes at a time, from right to left, checking to see if the next-left character is a number. If it is, it insert a comma, else it adds a blank byte to the front of the string:
User-defined function - convert decimal to character - with commas
Convert Timestamp to Numeric
There is absolutely no sane reason why anyone would want to convert a date, time, or timestamp value directly to a number. The only correct way to manipulate such data is to use the provided date/time functions. But having said that, here is how one does it:
Convert Timestamp to number
Selective Column Output
There is no way in static SQL to vary the number of columns returned by a select statement. In order to change the number of columns you have to write a new SQL statement and then rebind. But one can use CASE logic to control whether or not a column returns any data.
Imagine that you are forced to use static SQL. Furthermore, imagine that you do not always want to retrieve the data from all columns, and that you also do not want to transmit data over the network that you do not need. For character columns, we can address this problem by retrieving the data only if it is wanted, and otherwise returning to a zero-length string. To illustrate,
here is an ordinary SQL statement:
Sample query with no column control
Here is the same SQL statement with each character column being checked against a hostvariable. If the host-variable is 1, the data is returned, otherwise a zero-length string:
Sample query with column control
Making Charts Using SQL
Imagine that one had a string of numeric values that one wants to display as a line-bar chart. With a little coding, this is easy to do in SQL:
Make chart using SQL
To create the above graph we first converted the column of interest to an integer field of a manageable length, and then used this value to repeat a single "*" character a set number of times.
One problem with the above query is that we won't know how long the chart will be until we run the statement. This may cause problems if we guess wrongly and we are tight for space, so the next query addresses this issue by creating a chart of known length. To do this, it does the following:
Now for the code:
Make chart of fixed length
Multiple Counts in One Pass
The STATS table that is defined on page 116 has a SEX field with just two values, 'F' (for female) and 'M' (for male). To get a count of the rows by sex we can write the following:
Use GROUP BY to get counts
Imagine now that we wanted to get a count of the different sexes on the same line of output. One, not very efficient, way to get this answer is shown below. It involves scanning the data table twice (once for males, and once for females) then joining the result.
Use Common Table Expression to get counts
DB2 Using SQL Related Interview Questions
|PL/SQL Interview Questions||IBM DB2 Interview Questions|
|Oracle Interview Questions||COBOL Interview Questions|
|DB2 Using SQL Interview Questions||IBM Mainframe Interview Questions|
|MYSQL DBA Interview Questions||DB2 SQL Programming Interview Questions|
|IMS/DB Interview Questions||Mainframe DB2 Interview Questions|
Db2 Using Sql Tutorial
Introduction To Sql
Data Manipulation Language
User Defined Functions
Order By, Group By, And Having
Union, Intersect, And Except
Materialized Query Tables
Identity Columns And Sequences
Protecting Your Data
Retaining A Record
Using Sql To Make Sql
Running Sql Within Sql
Fun With Sql
Quirks In Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.