DB2 query - can you get it to work ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

DB2 query - can you get it to work ?

Author

Topic

rpisaneschi
Starting Member

4 Posts

Posted - 2006-08-25 : 13:21:01

SELECT DISTINCT
SP.SPCLT_CD,
CASE CI.CORP_ENT_CD
WHEN 'IL1' THEN ' '
ELSE SP.PROV_TYP_CD
END AS XPROV_TYP_CD,
ST.PWN_STA_CD
FROM
TPSDB05.PAL_PFIN PP
INNER JOIN
TPSDB05.PWN_CI_PFIN CI
ON PP.PROV_ID = CI.PROV_ID
INNER JOIN
TPSDB05.PWN_SPCLT SP
ON CI.PROV_ID = SP.PROV_ID
AND CI.NWK_CD = SP.NWK_CD
AND CI.SUB_NWK_CD = SP.SUB_NWK_CD
AND CI.CORP_ENT_CD = SP.CORP_ENT_CD
INNER JOIN
TPSDB05.PWN_SPCLT SP
ON CI.PROV_ID = SP.PROV_ID
AND CI.NWK_CD = SP.NWK_CD
AND CI.SUB_NWK_CD = SP.SUB_NWK_CD
AND CI.CORP_ENT_CD = SP.CORP_ENT_CD
INNER JOIN
TPSDB05.PWN_STATUS ST
ON CI.PROV_ID = ST.PROV_ID
AND CI.NWK_CD = ST.NWK_CD
AND CI.SUB_NWK_CD = ST.SUB_NWK_CD
AND CI.CORP_ENT_CD = ST.CORP_ENT_CD
WHERE
PP.PROV_ID = 354664370 AND
CI.CORP_ENT_CD = 'OK1' AND
CI.NWK_CD IN
(SELECT CD_VAL
FROM TPSDB05.CODE_REF
WHERE CD_REF_NM = 'BRE03PCPA'
AND CORP_ENT_CD = 'OK1'
AND VLDT_IND = 'Y'
AND CD_EFDT <= '2006-08-21'
AND (CD_END_DT > '2006-08-21'

OR CD_END_DT IS NULL)) AND
CI.SUB_NWK_CD IN
(SELECT CD_VAL
FROM TPSDB05.CODE_RELSHP
WHERE CD_REF_NM = 'BREXTSUBN'
AND CORP_ENT_CD = 'OK1'
AND REL_CD_REF_NM = 'BREXTPCPA'
AND REL_CD_VAL = CI.NWK_CD
AND VLDT_IND = 'Y'
AND REL_CD_EFDT <= '2006-08-21'
AND (REL_CD_END_DT > '2006-08-21'
OR REL_CD_END_DT IS NULL)) AND
PP.ACTVN_DT <= '2006-08-21' AND
CI.VLDT_IND = 'Y' AND
CI.PWN_CI_PFIN_EFDT <= '2006-08-21' AND
( CI.PWN_CI_PFIN_END_DT IS NULL
OR CI.PWN_CI_PFIN_END_DT > '2006-08-21')
AND
SP.VLDT_IND = 'Y' AND
SP.SPCLT_CD IN
(SELECT CD_VAL
FROM TPSDB05.CODE_REF
WHERE CORP_ENT_CD = 'OK1'
AND CD_REF_NM = 'BRE03SPCLT'
AND VLDT_IND = 'Y'
AND CD_EFDT <= '2006-08-21'
AND (CD_END_DT > '2006-08-21'
OR CD_END_DT IS NULL)) AND
SP.PWN_SPCLT_EFDT <= '2006-08-21' AND
(SP.PWN_SPCLT_END_DT IS NULL
OR SP.PWN_SPCLT_END_DT > '2006-08-21')
AND
ST.VLDT_IND = 'Y' AND
ST.PWN_STA_EFDT <= '2006-08-21' AND
(ST.PWN_STA_END_DT IS NULL
OR ST.PWN_STA_END_DT > '2006-08-21');
DSNT404I SQLCODE = 203, WARNING: THE QUALIFIED COLUMN NAME SP.SPCLT_CD WAS
RESOLVED USING A NON-UNIQUE OR UNEXPOSED NAME
DSNT418I SQLSTATE = 01552 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 1 1108504850 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'42127112'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
SPCLT_CD XPROV_TYP_CD PWN_STA_CD
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

snSQL
Starting Member

14 Posts
Posted - 08/25/2006 : 12:28:56
--------------------------------------------------------------------------------

You'll have to give a bit more information here - what happens when you execute this? At a glance it looks OK, what error do you get when you try it?

I guess the most likely problem is you don't have the exact same tables on SQL Server or that you are executing the diagnostic stuff at the end (that's DB2 specific, so only execute the SELECT).

ie. don't try to execute this part
DSNT404I SQLCODE = 203, WARNING: THE QUALIFIED COLUMN NAME SP.SPCLT_CD WAS
RESOLVED USING A NON-UNIQUE OR UNEXPOSED NAME
DSNT418I SQLSTATE = 01552 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 1 1108504850 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'42127112'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
SPCLT_CD XPROV_TYP_CD PWN_STA_CD

rpisaneschi
Starting Member

USA
3 Posts
Posted - 08/25/2006 : 12:58:42
--------------------------------------------------------------------------------

This is what I get back:
Is the SQL 203 just a warning or is there something wrong with the SQL ?

Thanks for your help, its appreciated !
Bob

DSNT404I SQLCODE = 203, WARNING: THE QUALIFIED COLUMN NAME SP.SPCLT_CD WAS
RESOLVED USING A NON-UNIQUE OR UNEXPOSED NAME
DSNT418I SQLSTATE = 01552 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 1 1108504850 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'42127112'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
SPCLT_CD XPROV_TYP_CD PWN_STA_CD
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-25 : 13:54:38

Try the DB2 forum over at dbforums.com

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 08:18:24

Cross posting http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71081

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources