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
|
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
|