By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,233 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,233 IT Pros & Developers. It's quick & easy.

IBMDBDB2 OLEDB provider does not appear to like case

P: n/a
JAW
I have sucessfully created an SQL SERVER LINKED SERVER to point to DB2
on Z/OS.
I have been successully able to copy most of the tables in the
database. However, I have a couple that have a dates outside of SQL
Server supported range.

IBMDADB2 at 8.1 FIXPAK 10.

I can run the OPENQUERY() component on the mainframe and it works fine
except when using CASE. I can CAST various datatypes fine (From
TIMESTAMP to CHAR(26) for instance)
TRUNCATE TABLE METER_PT
INSERT INTO METER_PT
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,DT_EFF, CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM OPENQUERY(HOST,'
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,
CASE WHEN DT_EFF < '1800-01-01' THEN NULL ELSE DT_EFF END DT_EFF,
CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM DB2X.XXXXX.METER_PT
')
GO

Aug 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
JAW
CASE WHEN DT_EFF < '1800-01-01' THEN NULL

should have been

CASE WHEN DT_EFF < ''1800-01-01'' THEN NULL

Its all good!
JAW wrote:
I have sucessfully created an SQL SERVER LINKED SERVER to point to DB2
on Z/OS.
I have been successully able to copy most of the tables in the
database. However, I have a couple that have a dates outside of SQL
Server supported range.

IBMDADB2 at 8.1 FIXPAK 10.

I can run the OPENQUERY() component on the mainframe and it works fine
except when using CASE. I can CAST various datatypes fine (From
TIMESTAMP to CHAR(26) for instance)
TRUNCATE TABLE METER_PT
INSERT INTO METER_PT
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,DT_EFF, CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM OPENQUERY(HOST,'
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,
CASE WHEN DT_EFF < '1800-01-01' THEN NULL ELSE DT_EFF END DT_EFF,
CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM DB2X.XXXXX.METER_PT
')
GO
Aug 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.