|
Tylogix - iSeries SQL Tips and Techniques
|
|
Two examples of how SQL can be used to search the Database Catalog:
How to find a table name, using it's object description:
SELECT SUBSTR(TABLE_NAME, 1, 20), CHAR(TABLE_TEXT) FROM SYSTABLES
WHERE LOWER(CHAR(TABLE_TEXT)) LIKE '%vendor master%'
How
to find tables that use a field suffix - in this case, a portion of the
field will contain "mail" and I am looking only in library "ALLCO"
SELECT DISTINCT SUBSTR( COLUMN_NAME,1, 10) COL_NAME,SUBSTR(CHAR(COLUMN_HEADING), 1, 30) COL_HEADING,
SUBSTR(COL.TABLE_NAME,1, 10) COL_TABLE, SUBSTR(CHAR(TAB.TABLE_TEXT ), 1, 50) TAB_TEXT,
SUBSTR(CHAR(TAB.TABLE_SCHEMA), 1, 30) TAB_SCHEMA
FROM SYSCOLUMNS COL INNER JOIN SYSTABLES TAB ON COL.TABLE_NAME =
TAB.TABLE_NAME WHERE
CHAR(COLUMN_NAME) LIKE '%MAIL%' AND CHAR(TAB.TABLE_SCHEMA) = 'ALLCO'
Go Back to the Top
|
|
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? 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
Note: The TRIM function can also be used for this.
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 limit the number of records returned from a query.
This
technique is handy to ensure too many rows are not retrieved. It can
also be used in a co-related sub-select update, to ensure only one row
is used in the update operation.
In this example, I used 1, but it could be any number.
SELECT * FROM syscolumns WHERE COLUMN_NAME = 'DSTCTL' LIMIT 1
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 |
|
An SQL Technique to substitute variables depending on value using multiple CASE statements.
This
tip is long over-due, credit goes to my colleague Prasanna. Note:
> This is a most useful technique for a quick transformation in an ETL situation.
> The CASE Statement will yield a NULL if no ELSE clause is specified.
SELECT ET.EMPLOYEE_NO, ET.FIRST_NAME, ET.LASTNAME,
CASE
WHEN ET.YEARS_OF_SERVICE > 25
THEN ‘ELIGIBLE FOR RETIREMENT’
WHEN ET.YEARS_OF_SERVICE > 15
THEN ’15 YEARS OR LESS TO GO!’
ELSE ’TAKE A DEEP BREATH!’
END
FROM EMPLOYEE_TABLE ET
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
|
|
SQL Technique to compare consecutive Rows in a Journal File to find changes
This technique is useful sniff out variations within a specific field in a file journal. In this case, a margin change.
POJRNDLY01: Generate OUTFILE FILE POJRNDLY01, which is
simply
ensuring the data is ordered by key and
timestamp. The Timestamp order is critical. It will ensure we can
use the RRN for the next query to get the logical
previous row.
This can be done with QMQRY or EXCSQL.
SELECT
*
FROM
PO_FILE ORDER BY POCMPN, BWCUSN, BWTIMSTP
POJRNDLY02: PO Daily Margin Change Journal
Query
Generate OUTFILE FILE POJRNDLY02, which will query POJRNDLY01 and join it to itself to get the previous row
for each record read. The idea is to kick out a new row for every single margin change
SELECT
AA.BWCMPN, AA.PODIVN, AA.PODPTN,
AA.POCUSN,AA.POTTNP,
BB.PODCTP MARGIN_BEFORE, AA.PODCTP
MARGIN_AFTER,
AA.PODCTP - BB.PODCTP
MARGINDIFFERENCE,
char(BB.POTIMSTP )
BEFORE_TIMESTAMP,
char(AA.POTIMSTP )
AFTER_TIMESTAMP,
BB.POPGMNAM,
CURRENT TIMESTAMP
CURRENT_TIMESTAMP
FROM
POJRNDLY01
AA
INNER JOIN POJRNDLY01
BB
ON
RRN(AA)-1 =
RRN(BB)
AND AA.POCMPN =
BB.POCMPN
AND AA.POCUSN =
BB.POCUSN
AND BB.PODCTP <>
AA.PODCTP
WHERE
AA.PODCTP <>
0
AND
BB.PODCTP IS NOT
NULL
Go Back to the Top
|
Technique to find a table by searching on a single column name in Oracle
select col.column_id,
col.owner as schema_name,
col.table_name,
col.column_name,
col.data_type,
col.data_length,
col.data_precision,
col.data_scale,
col.nullable,
t.NUM_ROWS
from sys.all_tab_columns col
inner join sys.all_tables t on
col.owner = t.owner
and col.table_name = t.table_name
where col.owner = 'SCHEMA_OWNER'
and col.column_name like
'%INV%'
and t.NUM_ROWS > 0
order by col.column_id
|
Technique to find a table by searching on two column names in Oracle
select col.table_name,
col.column_name as COLUMN_NAME_1,
col2.column_Name as COLUMN_NAME_2,
col.data_type,
col.data_length,
col.data_precision,
t.NUM_ROWS
from sys.all_tab_columns col
inner join sys.all_tables t on
col.owner = t.owner
and col.table_name = t.table_name
left outer join sys.all_tab_columns
COL2
on COL.table_name = COL2.table_name
where col.owner = 'SCHEMA_OWNER'
and col.COLUMN_NAME like
'%QTY%'
and Col2.COLUMN_NAME Like
'%LIN%'
and t.NUM_ROWS > 0
-- and col.table_Name Like '%AGMT%'
order by col.table_name, col.column_id
|
Technique to find a table by searching on three column names in Oracle
select DISTINCT
T.num_rows,
COL2.owner as schema_name,
COL2.table_name,
COL2.column_NAME as FIRST_COLUMN_SEARCH,
COL3.column_name as
SECOND_COLUMN_SEARCH,
COL4.column_name as THIRD_COLUMN_SEARCH,
COL2.data_type,
COL2.data_length,
COL2.data_precision,
COL2.data_scale,
COL2.nullable
from sys.all_tab_columns COL2
inner join sys.all_tables t on
COL2.owner = t.owner
and COL2.table_name = t.table_name
left outer join sys.all_tab_columns
COL3
on COL2.table_name = COL3.table_name
left outer join sys.all_tab_columns
COL4
on COL2.table_name = COL4.table_name
where COL2.owner = 'SCHEMA_OWNER'
and t.NUM_ROWS > 0
and COL2.column_NAME like '%QTY%'
and COL3.column_NAME like '%LIN%'
and COL4.column_NAME like '%SIG%'
-- and col.table_Name Like
'%AGMT%'
order by 5, COL2.TABLE_NAME, COL2.COLUMN_NAME,
COL3.COLUMN_NAME
|
Technique to find a table by searching on a single column name in Microsoft SQL Server
select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
typ.name as data_type,
col.max_length,
col.precision,
tab.type_desc
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as typ
on col.user_type_id = typ.user_type_id
where lower(col.name) like '%priority%'
order by schema_name,
table_name,
column_id
|
Technique to find a table by searching on two column names in Microsoft SQL Server
select distinct
schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col1.column_id,
col1.name as Column_Selection_01,
col1.max_length,
col1.precision,
typ2.name as data_type,
col2.column_id,
col2.name as Column_Selection_02,
col2.max_length,
col2.precision,
typ2.name as data_type
from sys.tables as tab
inner join sys.columns as col1
on tab.object_id = col1.object_id
Left outer join sys.types as typ1
on col1.user_type_id = typ1.user_type_id
Left outer join sys.columns as col2
on tab.object_id = col2.object_id
Left outer join sys.types as typ2
on col1.user_type_id = typ2.user_type_id
where lower(col1.name) like '%invoice%'
and lower(col2.name) like '%po%'
order by schema_name,
tab.name,
col1.column_id,
col2.column_id
|
Technique to find a table by searching on three column names in Microsoft SQL Server
select distinct
schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col1.column_id,
col1.name as Column_Selection_01,
col1.max_length,
col1.precision,
typ2.name as data_type,
col2.column_id ,
col2.name as Column_Selection_02,
col2.max_length,
col2.precision,
typ2.name as data_type,
typ3.name as data_type,
col3.column_id ,
col3.name as Column_Selection_03,
col3.max_length,
col3.precision,
typ3.name as data_type
from sys.tables as tab
inner join sys.columns as col1
on tab.object_id = col1.object_id
Left outer join sys.types as typ1
on col1.user_type_id = typ1.user_type_id
Left outer join sys.columns as col2
on tab.object_id = col2.object_id
Left outer join sys.types as typ2
on col1.user_type_id = typ2.user_type_id
Left outer join sys.columns as col3
on tab.object_id = col2.object_id
Left outer join sys.types as typ3
on col1.user_type_id = typ3.user_type_id
where lower(col1.name) like '%invoice%'
and lower(col2.name) like '%po%'
and lower(col3.name) like '%priority%'
order by schema_name,
tab.name,
col1.column_id,
col2.column_id
|