Whenever rows get rejected from an insert/update/whatever, they get written to the badfiles. This is kind of like "time out" for naughty records. Unlike naughty children, however, you can choose to completely ignore these records for all eternity. That might not be what you want to do, however.
Assuming you care what's in that file, you'll need to read it. Thing is, it's kind of a convoluted mess upon close examination. Informatica creates a dump of your record plus a bunch of other stuff into a comma-delimited flat file. You can read this file like any other source record, except it's not the same as your failed session's target file definition.
In fact, it looks a little something like this:
0,D,26,D,,N,138,D,,N,,N,,N,,N,7,D,,N,,N,1,D....
The first column is a row_indicator (smallint). It contains values from 0 to 9. The most common being 0 or 1 which means the row was either inserted or updated and failed.
Then, there is a column indicator. The first one of this is always a "D", which means it's valid data.
Then you get every column that was written to your previous target that failed, followed by a column indicator after every column.
So, if you tried to write to a table with 3 columns A, B and C, and failed on a unique constraint violation, your badfiles table will look like this:
0,D,A,D,B,D,C,D
Where all the "D" values are column indicators indicating that the columns were valid data, and the 0 indicates that it was an insert. In the event that a column was null, you'll two commas (,,) followed by an "N" for Null.
For more information on badfiles, consult Informatica's excellent help. This topic is listed under "reject files" - you'll have a hard time searching for it under "badfiles".
Thursday, March 18, 2010
Thursday, February 18, 2010
Convert UNIX epoch time in Informatica
Handy function for converting UNIX epoch time in Informatica. UNIX epoch time is a int(11) field which contains the number of elapsed seconds since midnight 1/1/1970.
To convert to a GMT date in Informatica, I created a mapplet. The mapplet receives the EPOCH_TIME as a variable.
IIF(EPOCH_TIME > 0,
ADD_TO_DATE(to_date('19700101', 'yyyymmdd'),'SS',EPOCH_TIME), NULL)
The original version I had created of this lacked the IIF test. So, in cases where epoch time was 0, I was getting 1/1/1970 as my result. Which I didn't want.
Enjoy.
To convert to a GMT date in Informatica, I created a mapplet. The mapplet receives the EPOCH_TIME as a variable.
IIF(EPOCH_TIME > 0,
ADD_TO_DATE(to_date('19700101', 'yyyymmdd'),'SS',EPOCH_TIME), NULL)
The original version I had created of this lacked the IIF test. So, in cases where epoch time was 0, I was getting 1/1/1970 as my result. Which I didn't want.
Enjoy.
Tuesday, December 15, 2009
Informatica for free
Saw this today, and thought I'd share.
If you are interested in installing Informatica PowerCenter (server + client) on your Home PC/ Laptop, here is how you can do it. The copy comes with a Development License http://www.oracle.com/technology/software/popup-license/standard-license.html that allows you to use full version of the product at no charge for strictly self-educational purposes.
Go to http://edelivery.oracle.com/
Fill in the form in the next page.
In the Media Pack Search page, search for "Oracle Business Intelligence"
Download the version that you want to install.
Notes:
You may also want to download an Oracle database.
These pages will help you in the installation process:
http://download.oracle.com/docs/cd/E10783_01/doc/bi.79/e10742/anyinstadmwininstall.htm#BABBHJDH
http://gerardnico.com/wiki/dw/etl/informatica_powercenter/powercenter_installation
If you are interested in installing Informatica PowerCenter (server + client) on your Home PC/ Laptop, here is how you can do it. The copy comes with a Development License http://www.oracle.com/technology/software/popup-license/standard-license.html that allows you to use full version of the product at no charge for strictly self-educational purposes.
Go to http://edelivery.oracle.com/
Fill in the form in the next page.
In the Media Pack Search page, search for "Oracle Business Intelligence"
Download the version that you want to install.
Notes:
You may also want to download an Oracle database.
These pages will help you in the installation process:
http://download.oracle.com/docs/cd/E10783_01/doc/bi.79/e10742/anyinstadmwininstall.htm#BABBHJDH
http://gerardnico.com/wiki/dw/etl/informatica_powercenter/powercenter_installation
Monday, October 19, 2009
As I live and breathe
Whoever wrote this should have their programmer license revoked. Oh, wait, licenses aren't required to be programmers. Well, maybe we should start requiring licenses. The first question on that test should be "when is it OK to include a 'SELECT *' in production code, and if the answer is anything other than 'NEVER', the programmer should be shot.
Actual production SQL code follows:
SELECT P.WMS_SHIP_DT, P.DC_LOCATION_ID, P.TO_LOCATION_ID, P.PRODUCT_ID, P.PO_NBR, Z.DEMAND_DT
, NVL(Z.SOURCE_VENDOR_ID, 1802) AS SOURCE_VENDOR_ID, Z.VENDOR_ID
, CASE WHEN NVL(Z.DPR_PROJ_ORDER_QTY,0) > NVL(P.WMS_ORIG_ORD_QTY,0) THEN NVL(Z.DPR_PROJ_ORDER_QTY,0)
ELSE NVL(P.WMS_ORIG_ORD_QTY,0) END AS DEMAND_QTY
, NVL(Z.DPR_PROJ_ORDER_QTY,0) AS DPR_PROJ_ORDER_QTY, NVL(P.PO_ORIG_ORDER_QTY,0) AS PO_ORIG_ORDER_QTY
, NVL(P.PO_ORDER_QTY,0) AS PO_ORDER_QTY, NVL(P.WMS_UNIT_PRICE,0) AS WMS_UNIT_PRICE
, NVL(P.WMS_ORIG_ORD_QTY,0) AS WMS_ORIG_ORD_QTY, NVL(P.WMS_SHIPPED_QTY,0) AS WMS_SHIPPED_QTY
, NVL(P.WMS_CANCEL_QTY,0) AS WMS_CANCEL_QTY, NVL(P.OUT_OF_STOCK_QTY,0) AS OUT_OF_STOCK_QTY
FROM (SELECT O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID,
MAX(T.PO_DOC_DT) AS PO_DOC_DT, MAX(T.PO_ORIG_ORDER_QTY) AS PO_ORIG_ORDER_QTY,
MAX(T.PO_ORDER_QTY) AS PO_ORDER_QTY, MAX(NVL(O.WMS_UNIT_PRICE,0)) AS WMS_UNIT_PRICE,
SUM(O.WMS_ORIG_ORD_QTY) AS WMS_ORIG_ORD_QTY, SUM(O.WMS_SHIPPED_QTY) AS WMS_SHIPPED_QTY,
SUM(O.WMS_CANCEL_QTY) AS WMS_CANCEL_QTY,
CASE WHEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY) >= 0
THEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY)
ELSE 0 END AS OUT_OF_STOCK_QTY
FROM EDW_STANDBY..WMS_OUTBOUND O
LEFT OUTER JOIN (SELECT PH.PO_DOC_DT, PH.PO_NBR, PL.PRODUCT_ID, PL.LOCATION_ID
,PL.PO_ORIG_ORDER_QTY, PL.PO_ORDER_QTY
FROM EDW_STANDBY..PO_HDR_FACT PH, EDW_STANDBY..PO_LN_FACT PL
WHERE PH.PO_MSTR_DUE_DT BETWEEN CURRENT_DATE - 75 AND CURRENT_DATE + 30
AND PH.PO_TYPE_ID = 'UB'
AND PH.PO_GID = PL.PO_GID
AND PH.PO_MSTR_DUE_DT = PL.PO_MSTR_DUE_DT) T
ON O.TO_LOCATION_ID = T.LOCATION_ID AND O.PO_NBR = T.PO_NBR AND O.PRODUCT_ID = T.PRODUCT_ID
WHERE O.WMS_SHIP_DT > CURRENT_DATE - 35
AND O.PO_NBR IS NOT NULL
GROUP BY O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID) P
LEFT OUTER JOIN (SELECT *
FROM WMS_DEMAND_STORE_DAY
WHERE DEMAND_DT > CURRENT_DATE - 60
AND SHIP_SEQ_NBR = 1) Z
ON NVL(P.PO_DOC_DT, TO_DATE('12/31/9999', 'MM/DD/YYYY')) = Z.DEMAND_DT
AND P.TO_LOCATION_ID = Z.TO_LOCATION_ID
AND P.DC_LOCATION_ID = Z.DC_LOCATION_ID
AND P.PRODUCT_ID = Z.PRODUCT_ID
Actual production SQL code follows:
SELECT P.WMS_SHIP_DT, P.DC_LOCATION_ID, P.TO_LOCATION_ID, P.PRODUCT_ID, P.PO_NBR, Z.DEMAND_DT
, NVL(Z.SOURCE_VENDOR_ID, 1802) AS SOURCE_VENDOR_ID, Z.VENDOR_ID
, CASE WHEN NVL(Z.DPR_PROJ_ORDER_QTY,0) > NVL(P.WMS_ORIG_ORD_QTY,0) THEN NVL(Z.DPR_PROJ_ORDER_QTY,0)
ELSE NVL(P.WMS_ORIG_ORD_QTY,0) END AS DEMAND_QTY
, NVL(Z.DPR_PROJ_ORDER_QTY,0) AS DPR_PROJ_ORDER_QTY, NVL(P.PO_ORIG_ORDER_QTY,0) AS PO_ORIG_ORDER_QTY
, NVL(P.PO_ORDER_QTY,0) AS PO_ORDER_QTY, NVL(P.WMS_UNIT_PRICE,0) AS WMS_UNIT_PRICE
, NVL(P.WMS_ORIG_ORD_QTY,0) AS WMS_ORIG_ORD_QTY, NVL(P.WMS_SHIPPED_QTY,0) AS WMS_SHIPPED_QTY
, NVL(P.WMS_CANCEL_QTY,0) AS WMS_CANCEL_QTY, NVL(P.OUT_OF_STOCK_QTY,0) AS OUT_OF_STOCK_QTY
FROM (SELECT O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID,
MAX(T.PO_DOC_DT) AS PO_DOC_DT, MAX(T.PO_ORIG_ORDER_QTY) AS PO_ORIG_ORDER_QTY,
MAX(T.PO_ORDER_QTY) AS PO_ORDER_QTY, MAX(NVL(O.WMS_UNIT_PRICE,0)) AS WMS_UNIT_PRICE,
SUM(O.WMS_ORIG_ORD_QTY) AS WMS_ORIG_ORD_QTY, SUM(O.WMS_SHIPPED_QTY) AS WMS_SHIPPED_QTY,
SUM(O.WMS_CANCEL_QTY) AS WMS_CANCEL_QTY,
CASE WHEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY) >= 0
THEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY)
ELSE 0 END AS OUT_OF_STOCK_QTY
FROM EDW_STANDBY..WMS_OUTBOUND O
LEFT OUTER JOIN (SELECT PH.PO_DOC_DT, PH.PO_NBR, PL.PRODUCT_ID, PL.LOCATION_ID
,PL.PO_ORIG_ORDER_QTY, PL.PO_ORDER_QTY
FROM EDW_STANDBY..PO_HDR_FACT PH, EDW_STANDBY..PO_LN_FACT PL
WHERE PH.PO_MSTR_DUE_DT BETWEEN CURRENT_DATE - 75 AND CURRENT_DATE + 30
AND PH.PO_TYPE_ID = 'UB'
AND PH.PO_GID = PL.PO_GID
AND PH.PO_MSTR_DUE_DT = PL.PO_MSTR_DUE_DT) T
ON O.TO_LOCATION_ID = T.LOCATION_ID AND O.PO_NBR = T.PO_NBR AND O.PRODUCT_ID = T.PRODUCT_ID
WHERE O.WMS_SHIP_DT > CURRENT_DATE - 35
AND O.PO_NBR IS NOT NULL
GROUP BY O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID) P
LEFT OUTER JOIN (SELECT *
FROM WMS_DEMAND_STORE_DAY
WHERE DEMAND_DT > CURRENT_DATE - 60
AND SHIP_SEQ_NBR = 1) Z
ON NVL(P.PO_DOC_DT, TO_DATE('12/31/9999', 'MM/DD/YYYY')) = Z.DEMAND_DT
AND P.TO_LOCATION_ID = Z.TO_LOCATION_ID
AND P.DC_LOCATION_ID = Z.DC_LOCATION_ID
AND P.PRODUCT_ID = Z.PRODUCT_ID
Thursday, October 15, 2009
We have another winner
This one is worse than the last one. It's hard to believe this is a single SQL statement.
SELECT from_lane.lane_id, from_lane.lane_type from_lane_type_id,
from_lane.city from_lane_city, from_lane.state_cd from_lane_state_cd,
from_lane.country_cd from_lane_country_cd,
from_lane.low_zip from_lane_low_zip,
from_lane.high_zip from_lane_high_zip, from_lane.other from_lane_other,
to_lane.lane_type to_lane_type_id, to_lane.city to_lane_city,
to_lane.state_cd to_lane_state_cd, to_lane.country_cd to_lane_country_cd,
to_lane.low_zip to_lane_low_zip,
to_lane.high_zip to_lane_high_zip, to_lane.other to_lane_other
FROM (SELECT ID lane_id, from_key_num lane_type, NULL city,
from_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 2
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
from_low_key_value || '00' low_zip,
from_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.from_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2
ON p.from_high_key_value = c2.zip3
WHERE from_key_num = 3
UNION
SELECT ID lane_id, from_key_num lane_type, from_low_key_value city,
SUBSTR (from_high_key_value, 1, 4) state_cd, country,
MIN (zip) low_zip, MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.from_low_key_value = c.city
AND p.from_high_key_value = c.state
WHERE p.from_key_num = 4
GROUP BY ID, from_key_num, from_low_key_value, from_high_key_value, country
UNION
SELECT ID lane_id, from_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.from_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.from_high_key_value = c2.zip
WHERE from_key_num = 5
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 6
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, from_low_key_value other
FROM tms_lane_pre
WHERE from_key_num = 7) from_lane,
(SELECT ID lane_id, to_key_num lane_type, NULL city,
to_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 2
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
to_low_key_value || '00' low_zip,
to_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.to_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2 ON p.to_high_key_value =
c2.zip3
WHERE to_key_num = 3
UNION
SELECT ID lane_id, to_key_num lane_type, to_low_key_value city,
SUBSTR (to_high_key_value, 1, 4) state_cd,country country_cd, MIN (zip) low_zip,
MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.to_low_key_value = c.city
AND p.to_high_key_value = c.state
WHERE p.to_key_num = 4
GROUP BY ID, to_key_num, to_low_key_value, to_high_key_value, country
UNION
SELECT ID lane_id, to_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.to_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.to_high_key_value = c2.zip
WHERE to_key_num = 5
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 6
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, to_low_key_value other
FROM tms_lane_pre
WHERE to_key_num = 7) to_lane
WHERE from_lane.lane_id = to_lane.lane_id
SELECT from_lane.lane_id, from_lane.lane_type from_lane_type_id,
from_lane.city from_lane_city, from_lane.state_cd from_lane_state_cd,
from_lane.country_cd from_lane_country_cd,
from_lane.low_zip from_lane_low_zip,
from_lane.high_zip from_lane_high_zip, from_lane.other from_lane_other,
to_lane.lane_type to_lane_type_id, to_lane.city to_lane_city,
to_lane.state_cd to_lane_state_cd, to_lane.country_cd to_lane_country_cd,
to_lane.low_zip to_lane_low_zip,
to_lane.high_zip to_lane_high_zip, to_lane.other to_lane_other
FROM (SELECT ID lane_id, from_key_num lane_type, NULL city,
from_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 2
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
from_low_key_value || '00' low_zip,
from_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.from_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2
ON p.from_high_key_value = c2.zip3
WHERE from_key_num = 3
UNION
SELECT ID lane_id, from_key_num lane_type, from_low_key_value city,
SUBSTR (from_high_key_value, 1, 4) state_cd, country,
MIN (zip) low_zip, MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.from_low_key_value = c.city
AND p.from_high_key_value = c.state
WHERE p.from_key_num = 4
GROUP BY ID, from_key_num, from_low_key_value, from_high_key_value, country
UNION
SELECT ID lane_id, from_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.from_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.from_high_key_value = c2.zip
WHERE from_key_num = 5
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 6
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, from_low_key_value other
FROM tms_lane_pre
WHERE from_key_num = 7) from_lane,
(SELECT ID lane_id, to_key_num lane_type, NULL city,
to_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 2
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
to_low_key_value || '00' low_zip,
to_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.to_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2 ON p.to_high_key_value =
c2.zip3
WHERE to_key_num = 3
UNION
SELECT ID lane_id, to_key_num lane_type, to_low_key_value city,
SUBSTR (to_high_key_value, 1, 4) state_cd,country country_cd, MIN (zip) low_zip,
MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.to_low_key_value = c.city
AND p.to_high_key_value = c.state
WHERE p.to_key_num = 4
GROUP BY ID, to_key_num, to_low_key_value, to_high_key_value, country
UNION
SELECT ID lane_id, to_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.to_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.to_high_key_value = c2.zip
WHERE to_key_num = 5
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 6
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, to_low_key_value other
FROM tms_lane_pre
WHERE to_key_num = 7) to_lane
WHERE from_lane.lane_id = to_lane.lane_id
Tuesday, October 13, 2009
SQL Disaster
In keeping with the theme of my blog, I present to you one example of a horrendous SQL query my co-worker sent me. I don't know what kind of super villain created this, but I'd hate to meet him or her in a dark alley. We're hoping it's some sort of program-generated query, like what Microstrategy can produce.
This one has it all. - advanced analytical functions, aggregates, a hint override, order bys, group bys, aliases, nested sub-selects, outer joins, in clauses, between clauses, even a UNION!
Think you've seen worse? Send it to me!
SELECT /*+ ORDERED */
inv.inv_bol, inv.cost_qualifier, NVL(inv.cost_code,' '), inv.invoice_id
,inv.inv_nbr, rcv.dttm, inv.status_code, inv.inv_date, vouch.dttm AS VOUCHER_DTTM
,inv.inv_distance, inv.inv_weight, inv.inv_cube, vouch.ap_pay_date, inv.amount
FROM (SELECT i.inv_bol, c.cost_qualifier, c.cost_code, i.invoice_id
,MAX(i.inv_date) AS INV_DATE
,MAX(i.inv_nbr) AS INV_NBR
,MAX(i.status_code) AS STATUS_CODE
,MAX(i.inv_distance) AS INV_DISTANCE
,MAX(i.inv_weight) AS INV_WEIGHT
,MAX(i.inv_cube) AS INV_CUBE
,SUM(amount) AS AMOUNT
FROM (SELECT inv_bol
,LAST_VALUE(invoice_id) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INVOICE_ID
,LAST_VALUE(inv_date) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DATE
,LAST_VALUE(inv_nbr) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_NBR
,LAST_VALUE(status_code) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) STATUS_CODE
,LAST_VALUE(inv_distance) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DISTANCE
,LAST_VALUE(inv_weight) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_WEIGHT
,LAST_VALUE(inv_cube) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_CUBE
,ROW_NUMBER() OVER(PARTITION BY inv_bol ORDER BY status_code DESC) SEQ
FROM
tm_fp_invoice
,(SELECT DISTINCT PRIMARY_KEY1 BOL
FROM tm_FP_AUDIT_TRAIL
WHERE TABLE_NAME = 'FP_MASTER'
AND DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
UNION
SELECT DISTINCT I.INV_BOL BOL
FROM tm_FP_AUDIT_TRAIL A, tm_FP_INVOICE I
WHERE TABLE_NAME = 'FP_INVOICE'
AND A.DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
AND A.PRIMARY_KEY1 = I.INVOICE_ID) B
WHERE tm_fp_invoice.inv_bol = b.bol) I
,tm_fp_invoice_cost C
WHERE i.seq = 1
AND i.invoice_id = c.invoice_id
AND c.cost_qualifier <= 6
GROUP BY i.inv_bol,c.cost_qualifier,c.cost_code,i.invoice_id) INV
,(SELECT
i.inv_bol
,a.dttm
,p.ap_pay_date
FROM
tm_fp_audit_trail A
,tm_fp_invoice I
,tm_fp_payment P
WHERE a.table_name = 'FP_INVOICE'
AND a.value_after = '70'
AND a.value_before != '70'
AND a.primary_key1 = TO_CHAR(i.invoice_id)
AND i.status_code IN (70,80)
AND i.invoice_id = p.invoice_id (+)) VOUCH
,(SELECT primary_key1, MAX(dttm) DTTM
FROM tm_fp_audit_trail
WHERE action_code = 'A'
AND table_name = 'FP_INVOICE'
GROUP BY primary_key1) RCV
WHERE inv.inv_bol = vouch.inv_bol (+)
AND inv.invoice_id = rcv.primary_key1 (+)
This one has it all. - advanced analytical functions, aggregates, a hint override, order bys, group bys, aliases, nested sub-selects, outer joins, in clauses, between clauses, even a UNION!
Think you've seen worse? Send it to me!
SELECT /*+ ORDERED */
inv.inv_bol, inv.cost_qualifier, NVL(inv.cost_code,' '), inv.invoice_id
,inv.inv_nbr, rcv.dttm, inv.status_code, inv.inv_date, vouch.dttm AS VOUCHER_DTTM
,inv.inv_distance, inv.inv_weight, inv.inv_cube, vouch.ap_pay_date, inv.amount
FROM (SELECT i.inv_bol, c.cost_qualifier, c.cost_code, i.invoice_id
,MAX(i.inv_date) AS INV_DATE
,MAX(i.inv_nbr) AS INV_NBR
,MAX(i.status_code) AS STATUS_CODE
,MAX(i.inv_distance) AS INV_DISTANCE
,MAX(i.inv_weight) AS INV_WEIGHT
,MAX(i.inv_cube) AS INV_CUBE
,SUM(amount) AS AMOUNT
FROM (SELECT inv_bol
,LAST_VALUE(invoice_id) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INVOICE_ID
,LAST_VALUE(inv_date) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DATE
,LAST_VALUE(inv_nbr) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_NBR
,LAST_VALUE(status_code) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) STATUS_CODE
,LAST_VALUE(inv_distance) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DISTANCE
,LAST_VALUE(inv_weight) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_WEIGHT
,LAST_VALUE(inv_cube) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_CUBE
,ROW_NUMBER() OVER(PARTITION BY inv_bol ORDER BY status_code DESC) SEQ
FROM
tm_fp_invoice
,(SELECT DISTINCT PRIMARY_KEY1 BOL
FROM tm_FP_AUDIT_TRAIL
WHERE TABLE_NAME = 'FP_MASTER'
AND DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
UNION
SELECT DISTINCT I.INV_BOL BOL
FROM tm_FP_AUDIT_TRAIL A, tm_FP_INVOICE I
WHERE TABLE_NAME = 'FP_INVOICE'
AND A.DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
AND A.PRIMARY_KEY1 = I.INVOICE_ID) B
WHERE tm_fp_invoice.inv_bol = b.bol) I
,tm_fp_invoice_cost C
WHERE i.seq = 1
AND i.invoice_id = c.invoice_id
AND c.cost_qualifier <= 6
GROUP BY i.inv_bol,c.cost_qualifier,c.cost_code,i.invoice_id) INV
,(SELECT
i.inv_bol
,a.dttm
,p.ap_pay_date
FROM
tm_fp_audit_trail A
,tm_fp_invoice I
,tm_fp_payment P
WHERE a.table_name = 'FP_INVOICE'
AND a.value_after = '70'
AND a.value_before != '70'
AND a.primary_key1 = TO_CHAR(i.invoice_id)
AND i.status_code IN (70,80)
AND i.invoice_id = p.invoice_id (+)) VOUCH
,(SELECT primary_key1, MAX(dttm) DTTM
FROM tm_fp_audit_trail
WHERE action_code = 'A'
AND table_name = 'FP_INVOICE'
GROUP BY primary_key1) RCV
WHERE inv.inv_bol = vouch.inv_bol (+)
AND inv.invoice_id = rcv.primary_key1 (+)
Monday, July 20, 2009
Recovering a workflow
I've discovered a powerful option within Informatica's Workflow Manager. This might be old hat to some of you, but it was new to me, so I thought I'd share it.
In general, restarting a down workflow is kind of a pain, especially if you have one as complex as the main one I maintain:
In this workflow, I've identified a couple of "merge points". These are sessions that run all by themselves. The beauty of these specific sessions is that if the workflow fails at some point before these merge points, I can "Restart workflow from task" and once it gets up to the "merge point", I can then "restart workflow from task" at the merge point, and then let it run from there.
There's a better way - suspense.
In the Workflow General properties (Workflow -> edit -> general tab) you'll see a checkbox called "Suspend on Error". Honestly, why this isn't checked by default is kind of a mystery, because it's a beautiful, powerful thing.
When a session fails, the workflow will go into "Suspend" mode. When in suspense mode, you have the option to "recover" the workflow. Once chosen, the workflow will restart whatever down job there is, and then finish the workflow from there. The beauty of this option is that you don't have to intervene at the merge point. So, if a job that would normally run in parallel to other sessions fails, you don't have to then restart the workflow from the merge point forward. Informatica retains session status and can continue on the workflow as needed.
When recovered, the failed job gets this weird red/green hybrid line to indicate that it was failed, but then succeeded.
In general, restarting a down workflow is kind of a pain, especially if you have one as complex as the main one I maintain:
In this workflow, I've identified a couple of "merge points". These are sessions that run all by themselves. The beauty of these specific sessions is that if the workflow fails at some point before these merge points, I can "Restart workflow from task" and once it gets up to the "merge point", I can then "restart workflow from task" at the merge point, and then let it run from there.
There's a better way - suspense.
In the Workflow General properties (Workflow -> edit -> general tab) you'll see a checkbox called "Suspend on Error". Honestly, why this isn't checked by default is kind of a mystery, because it's a beautiful, powerful thing.
When a session fails, the workflow will go into "Suspend" mode. When in suspense mode, you have the option to "recover" the workflow. Once chosen, the workflow will restart whatever down job there is, and then finish the workflow from there. The beauty of this option is that you don't have to intervene at the merge point. So, if a job that would normally run in parallel to other sessions fails, you don't have to then restart the workflow from the merge point forward. Informatica retains session status and can continue on the workflow as needed.
When recovered, the failed job gets this weird red/green hybrid line to indicate that it was failed, but then succeeded.
Subscribe to:
Posts (Atom)