|
Tylogix - iSeries SQL Tips and Techniques
|
|
On how to find
duplicate records on iSeries using SQL, James from Kentucky
asked :
I am trying
to identify duplicate key fields within a file. What is the best way to do
this?
Answer:
Here is an
easy to follow technique.
Assuming a
table TABLE_X with column_1, column_2, and column_3, and assuming the aim is
to find all columns who happen to have exact duplicate combination of data
for these three columns: You would key, in the SQL command line the following
command:
SELECT COLUMN_1,
COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3
Go Back to the Top
|
|
Technique
to Remove Duplicates from a Table
To
remove duplicates from a table, assuming the table is keyed and that only the
greatest key value is the one to keep, this is the technique:
DELETE FROM
SAMPLETABLE
WHERE ID_NUMBER <
(
SELECT MAX(ID_NUMBER)
FROM SAMPLETABLE MAXVALUES
WHERE MAXVALUES.NAME = SAMPLETABLE.NAME
)
An
other
method, using the Relative Record Number (RRN, equivalent of Row ID in
Oracle) is as follows:
DELETE FROM
FILENAMEX H1
WHERE RRN(H1) <
(
SELECT MAX( RRN(H2) ) FROM FILENAMEX H2
WHERE H1.TRANSACTION_ID = H2.TRANSACTION_ID
)
Go Back to the Top
|
|
Technique
to extract ONLY n Rows from a table
To limit
the amount of results that a query may retrieve, the “FETCH FIRST N
ROWS ONLY” is a useful technique:
SELECT * FROM
TABLE_NAME
FETCH FIRST 10 ROWS ONLY
The last clause
ensures that only 10 rows are fetched.
Go Back to the Top
|
|
On how to manipulate data on
a remote system, Dan from California
wrote:
I work
with for a company who owns several systems, amongst which there are AS/400.
I can FTP from/to these systems but no DDM is allowed. Would there be a way
to manipulate data on a remote system using FTP?
Answer:
To
reach a remote site with SQL, you need to first connect and ensure the remote
site is in the system directory. This is explained in detail in the Tylogix
Remote SQL Technique page
The best
answer is perhaps not using FTP, even if a program can be remotely triggered
on a remote system using the FTP instruction "QUOTE" - (more on this in the FTP
Q&A Page).
Go Back to the Top
|
|
This SQL tip is the formula
to find a job name in IBM's Job Scheduler, based on the contents of its
command field:
This
SQL statement will find any JS job which has the string '583550'
imbedded in the command line.
You can
substitute '583550' with anything such as 'PGMABC' for example,
and find which JS Job runs that command.
SELECT T1.JMJOB,
T1.JMGRP, T1.JMSEQ, T1.JMTEXT, T2.CMCSEQ, T2.CMCMD
FROM QUSRIJS/QAIJSMST T1
JOIN QUSRIJS/QAIJS1CM T2 ON
(T1.JMJOB = T2.CMJOB AND T1.JMGRP = T2.CMGRP
AND T1.JMSEQ = T2.CMSEQ)
WHERE T2.CMCMD like '%583550%'
ORDER BY T1.JMGRP, T1.JMSEQ, T2.CMCSEQ
Go Back to the Top
|
|
This
SQL tip is the formula to find jobs that fail most frequently over time in
IBM's Job Scheduler:
This
query shows JS failures from the JS history file.
This is a method to look for areas for improvement by bringing scheduled jobs
that fail frequently over time.
SELECT
JMGRP,JMJOB, JMSEQ, COUNT(*) FROM QUSRIJS/QAIJSHST WHERE
SUBSTR(DIGITS((1900*10000)+JMLRD),7,2) >=
SUBSTR(DIGITS(YEAR(CURDATE() )-1),9,2) AND (JMLCC = 'A' OR JMLCC =
'G' OR JMLCC = 'T' OR JMLCC = 'L') AND (JMAPPL = 'MI' OR JMAPPL =
'DW') GROUP BY JMGRP, JMJOB , JMSEQ ORDER BY 4
DESC
Go Back to the Top
|
|
SQL
Join Basics
Joining data
from more than one table
Sometimes the
information you want to see is not in a single table. To form a row of the
result table, you might want to retrieve some column values from one table
and some column values from another table. You can retrieve and join column
values from two or more tables into a single row.
Several
different types of joins are supported by DB2 UDB for iSeries:
inner join, left outer join, right outer join, left exception join, right
exception join, and cross join.
- An Inner Join returns only the rows from each table that
have matching values in the join columns. Any rows that do not have a
match between the tables will not appear in the result table.
- A Left Outer Join returns values for all of the rows
from the first table (the table on the left) and the values from the second
table for the rows that match. Any rows that do not have a match in the
second table will return the null value for all columns from the second
table. Nulls can be monitored and be defaulted with an
IFNULL. For example: IFNULL(TABLEX.OBSERVATION_HR
, 0) will replace the value of
TABLEX.OBSERVATION_HR with a 0 if a NULL was detected.
- A Right Outer Join return values for all of the rows
from the second table (the table on the right) and the values from the
first table for the rows that match. Any rows that do not have a match
in the first table will return the null value for all columns from the
first table.
- A Left Exception Join returns only the rows from the
left table that do not have a match in the right table. Columns in the
result table that come from the right table have the null value.
- A Right Exception Join returns only the rows from the
right table that do not have a match in the left table. Columns in the
result table that come from the left table have the null value.
- A Cross Join returns a row in the result table for each
combination of rows from the tables being joined (a Cartesian Product).
- A Simulating a Full Outer Join like the left and right
outer joins, a full outer join returns matching rows from both tables.
However, a full outer join also returns non-matching rows from both
tables; left and right.
- Using Multiple Join Types in One Statement There are times
when more than two tables need to be joined to produce the desired
result. This is an example of how to proceed.
You can
simulate a Full Outer Join using a Left Outer join and a Right Exception
Join. See Simulating a Full Outer Join for details. Additionally,
you can use multiple join types in a single statement. See Multiple join types in one statement for details.
Notes
on joins
When you
join two or more tables:
- If there are common column names, you must qualify
each common name with the name of the table (or a correlation name).
Column names that are unique do not need to be qualified.
- If you do not list the column names you want, but
instead use SELECT *, SQL returns rows that consist of all the columns
of the first table, followed by all the columns of the second table, and
so on.
Go Back to the Top
|
|
Casting
Data Types with SQL
Sometimes,
it may be convenient to compare data which is stored in different data types.
This is where casting comes in:
To cast alpha data as numeric, use the following formula. Note that this
example also has a substring built-in:
SELECT @iNumValue = INT( SUBSTRING(CharColumnName,
StartChar, Length)) from filename
To cast
numeric data as alpha, use the following formula:
SELECT @icharValue = CAST( IntegerColumn,
as char(5)) from filename
(5 in
this example is the length of the character string to convert to)
Sometimes
the information you want to see is not in a single table. To form a row of
the result table, you might want to retrieve some column values from one
table and some column values from another table. You can retrieve and join
column values from
An other useful data manipulator I like is the SQL
keyword STRIP, which takes only the non-blank characters out of a character
column. This is especially useful if you are concatenating values like
FIRST_NAME and LAST_NAME. If each of these columns have 50 charaters, you end up with something that does not
look very readable.
Using STRIP(FIRST_NAME) || ' ' || STRIP(LAST_NAME), you can get
something relatively normal to read.
Go Back to the Top
|
|
Techniques to convert
JD Edwards Julian Dates to MDY and Back using SQL
JD
Edwards stores dates in a particular format:
- first digit is a 0 for dates prior to Y2K and 1 for dates after Y2K
- second and third digits are the last two numbers of a year i.e. for 2003
this value will be "03"
- third, fourth and fifth digits are the julian day
(nth day of the year).
While this
works well for a computer, it is not very readable by people. This can be
easily changed for reading and reporting with SQL by using the following SQL
formula, here is an example that can be used interactively with
SQL: SELECT
date(char(1900000+sdupmj)) from f4211
-------------------------------------------------------------------------------------------
Here is
an SQL function to convert a JDE Julian Date to a MDY date:
Calling
example:
SELECT
XJDETOMDY(105144) FROM SYSIBM/SYSDUMMY1
SQL Code:
CREATE
FUNCTION XJDETOMDY (IN_JDE_DATE INT)
RETURNS
DATE
LANGUAGE
SQL
BEGIN
DECLARE OUT_YMD DATE
;
SET OUT_YMD =
DATE(CHAR(1900000+IN_JDE_DATE)) ;
RETURN
(OUT_YMD)
;
END
For
reference and comparison, in Excel, the Julian to Gregorian conversion code
is as follows:
A B
====== ===========
1 Julian
Gregorian
2
108100 04/09/2008
The contents of the cells A2 and B2 are:
108100 =DATE(1900+INT(A2/1000),1,MOD(A2,1000))
Here is
an SQL function to convert a MDY Date to a JDE Date
Calling
example:
SELECT
XMDYTOJDE(DATE('05/24/05')) FROM SYSIBM/SYSDUMMY1
SQL Code:
CREATE
FUNCTION XMDYTOJDE (IN_YMD_DATE DATE)
RETURNS
INT
LANGUAGE
SQL
BEGIN
DECLARE OUT_JDE_DATE
INT
;
DECLARE OUT_JDE_PART1 CHAR(1)
;
DECLARE OUT_JDE_PART2 CHAR(2)
;
DECLARE OUT_JDE_PART3I INT
;
DECLARE OUT_JDE_PART3C CHAR(3)
;
CASE
WHEN IN_YMD_DATE <
DATE('01/01/2000')
THEN SET OUT_JDE_PART1 =
'0' ;
ELSE
SET OUT_JDE_PART1 =
'1'
;
END CASE
;
SET OUT_JDE_PART2 =
SUBSTR(CHAR(IN_YMD_DATE), 7,2)
;
SET OUT_JDE_PART3I =
DAYS(IN_YMD_DATE)
-
DAYS( DATE('01/01/' ||
OUT_JDE_PART2 ) )Ư
;
CASE
WHEN OUT_JDE_PART3I <
10
THEN SET OUT_JDE_PART3C = '00' ||
CHAR(OUT_JDE_PART3I)
;
WHEN OUT_JDE_PART3I <
100
THEN SET OUT_JDE_PART3C =
'0' || CHAR(OUT_JDE_PART3I)
;
ELSE
SET OUT_JDE_PART3C = CHAR(OUT_JDE_PART3I)
;
END CASE
;
SET OUT_JDE_DATE = INT(OUT_JDE_PART1 || OUT_JDE_PART2 ||
OUT_JDE_PART3C ) ;
RETURN (OUT_JDE_DATE)
;
END
Technique to convert for Gregorian to Julian Dates (JDE
format) using SQL
CASE WHEN SUBSTR(
CHAR( YEAR( DATE('2009-03-19') ) ) , 1, 1) = '1'
THEN '0' ELSE '1' END ||
SUBSTR( CHAR( YEAR(DATE('2009-03-19')) * 1000
+ DAYOFYEAR(DATE('2009-03-19')) ) , 3, 5)
|
|
What is the DB2 equivalent of
Oracle's DUAL?
The
name of the file on DB2 is sysibm/sysdummy1
A bit less handy than "DUAL", but it works the same. Here are two
examples with dates.
SELECT char(
DATE(timestamp( '2005-12-31-23.59.59.000000'))) FROM sysibm/sysdummy1
12/31/05
SELECT char(
DATE(timestamp( '2005-12-31-23.59.59.000000') +7 days )) FROM sysibm/sysdummy1
01/07/06
Go Back to the Top
|
|
What
are the SQL data type equivalents in C, RPG and COBOL?
The data type equivalence tables can be found in the following pages:
SQL and C Types
SQL and RPG Types
SQL and COBOL Types
Go Back to the Top
|
|
Defining
an iSeries table with long field names and
10-character field references
Here is an example:
CREATE TABLE
TESTPF
(
DOCUMENT_COMPANY FOR GLKCO CHAR(10) ,
DOCUMENT_TYPE FOR GLDCT CHAR(2) ,
DOCUMENT_NUMBER FOR GLDOC DEC(8,0)
);
--
LABEL ON TESTPF (DOCUMENT_COMPANY TEXT IS
'Document Company ');
LABEL ON TESTPF (DOCUMENT_TYPE TEXT IS
'Document Type ');
LABEL ON TESTPF (DOCUMENT_NUMBER TEXT IS
'Document Number ');
--
LABEL ON TABLE TESTPF IS 'Example Table';
Go Back to the Top
|
|
How
to run an SQL Statement from a CL Program
1) First
Enter your SQL Statement in a source member
2) Use the command RUNSQLSTM with the member name
3) An other solution may be to implement and use the EXCSQL
command, authored by Dan Riehl, posted on this
site.
Go Back to the Top
|
|
Creating an Integrity Check
between two Tables Using SQL
This code shows
how two files can be scanned for differences using SQL.
The equivalent can be achived with CMPPFM, but the
SQL code is portable.
------------------------------------------------------------------------------
--
Delete ALL rows from Integrity Result Table
INTRESULT
------------------------------------------------------------------------------
DELETE FROM INTRESULT
;
------------------------------------------------------------------------------
--
Insert the rows that are
different.
------------------------------------------------------------------------------
INSERT INTO
INTRESULT
(
CAT_CODE_2
,
COIND
,
OBJECT_ACCOUNT
,
RECORD_COUNT_DIFFERENCE
,
SUM_DIFFERENCE
,
COMMENT
)
SELECT
BASETABLE.CAT_CODE_2
,
BASETABLE.COIND
,
BASETABLE.OBJECT_ACCOUNT
,
BASETABLE.SOURCE_RECORD_COUNT
- NEWTABLE.TARGET_RECORD_COUNT
,
BASETABLE.SOURCE_SUM
-
NEWTABLE.TARGET_SUM
,
'Differences between Base Table and Source and
New Table Totals'
FROM
BASETABLE
JOIN
NEWTABLE
ON BASETABLE.CAT_CODE_2
=
NEWTABLE.CAT_CODE_2
AND
BASETABLE.COIND
=
NEWTABLE.COIND
AND
BASETABLE.OBJECT_ACCOUNT
=
NEWTABLE.OBJECT_ACCOUNT
where BASETABLE.SOURCE_RECORD_COUNT
<> NEWTABLE.TARGET_RECORD_COUNT
or
BASETABLE.SOURCE_SUM
<> NEWTABLE.TARGET_SUM
;
------------------------------------------------------------------------------
-- Insert the rows that are in BASETABLE but are not in
NEWTABLE
------------------------------------------------------------------------------
INSERT INTO
INTRESULT
(
CAT_CODE_2
,
COIND
,
OBJECT_ACCOUNT
,
RECORD_COUNT_DIFFERENCE
,
SUM_DIFFERENCE
,
COMMENT
)
SELECT
BASETABLE.CAT_CODE_2
,
BASETABLE.COIND
,
BASETABLE.OBJECT_ACCOUNT
,
BASETABLE.SOURCE_RECORD_COUNT
,
BASETABLE.SOURCE_SUM
,
'Records in BASETABLE (Source) but not in
NEWTABLE(Target)'
FROM
BASETABLE
LEFT EXCEPTION JOIN
NEWTABLE
ON
BASETABLE.CAT_CODE_2 =
NEWTABLE.CAT_CODE_2
AND
BASETABLE.COIND
=
NEWTABLE.COIND
AND BASETABLE.OBJECT_ACCOUNT =
NEWTABLE.OBJECT_ACCOUNT
;
------------------------------------------------------------------------------
-- Insert the rows that are in NEWTABLE but are not in
BASETABLE
------------------------------------------------------------------------------
INSERT INTO
INTRESULT
(
CAT_CODE_2
,
COIND
,
OBJECT_ACCOUNT
,
RECORD_COUNT_DIFFERENCE
,
SUM_DIFFERENCE
,
COMMENT
)
SELECT
NEWTABLE.CAT_CODE_2
,
NEWTABLE.COIND
,
NEWTABLE.OBJECT_ACCOUNT
,
NEWTABLE.TARGET_RECORD_COUNT
,
NEWTABLE.TARGET_SUM
,
'Records in NEWTABLE (Data Warehouse summary)
but not in BASETABLE (Source)'
FROM
NEWTABLE
LEFT EXCEPTION JOIN
BASETABLE
ON
NEWTABLE.CAT_CODE_2 =
BASETABLE.CAT_CODE_2
AND
NEWTABLE.COIND
=
BASETABLE.COIND
AND NEWTABLE.OBJECT_ACCOUNT =
BASETABLE.OBJECT_ACCOUNT
;
Go Back to the Top
|
|
Example of a simple text parsing Stored Procedure
This link
points to the source for a simple text parser, created as an SQL Stored
Procedure.
The challenge was to get item description data that was relatively well structured,
but in Microsoft Excel format, into a table.
There was more than one description sentences per item and we needed to have
each item description sentence split in discrete rows.
It all sounds simple, but we had to
- replace manual line feeds with a special stand-in character (we chose
"~") to avoid un-wanted line breaks during FTP transfers (we had to
use MS Word for that)
- FTP the data
- Parse out the data so that it could be accepted in that particular format
at the other end.
The procedure uses two nested cursors.
Here
is the link to the Text-Parsing Stored Procedure Example.
Go Back to the Top
|
|
Debugging an SQL Stored Procedure
Debugging
a Stored Procedure is very much like debugging any other ILE C program:
Consider a
simple SQL Stored Procedure that uses a cursor to update each row in a table
with a unique time stamp (The example code is supplied below). The way to
create the procedure in a way that will allow it to be debugged is as
follows:
RUNSQLSTM
SRCFILE(OBJLIB/PROCEDURES) SRCMBR(UPDT_TIMEz)
DBGVIEW(*SOURCE)
Note: DBGVIEW(*SOURCE) will step
through the SQL code that you wrote. Easier to debug that way.
DBGVIEW(*LIST) will step though
the ILE C program generated by the system to wrap around your SQL code.
Interesting to visit but harder to debug.
To start
the debugging process, use the following expression:
STRDBG
PGM(OBJLIB/UPDT_TIMEZ) UPDPROD(*YES) OPMSRC(*YES) DSPMODSRC(*YES)
This will
bring up the source of the C program created by the compiler to run the SQL
procedure. Hit F10 without entering anything else.
Now, start SQL with the command STRSQL
Within the SQL command line, call the stored procedure, exactly as if you
were calling it from a normal command line. The syntax is the same. Note
however that if you called a stored procedure directly from the command line,
it would crash. Stored procedures have to be called either from an STRSQL
screen, or from a member executed with a STRSQLSTM or even from a QMQRY
member.
CALL
OBJLIB/SAMPLEPGM
Now, the
program will start in debug mode and you can step (F10) through it, like any
other C program. In addition, if you look at your joblog,
you will see what choices the SQL Optimizer has made. (see the SQL Optimizer section in this page for
the details)
Here is a
sample procedure, using a cursor. It updates each record with a distinct
timestamp.
--------------------------------------------------------------------------------
--
TEST SQL
PROCEDURE
--------------------------------------------------------------------------------
CREATE PROCEDURE
UPDT_TIMEZ
LANGUAGE
SQL
-- START
PROCEDURE
BEGIN
-- DECLARE CURSOR
VARIABLES
DECLARE
PUBLISH_TMS
TIMESTAMP
;
DECLARE
WORK_TIMESTAMP TIMESTAMP
;
DECLARE
SQLSTATE
CHAR(5) DEFAULT '00000'
;
DECLARE
AT_END
INT DEFAULT 0
;
DECLARE
SQLCODE
INT DEFAULT 0
;
DECLARE CURSOR_UPD CURSOR
FOR
SELECT PUBLISH_TMS FROM ER400SX
MAIN;
SET
AT_END =
0;
OPEN CURSOR_UPD
;
FETCH_LOOP:
LOOP
FETCH CURSOR_UPD INTO WORK_TIMESTAMP
;
IF SQLCODE = 0 AND SQLSTATE =
'00000' THEN
UPDATE
ER400SX
SET PUBLISH_TMS = CURRENT TIMESTAMP, TIME_ELAPSED =
DAY(CURRENT_TIME_STAMP –
WORK_TIMESTAMP)
WHERE CURRENT OF
CURSOR_UPD
;
ELSE
SET AT_END = 1
;
LEAVE FETCH_LOOP
;
END IF
;
END LOOP
;
CLOSE CURSOR_UPD
;
END
Go Back to the Top |
|
Debugging Stored Procedures: SQLCODE,
Variables and DIAGNOSTICS
The variable
SQLCODE is actually a sub-field of a data structure named SQLCA.
Here are the steps to debug it:
1) Start your debugger
with the following instruction:
STRDBG PGM(PGMNAME)
UPDPROD(*YES)
The
debugger will then show the source.
Hit F10.
The debugger will leave the source instantly.
2) Call your
program:
CALL
PGM(PGMNAME)
- The
debugger will now start for real. Hit
- F10 to step through a function
- F22 to Step INTO a function and see each line or component going through
3)To
see the value of SQLCODE in V5R2, use the following command:
EVAL sqlca (Make sure that
"sqlca" is in lower case)
This
instruction will display the entire SQLCA data structure, SQLCODE is actually
a sub-field of the SQLCA data structure. To interpret the code, go to the IBM
SQL Website.Within
that page is a search field. Search for the string "SQL messages and
codes". A search result box will appear. Hit the first link, "SQL
messages and codes". Within the resulting page, look for the link saying
"SQL Message Finder" Hit that link. This is a good place to start
investigating.
4) Getting Diagnostic
information from SQL
DECLARE SQLCODE INT
DEFAULT 0 ;
DECLARE SQL_EXCEPTION INT ;
DECLARE
RECORD_COUNT INT DEFAULT 0 ;
DECLARE CONTINUE HANDLER FOR
SQLEXCEPTION SET SQL_EXCEPTION = 1 ;
GET DIAGNOSTICS RECORD_COUNT
= ROW_COUNT
;
SET INSERT_COUNT_CM999 =
INSERT_COUNT_CM999 + RECORD_COUNT ;
To get all the
possible debugging and diagnostic values available through the GET DIAGNOSTIC
SQL verb, go to : http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/index.htm?info/db2/rbafzmstincludesqlca.htm
5)To see the value
of a character string in a C/400 debug session, use the following formula:
EVAL
*variablename :s 12 (where
12 is the length of the string you wish to see)
6)To see the value
of ALL LOCAL VARIABLES in your C/400 debug session, use the following
formula:
EVAL
%LOCALVARS
Go Back to the Top
|
|
Deleting
Duplicate Records from a Table Using SQL
A simple Correlated Delete Example Using SQL. The value
of "select 1" in this situation is really there to verify that the
"where exist" condition is true.
DELETE FROM MAIN_TABLE MAIN
WHERE EXISTS
(SELECT 1 FROM UPDATE_TABLE UPDT
WHERE MAIN.KEY_ID=UPDT.KEY_ID)
A good way to
test the number of rows to be affected by this delete would be to replace the
first line with
SELECT
COUNT(1)FROM MAIN_TABLE MAIN
In most cases using 1 will produce the same result as
using *. The only time it may differ is the situation where all fields in a
record are NULL. A better illustration of the utility of using a 1 is to
compare the results with a query that uses a specific field that sometimes
contains NULL values.
A Correlated Delete Example Using other conditions with
the correlation
DELETE FROM
CONTACT_TABLE AD1
WHERE AD1.ID_NUMBER <
(
SELECT MAX(AD2.ID_NUMBER)
FROM CONTACT_TABLE AD2
WHERE
(
AD1.ADDRESS_1 = AD2.ADDRESS_1 AND
AD1.ADDRESS_2 = AD2.ADDRESS_2 AND
AD1.ADDRESS_3 = AD2.ADDRESS_3
)
Go Back to the Top
|
|
Joining with Unreliable
Numeric Data Stored in a Character Variable
Up until now (V5R3), SQL
on the iSeries has no built-in method to determine if a character
column contains valid numeric data. Here is a simple way to do this, using
the keyword LOCATE. LOCATE determines the position of a string within an other string. If it is not found a zero is returned.
Using this technique, one can compare each individual position of the column
and figure out if the data is numeric or not, and in the latter case assing a default numeric value. The result
of this case can be used to do a reliable join with a numeric key.
Example 1 - strictly numeric data is acceptable:
LEFT OUTER JOIN
FAMRFCUS FCUS ON
CASE
WHEN
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 22, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 23, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 24, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 25, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 26, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA
, 27, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 28, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 29, 1), '0123456789') = 0
OR
LOCATE(SUBSTR(WEBLOG_RAW_DATA , 30, 1), '0123456789') = 0
THEN 0
ELSE DEC(
SUBSTR(WEBLOG_RAW_DATA, 22, 9) )
END
=
FCUS.RFID_CUSTOMER_ID
Example 2 - Allows for
blanks :
SELECT
* FROM FILEAA AA
LEFT OUTER JOIN
FILEBB BB
ON
CASE WHEN
(
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,1,
1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,2, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,3, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,4, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,5, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,6, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,7, 1),' 0123456789')<>0
AND
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,8, 1),' 0123456789')<>0
)
THEN
INT(AA.PURCHASE_ORDER_NUMBER)
ELSE 0
END
=
BB.NUMERIC_PO_NUMBER
Go Back to the Top
|
|
DB2 SQLCaveat! Left Outer Join with a Where
clause on the Outer File Causes Inner Join
This is a "DB2
FEATURE": When using Left Outer Join with a WHERE on the outer joined
table, the join actually turns itself into an INNER JOIN. Here is an example
of this "feature":
This is the left table:
==============================================================================================
SELECT * FROM
LEFT_TABLE
LEFT_PK
RIGHT_FK
DESC
1
3 left row 1
2
4 left row 2
3
0 left row 3
**
End of data
********
This is the right table:
==============================================================================================
SELECT *
FROM RIGHT_TABLE
RIGHT_PK
DESC
1 right row 1
2 right row 2
3 right row 3
4 right row 4
***
End of data ******
This is a select from the
Right table that yields 100% of the results
==============================================================================================
SELECT * FROM
RIGHT_TABLE
WHERE RIGHT_TABLE.DESC
!= 'HELLO'
RIGHT_PK
DESC
1 right row 1
2 right row 2
3 right row 3
4 right row 4
***
End of data ******
Query that produces a proper
left outer join - note the nulls on the third row result:
==============================================================================================
SELECT * FROM
LEFT_TABLE LEFT OUTER JOIN RIGHT_TABLE
ON RIGHT_FK =
RIGHT_PK
LEFT_PK
RIGHT_FK DESC
RIGHT_PK
DESC
1 3 left row
1 3 right
row 3
2 4 left row
2 4 right
row 4
3 0 left row
3 -
-
***
End of data
********
Identical query but contains a
condition using the right table:
==============================================================================================
SELECT * FROM
LEFT_TABLE LEFT OUTER JOIN RIGHT_TABLE
ON RIGHT_FK =
RIGHT_PK WHERE RIGHT_TABLE.DESC !=
'HELLO'
LEFT_PK
RIGHT_FK
DESC RIGHT_PK
DESC
1 3
left row 1
3 right row
3
2 4
left row 2 4
right row 4
***
End of data
********
Go Back to the Top
|
|
Method to simulate a
"right-justify" for character data using SQL
There is no
"right-justify" string function built into SQL, but it can still be
done: Taking a character string of variable size and right-justifying within
a target column
containing a maximum of 12 characters. The example
below does a bit more but it illustrates well what can be done, effectively
concatenating blanks (up to 12) for a length of 12 - the length of the source
column.
INSERT INTO
TARGET_TABLE
SITE_CODE_RIGHT_JUSTIFED
SELECT
SUBSTR(' ', 1, 12 -
LENGTH( TRIM( CHAR_COLUMN_NAME)))
|| TRIM( CHAR_COLUMN_NAME )
FROM TABLE
FROM SOURCE_DATA
Go Back to the Top
|
|
Executing Correlated Deletes
using SQL and a correlated sub-select
Using SQL to do
Correlated deletes - Take note of the two WHERE clauses:
(DELETE FROM
EMPLOYEE_TABLE EM
WHERE EXISTS
(SELECT * FROM UPDATE_TABLE UPDT WHERE UPDT.ID = EM.ID);
(Note: this
technique - using a join as opposed to a correlated sub-select - is
available in some SQL flavors, such as MYSQL but NOT
YET in DB2 for iSeries)
Go
Back to the Top
|
|
Executing Correlated Updates using SQL on the iSeries – you have to use a correlated
sub-select
Using SQL to do
Correlated updates - Take note of the two WHERE clauses:
UPDATE
EMPLOYEE_TABLE EM
SET
(EM.PAY_SCALE, EM.SALARY) =
(SELECT
NPAY.PAY_SCALE, NPAY.SALARY FROM NEW_PAY NPAY WHERE NPAY.ID = EM.ID )
WHERE EXISTS
(SELECT *
FROM NEW_NEWPAY
NPAY WHERE NPAY.ID = EM.ID )
If there are more than one row
matching the update criteria, using the “DISTINCT”
clause may be a valid solution, as it will bring back only one row:
UPDATE
EACPPOMATH
SET PO_IN_GR =
(
SELECT DISTINCT
PO_REFERENCE_NUMBER
FROM EACPLGR5AR
WHERE LEGACY_PO_NUMBER = PO_REFERENCE_NUMBER
)
WHERE EXISTS
(
SELECT *
FROM EACPLGR5AR
WHERE LEGACY_PO_NUMBER = PO_REFERENCE_NUMBER
)
The following method is not
yet available on DB2/400 but it is pretty neat and works on other databases:
Using SQL to do
Correlated updates can be done in two ways: The first one is using an inner
join, the second one can be done using a correlated sub-select.
(Note: this technique - using a join as opposed to a
correlated sub-select - is available in some SQL flavors,
such as MYSQL but NOT YET in DB2 for iSeries)
The inner join technique
effectively says something like update the set of data as it come in from two
sources joined by a key:
UPDATE
EMP_TABLE1 INNER JOIN EMP_TABLE2
ON EMP_TABLE1.EMP_NBR = EMP_TABLE2.EMP_NBR
SET EMP_TABLE1.BENEFIT_NBR = EMP_TABLE2.BENEFIT_NBR
WHERE EMP_TABLE1.BENEFIT_NBR <> EMP_TABLE2.BENEFIT_NBR
--------------------------------------------------------------------------------
-- An other
example:
-- Double
Correlation Update, to update only the largest date for
-- a given set
of rows determined by a key in a secondary table.
--------------------------------------------------------------------------------
UPDATE FBSNHR
HOURS_MAIN
SET
HOURS_MAIN.EFCTV_TO_DTE = DATE('4000-01-01')
WHERE EXISTS
( SELECT
'YES' FROM HOURSFIX FIX
WHERE STRIP(FIX.FIXSTORE) = STRIP(HOURS_MAIN.BUSINESS_UNIT_NBR)
AND
HOURS_MAIN.EFCTV_TO_DTE =
( SELECT MAX(HOURS_SUB.EFCTV_TO_DTE) FROM FBSNHR HOURS_SUB
WHERE STRIP(HOURS_SUB.BUSINESS_UNIT_NBR) = STRIP(FIX.FIXSTORE)
)
)
Go
Back to the Top
|
|
Finding where (in which library) a DDL-built table
with a long name may be
To find the
library where a long-name (SQL naming convention) table may be on the system,
use the following query in the Catalog Table
SYSTABLES:
SELECT
TABLE_NAME,
TABLE_SCHEMA FROM QSYS2/SYSTABLES
WHERE TABLE_NAME
= 'MONTH_TO_DATE_SALES';
Go Back
to the Top
|
|
Targeting one or more members
in a multi-member file using SQL using ALIAS and UNION
To
gather the data from multiple members in an iSeries table, here is a technique
that uses ALIAS to target individual members and UNION
to retrieve the distinct rows:
CREATE
ALIAS LIBRARY1/SALES_HIST_1999
FOR
LIBRARY1/SALESHIST(MBR_HST_99)
CREATE
ALIAS LIBRARY1/SALES_HIST_2000
FOR LIBRARY1/SALESHIST(MBR_HST_00)
SELECT
* FROM LIBRARY1/SALES_HIST_1999
UNION
SELECT * FROM
LIBRARY1/SALES_HIST_2000
Go Back to the Top
|
|
Converting local TIMESTAMP value to and from a GMT
TIMESTAMP
Here are some
handy timestamp routines, including a Greenwitch Mean Time (GMT) conversion:
TIME Retrieval using CURTIME function
SELECT curtime()
FROM sysibm/sysdummy1
DATE Retrieval
using CURDATE function
SELECT curdate()
FROM sysibm/sysdummy1
CURRENT TIMESTAMP
Retrieval using NOW function
SELECT now() FROM sysibm/sysdummy1
GMT TIMESTAMP
using NOW and TIMEZONE
select now()- current timezone from sysibm/sysdummy1
Go
Back to the Top
|
|
Exploring the DB2 Catalog
All the
Database Objects in DB2 are classified and organized with a link in the DB2 Catalog. The DB2 Catalog tables
all start with SYS* and they all reside in library QS2. Examples of the most
well known DB2 Catalog tables are:
Catalog Table Description
SYSCOLUMNS: Columns
SYSCST: Constraints
SYSFUNCS: Functions
SYSINDEXES: Indexes
SYSKEYS: Keys
SYSPROCS: Procedures
SYSTABLES: Tables
SYSTRIGGER: Triggers
SYSVIEWS: Views
Example of the
use of the DB2 Catalog: "Where is this column
(field) name used?" :
SELECT * FROM QSYS2/SYSCOLUMNS
WHERE COLUMN_NAME LIKE '%MCU%'
Go Back to the Top
|
|
Global Character or String
Replace in SQL
Global character
or string replacement is very simple with SQL using the keyword REPLACE.
In this example this technique
is used to replace individual HEX Characters in a table.
Specificaly, a carriage-return special character is replaced with a
"|" character.
UPDATE
DATA_TABLE SET DESCRIPTION = REPLACE( DESCRIPTION , X'25' , X'4F' )
The same technique can be
used to replace strings:
UPDATE DATA_TABLE SET DESCRIPTION = REPLACE( DESCRIPTION , 'THIBAULT' ,
'Thibault' )
Go Back to the Top
|
|
Creating Characters by
Specifying the Hexadecimal Value in SQL
If necessary, one
can enter HEX characters directly while using SQL. This may be practical when
dealing with accents and foreign languages for example: HEX(43) is Ñ.
INSERT INTO
SAMPLE_TABLE
SAMPLE_COLUMN
SELECT
HEX(43)
FROM SYSIBM/SYSDUMMY1
Go Back to the Top
|
|
Finding (locating) Hexadecimal
Values in SQL
Locating a
special character, like a carriage return or any other un-printable character
is relatively easy with SQL.
This is especially handy when sub-stringing or parsing
unstructured data. Here is an example:
SELECT
LOCATE(X'25', GDTXVC,
1), -- show
the position
SUBSTR(GDTXVC, 1, LOCATE(X'25', GDTXVC, 1)) -- substring from 1 to location
FROM F00165
WHERE LOCATE(X'25', GDTXVC, 1) <
50 -- only if location is
< 50
positions
FROM SYSIBM/SYSDUMMY1
Go
Back to the Top
|
|
Technique to Call Programs or
Execute CL Commands from SQL
The
technique is simple: Simply call QCMDEXC, add the command as a parameter and
the length of the command as a subsequent parameter, with the lenght in the numeric format shown
below. For example, here is an override, followed by a call:
CALL QCMDEXC('OVRDBF
FROMFILE(FILEA) TOFILE(FILEB)',0000000036.00000);
or
CALL
QCMDEXC('CALL TESTLIB/TESTCLPGM',0000000022.00000);
Go Back to the Top
|
|
Difference between a Stored
Procedure and a Function
Stored Procedures have
the following charactetistics:
- They can accept n parameters
- They compile as an object of type *PGM, which has to
be called from an SQL environment to work
- They can return n parameter values
- Best use for Stored Procedures: when one needs to do
record-by-record processing - which can be done using a cursor
Functions have the
following charactetistics:
- They can accept n parameters
- They compile as an object of type *SRVPGM which has
to be called from an SQL environment to work
- WILL RETURN 1 AND ONLY 1 VALUE
- Best use for functions: For simple, often repeated,
formula-intensive calculations
Go Back to the Top
|
|
Method
to find the SQL Optimizer suggestions to improve program or stored procedure
SQL performance
If you
find your SQL is sluggish and you don't know why, you may want to know what
the Operating System - or more specifically the SQL optimizer - is thinking.
This is possible by using the debugger. For a given SQL routine that is too
slow and that you wish to analyze, do the following:
Step 1: If this is an individual SQL statement, cut and paste your SQL into a
QMQRY member. If this is a stored procedure, go to step 2.
Step 2: Start Debug using STRDBG UPDPROD(*YES) CHGJOB LOG(4 4 *SECLVL)- this
will change the job to record all activities and second level text. With
*SECLVL, both the message text and the message help (cause and recovery) of
the error message are written to the job log.
Step 3: If we are dealing now with a QM member, run the freshly created QM
(STRQM and hit 9 on your new member), otherwise if it is a stored procedure,
call the stored procedure from within an SQL environment (STRSQL and do
"CALL LIBNAM/PROC_NAME" from the SQL command line or RUNSQLSTM of a
member containing "CALL LIBNAM/PROC_NAME")
Step 4: Do a WRKJOB and look at the joblog. Look
for
**** Starting
optimizer debug message for query and Access path suggestion for file
(Note: DB2 Optimizer suggestions may or may not appear)
These messages will show you what the SQL optimizer is looking for and the
suggestions it may have made for you to improve the performance of your SQL
code.
Typically the Optimizer will suggest indexes, with precise key orders.
Go Back to the Top
|
|
Technique to
generate a Surrogate Key using the Current Time Stamp as a base
Of all
possible key formats, the fastest ones are single-column, numeric keys. When
a natural key is too long, to improve performance, using a unique numeric
surrogate key to represent a unique natural key is a common technique. The
question is: how does one reliably generate surrogate keys on the fly when
writing a new row in a table? The solution below is both simple and easy. It
relies on the fact that a timestamp on iSeries is
accurate to one millionth of a second. It is therefore a reliable source for
a unique surrogate key. The code below shows how one can use the timetamp to get a unique number, essentially converting
the time stamp to a numeric value:
SELECT
SUBSTR(CHAR(NOW()), 1, 4) || SUBSTR(CHAR(NOW()), 6, 2) ||
SUBSTR(CHAR(NOW()), 9, 2) || SUBSTR(CHAR(NOW()),12, 2) ||
SUBSTR(CHAR(NOW()),15, 2) || SUBSTR(CHAR(NOW()),18, 2) ||
SUBSTR(CHAR(NOW()),21, 6)
FROM SYSIBM/SYSDUMMY1
Go
Back to the Top
|
|
Aggregating Aggregates SQL
Technique
Aggregate the
result of multiple count(*) records: Note how the “TOTALDUPS”
variable is used to name the COUNT(*) result, and further used to total up
all duplicates.
Note also the “COUNT(*) -1” notation. The result of this
operation only counts the duplicates (i.e. if there were two identical
records, one would be deemed valid and the second one would be deemed to be
the duplicate).
SELECT SUM( TOTALDUPS
)
FROM (
SELECT POS.COIND, POS.ILITM, POS.ILMCU, COUNT(*) -1 TOTALDUPS
FROM POS_TABLE POS
GROUP BY POS.COIND, POS.ILITM, POS.ILMCU
HAVING COUNT(*) >2
) TABLEX
SUM ( TOTALDUPS
)
249
Combine
multiple techniques using UNION ALL: Notice the use of “zero and
ONHAND” and then “zero and ORDERED”, which enables the UNION
– as in the two sets are identical. Now we can get the sum of orders
and the matching sum of on-hands (two figures from two different tables).
SELECT SKU,
SUM(ORDERED) UNION_ORD ,SUM(ONHAND)
UNION_OH
FROM
(
SELECT ORDSKU SKU, ORDQTY
ORDERED, 0 ONHAND
FROM
ORDERS
UNION ALL
SELECT INVSKU SKU, 0
ORDERED, INVOHQ ONHAND
FROM INVENTORY
)
TABLEX
GROUP
BY
SKU
HAVING
SUM(UNION_ORD) > SUM(UNION_OH)
SKU UNION_ORD
UNION_OH
215996AA 274.0000
0.0000
215996LB 12.0000
0.0000
51789764 720.0000 242.0000
Go
Back to the Top
|
|
Technique to insert NULL FROM
SELECT data statement as a source:
Technique to
insert NULL data when a SELECT is used for the source of the insert: Notice
the cast of the NULL, this is the feature that enables this technique. The
other method is to completely omit the content of TARGET_COL2 in the SELECT,
but this method is more explicit, deliberate and easier to read.
INSERT INTO TARGET_TABLE
(
TARGET_COL1,
TARGET_COL2
)
SELECT SRCTBL.START_DATE,
CAST(NULL AS NUMERIC)
FROM SOURCE_TABLE SRCTBL
Go Back to the Top
|
|
Technique to enable
self-referential, previous record updating current record:
This
technique enables a self-referential update that is sequential – i.e.
record x with a key of SITE_CODE, PRODUCT, START_TIMESTAMP. In this
process, each row in table SITE_PROD_DAT_TABLE needs to be updated with a
value contained in the immediate preceding row with the same SITE_CODE and PRODUCT.
To do this, the SITE_PROD_DAT_TABLE is correlated three times in the same SQL
statement:
- The MAIN
correlation will be the target of the UPDATE
- The UPDTDTA
correlation will get the value which will be used to update MAIN (actually,
this is a subtraction update, if the value is not found, the IFNULL will
subtract itself and leave a zero value in MAIN)
- The MAXBELOWKEY
will get the maximum date below the current row with a key that matches
SITE_CODE and PRODUCT
Note that it is CRITICAL to have good indexes to get good performance on this
type of join. For this statement, the look-back period is only 21 days, which
also helps limit the amount of searching. The index for SITE_PROD_DAT_TABLE is by order of
cardinality: site_code, product, start_timestamp.
UPDATE SITE_PROD_DAT_TABLE
MAIN
SET MAIN.price_delta
=
MAIN.value – IFNULL
(
( SELECT UPDTDATA.value
FROM SITE_PROD_DAT_TABLE
UPDTDATA
WHERE UPDTDATA.site_code
= MAIN.site_code
AND UPDTDATA.product
= MAIN.product
AND UPDTDATA.start_timestamp
=
(
SELECT max(MAXBELOWKEY.start_timestamp)
FROM SITE_PROD_DAT_TABLE MAXBELOWKEY
WHERE MAXBELOWKEY.site_code = MAIN.site_code
AND MAXBELOWKEY.product = MAIN.product
AND MAXBELOWKEY.start_timestamp >= current date
- 21 days
AND
MAXBELOWKEY.start_timestamp < MAIN.start_timestamp
)
)
, MAIN.value
)
WHERE MAIN.start_timestamp
>= current date - 21 days
Go Back to the Top
|
|
My
SQL Catalog appears to be out of sync – what
can I do?
I stumbled
across a situation where I had a unique key on a table but could not find any
constraint for this table in the QSYS2/SYSCST.
Answer: There can
be more than one cause:
1) If the unique
key was created using a logical file with a unique key (built with a DDS),
the constraint will be there but it will not appear in the catalog file QSYS2/SYSCST.
2) If the catalog did go out of sync, the only way to repair it is
with a reclaim storage operation, as shown below:
RCLSTG SELECT(*DBXREF)
Go Back to the Top
|
|
Using Decimal Formatting in SQL
This is a
simple trick to format decimal data in SQL. Particularly useful when creating
new tables with a simple select statement.
SELECT
DEC(MONEY_TABLE.DOLLARS_COLUMN/10000 , 15 , 4) from TABLE
Note that the
division must be inside and the Decimal formatting outside. This technique is
especially useful for pulling JDE data with accurate decimal information.
To round down the
figure above from 4 to 2 decimals only, you can wrap it again using the
following technique:
SELECT DEC(
DEC(MONEY_TABLE.DOLLARS_COLUMN/10000 , 15 , 4), 15, 2)
To round down the figure above
from 4 to 2 decimals only, you can wrap it again using the following
technique:
SELECT
DEC( CASE
WHEN
STRIP(PDCRCD)
=
'CAD'
THEN
PDAEXP/100
ELSE
PDFEA/100
END
,
15,2) EXTENDED_PRICE
Note:
Be careful when formatting aggregate values:
==================================================
SUM(
DEC(DOMESTIC_OPEN_AMT/100, 14,2)) - Works
DEC(
SUM(DOMESTIC_OPEN_AMT) /100, 14,2) - Does not Work
Go Back to the Top
|
|
SQL Horizontal Pivoting Technique Coding Example
The following
links point to a real example, with each item belonging to up to 16 business
units,
arranged in up to 16 rows. The pivoting spreads the business units the item
belongs to sideways.
Pivot_SQL_Code_001
Pivot_SQL_Code_002
Pivot_SQL_Code_003
Pivot_SQL_Code_004
Go Back to the Top
|
|
A simple "Count Distinct" Technique
Count Distinct
sounds obvious, but often, I have to think of the syntax again to make it
work. Here is a working example:
SELECT COUNT ( DISTINCT
MATERIAL_NUMBER) FROM MATERIAL_TABLE
Go Back to the Top
|
|
A Universal or "case insensitive" Search and Replace
Technique
Search for a
string and find it, no matter in which case it is written in, and replace
with a new string - the trick is the UCASE keyword:
REPLACE
(ITEM_DESCRIPTION, UPPER('oem') , 'MANUFACTURER'
)
Yields an
upper-case result only.
The code below
keeps the old case.
------------------------------------------------------------------------------
-- Case Independent
String Replacer
-- This code was
created to replace non-standard descriptions
-- such as SN, S/N, SerNo, Serial#, Serial NO to at standard "Serial
Number"
--
-- This replacer is
different from most, as it
--
- finds the string in any possible case
--
- preserves the original on both side of what is found
--
- most practical for search/replace which have to leave the
--
original string in the original case.
------------------------------------------------------------------------------
SELECT
SUBSTR(TEMPPF , 1,
20)
ORIGINAL,
CASE WHEN LOCATE('OEM',
UPPER(TEMPPF) ) <>
0
-- Yes there!
THEN SUBSTR(TEMPPF, 1, LOCATE('OEM', UCASE(TEMPPF)) - 1)
|| 'MANUFACTURER'
|| CASE WHEN SUBSTR(
TEMPPF,
-- SUBSTR strg
LOCATE('OEM', UPPER(TEMPPF) ) + LENGTH('OEM'), -- SUBSTR from
LENGTH(TEMPPF) -
LOCATE('OEM', UPPER(TEMPPF))+ LENGTH('OEM')-- SUBSTR to
)= '' -- NOTHING MORE TO ADD.
THEN ''
ELSE -- THERE IS SOMETHING AFTER THE SEARCH STRING.
SUBSTR(
TEMPPF,
-- SUBSTR strg
LOCATE('OEM', UPPER(TEMPPF)) + LENGTH('OEM') , -- SUBSTR from
LENGTH(TEMPPF) -
LOCATE('OEM', UPPER(TEMPPF))+ LENGTH('OEM') +1) -- SUBSTR to
END
ELSE
TEMPPF -- Nothing to replace, just yield the original string
END
REPLACEMENT_RESULT
FROM
CATDA7/TEMPPF
--------------------------------------------------------------------
ORIGINAL
REPLACEMENT_RESULT
--------------------
----------------------------------------------
OEm test replacement
MANUFACTURER test
replacement
test OEM
replacement test MANUFACTURER
replacement
test replacement
OEM test replacement
MANUFACTURER
test replacement OeM test replacement MANUFACTURER with mixed CaSe
oem
MANUFACTURER
oem
MANUFACTURER
Go
Back to the Top
|
|
An SQL Technique to reduce a variable amount of spaces to only a
single space.
This tip
comes to me with credit from IT Jungle from Sean. It leaves only one space
where multiple spaces may exist.
SELECT Replace ( Replace(
Replace(name,' ','<>' ),'><','' ),'<>',' ' )
Go Back to the Top
|
|
Technique to convert a UNIX date into a Gregorian Date using SQL
SELECT DATE(((
1250703000 - (6*3600) ) / 86400 ) + 719163 ) FROM SYSIBM/SYSDUMMY1
Note:
● 1250703000 is the Julian date to convert (replaceable with a
variable in your application). In this example, 1250703000 converts to August
19, 2009.
● 6 is the Greenwich
offset in terms of hours (e.g. Mountain time is – 6 hours)
● The file sysibm/sysdummy1 is the
equivalent of Oracle’s “DUAL” table, which will yield only
one value. It is used only for this example. You can select your Unix dates
from any other file.
Conversely,
CAST
(DAYS(CURRENT TIMESTAMP) - DAYS('1970-01-01') AS INTEGER) * 86400 +
(MIDNIGHT_SECONDS(CURRENT TIMESTAMP - CURRENT TIMEZONE))
will return the number of seconds since 1970-01-01, the
"UNIX date" - at the time of execution.
IBM has a link on this topic located at: http://www-01.ibm.com/support/docview.wss?uid=swg21376100
Go Back to the Top
|
|
Technique to unpack packed numeric data embedded in a string
Assuming
you have an alpha field that contains embedded numeric packed data,
this technique will enable you to extract that numeric data from
individual fields using SQL
SELECT
KEY_FIELD,
SUBSTR(ALPHA_DATA,1,20),
CASE WHEN SUBSTR( CHAR(HEX( SUBSTR(ALPHA_DATA, 33,4) )), 8,1) = 'F'
THEN INT(SUBSTR( CHAR(HEX( SUBSTR(ALPHA_DATA, 33,4) )), 1, 7))
ELSE INT(SUBSTR( CHAR(HEX( SUBSTR(ALPHA_DATA, 33,4) )), 1, 7)) * -1
END
FROM DATA_TABLE WHERE KEY_FIELD = 1001
Go Back to the Top
|