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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 DB2 SQL - Can you get it to work ?

Author  Topic 

rpisaneschi
Starting Member

4 Posts
Posted - 2006-08-25 : 12:07:13
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
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-08-25 : 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
Go to Top of Page

rpisaneschi
Starting Member

4 Posts
Posted - 2006-08-25 : 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
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-08-25 : 13:10:18
OK, so you are running this on DB2, not SQL Server. It sounded like you are trying to run a query that you used on DB2 against SQL Server.

If you are using DB2 then you need to ask your question on a DB2 forum, this is a SQL Server forum.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2006-08-25 : 13:11:32
What table or subquery is aliased by SP...I think the alias is missing
Go to Top of Page

KenW
Constraint Violating Yak Guru

391 Posts
Posted - 2006-08-25 : 13:13:12
It's a problem with the SQL. The error message is saying either that you have the column SP.SPCLT_CD in more than one table (unlikely, since you aliased all the tables) or that a property was set on that column to make it "UNEXPOSED".

You'll probably get more help posting to a site that's DB2 specific; this is a SQL Server site.

Ken
Go to Top of Page

rpisaneschi
Starting Member

4 Posts
Posted - 2006-08-25 : 13:18:37
This is the Table SP is using:

TPSDB05.PWN_SPCLT SP
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2006-08-28 : 06:04:24
..in the CODE AS SUPPLIED....I THINK you've "aliased the same table TWICE"

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-28 : 06:34:02
Also you might find this query run faster
SELECT DISTINCT	SP.SPCLT_CD,
		CASE WHEN CI.CORP_ENT_CD = '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 AND CI.CORP_ENT_CD = 'OK1' AND CI.VLDT_IND = 'Y' AND CI.PWN_CI_PFIN_EFDT <= '2006-08-21' AND CI.PWN_CI_PFIN_END_DT > '2006-08-21'
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 AND SP.VLDT_IND = 'Y' AND SP.PWN_SPCLT_EFDT <= '2006-08-21' AND SP.PWN_SPCLT_END_DT > '2006-08-21'
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 AND ST.VLDT_IND = 'Y' AND ST.PWN_STA_EFDT <= '2006-08-21' AND ST.PWN_STA_END_DT > '2006-08-21'
LEFT JOIN	TPSDB05.CODE_REF a ON a.CD_VAL = CI.NWK_CD AND a.CD_REF_NM = 'BRE03PCPA' AND a.CORP_ENT_CD = 'OK1' AND a.VLDT_IND = 'Y' AND a.CD_EFDT <= '2006-08-21' AND a.CD_END_DT > '2006-08-21'
LEFT JOIN	TPSDB05.CODE_RELSHP b ON b.CD_VAL = CI.SUB_NWK_CD AND b.REL_CD_VAL = CI.NWK_CD AND b.CD_REF_NM = 'BREXTSUBN' AND b.CORP_ENT_CD = 'OK1' AND b.REL_CD_REF_NM = 'BREXTPCPA' AND b.VLDT_IND = 'Y' AND b.REL_CD_EFDT <= '2006-08-21' AND b.REL_CD_END_DT > '2006-08-21'
LEFT JOIN	TPSDB05.CODE_REF c ON c.CD_VAL = SP.SPCLT_CD AND c.CORP_ENT_CD = 'OK1' AND c.CD_REF_NM = 'BRE03SPCLT' AND c.VLDT_IND = 'Y' AND c.CD_EFDT <= '2006-08-21' AND c.CD_END_DT > '2006-08-21'
WHERE		PP.PROV_ID = 354664370
		AND PP.ACTVN_DT <= '2006-08-21'


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site