(Another) alias problem - SQL Server Forums

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

Transact-SQL (2000)

(Another) alias problem

Author

Topic

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-21 : 14:51:13

I am getting these errors when I use the following code...I cannot figure out why I cannot alias (using PART1 and PART2) the way I am trying to.

Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'PART1'.
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'PART2'.

SELECT PART1.*
INTO MHP001_15_Standard_NonStandardIPA_1
FROM
(SELECT *
FROM MAHP_Recon_Reports.dbo.PNCG0_Extract PE
WHERE (PE.CANC_DT <> PE.EFF_DT)
AND (PE.CONTR_TYP_CD = 'H')
AND (PE.CANC_DT > GETDATE())
AND (PE.PROV_TYP_CD = 'P')
AND (PE.IPA_NBR IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
44,45,46,47,48,49,50)) PART1
INNER JOIN (SELECT *
FROM MAHP_Recon_Reports.dbo.PNCG0_Extract PE
WHERE (PE.CANC_DT <> PE.EFF_DT)
AND (PE.CANC_DT > GETDATE())
AND (PE.PROV_TYP_CD = 'P')
AND (PE.IPA_NBR NOT IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
44,45,46,47,48,49,50)) PART2
ON (PART1.PROV_ID = PART2.PROV_ID)
AND (PART1.TAX_ID_NBR = (PART2.TAX_ID_NBR)
AND (PART1.IPA_NBR = PART2.IPA_NBR)

Help... :)

Dan Kohlhaas

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 15:01:07

The problem isn't with your aliases, it's with your parenthesis.



SELECT PART1.*
INTO MHP001_15_Standard_NonStandardIPA_1
FROM 
(
	SELECT *
	FROM MAHP_Recon_Reports.dbo.PNCG0_Extract
	WHERE CANC_DT <> EFF_DT
		AND CONTR_TYP_CD = 'H'
	AND CANC_DT > GETDATE()
	AND PROV_TYP_CD = 'P'
	AND IPA_NBR IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
	44,45,46,47,48,49,50)
) PART1
INNER JOIN 
(
	SELECT *
	FROM MAHP_Recon_Reports.dbo.PNCG0_Extract
	WHERE CANC_DT <> EFF_DT
	AND CANC_DT > GETDATE()
	AND PROV_TYP_CD = 'P'
	AND IPA_NBR NOT IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
	44,45,46,47,48,49,50)
) PART2
	ON PART1.PROV_ID = PART2.PROV_ID
		AND PART1.TAX_ID_NBR = PART2.TAX_ID_NBR
		AND PART1.IPA_NBR = PART2.IPA_NBR

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-21 : 15:01:25

Try this.. why r u using so many unnecessary brackets? making it more complecated..

SELECT PART1.*
INTO MHP001_15_Standard_NonStandardIPA_1
FROM
(SELECT *
FROM MAHP_Recon_Reports.dbo.PNCG0_Extract PE
WHERE (PE.CANC_DT <> PE.EFF_DT)
AND (PE.CONTR_TYP_CD = 'H')
AND (PE.CANC_DT > GETDATE())
AND (PE.PROV_TYP_CD = 'P')
AND (PE.IPA_NBR IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
44,45,46,47,48,49,50))) PART1
INNER JOIN (SELECT *
FROM MAHP_Recon_Reports.dbo.PNCG0_Extract PE
WHERE (PE.CANC_DT <> PE.EFF_DT)
AND (PE.CANC_DT > GETDATE())
AND (PE.PROV_TYP_CD = 'P')
AND (PE.IPA_NBR NOT IN (31,32,33,34,35,36,37,38,39,40,41,42,43,
44,45,46,47,48,49,50))) PART2
ON (PART1.PROV_ID = PART2.PROV_ID)
AND PART1.TAX_ID_NBR = (PART2.TAX_ID_NBR)
AND (PART1.IPA_NBR = PART2.IPA_NBR)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 15:02:41

You got

.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-21 : 15:06:37

Thank everyone. Like I said in a previous post, I am fairly new to this...I will learn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources