Can't understand this one...

Question

Post reply

Can't understand this one...

MrBaseball34

SSCrazy

Points: 2515
More actions
October 30, 2008 at 8:43 am

#200705

runiing the code below from database named Rabbit:
INSERT INTO ptEncounterEncounterDetailTemp (EncounterID, EncounterDetailID)
SELECT e.Migration, ed.Migration
FROM ptEncounter e, ptEncounterDetails ed
INNER JOIN [RabbitPM_brad].[dbo].[ptEncounterEncounterDetail] eed
ON e.Migration = eed.EncounterID
AND ed.Migration = eed.EncounterDetailID
Get these errors:
[font="Courier New"]Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "e.Migration" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "ed.Migration" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "ed.Migration" could not be bound.
[/font]
Syntax checks out, what is wrong?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try this. You have to join all the tables seperately.

SELECT e.Migration,

ed.Migration

FROM ptEncounter e

INNER JOIN [RabbitPM_brad].[dbo].[ptEncounterEncounterDetail] eed

ON e.Migration = eed.EncounterID

INNER JOIN ptEncounterDetails ed

ON ed.Migration = eed.EncounterDetailID

danschl SSCertifiable Points: 5770 More actions · Answer 2

yes you are using both the old method and the new method of joining in the same queries.

The old method is not going to be supported much longer

it is best to change to the new method using inner joins

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

Well, that doesn't seem to work either.

We need to build a new ptEncounterEncounterDetail from the EncounterID,

EncounterDetailID combinations where the Migration values are in the

old ptEncounterEncounterDetail (located on RabbitPM_brad).

Create scripts and test data in attached text file.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Just one thing missing, what should the expected results be based on the test data. Need something to check against.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

MrBaseball34 (10/30/2008)
Well, that doesn't seem to work either.

Please define 'that doesn't seem to work'. Did it throw an error? If so, what error. Does it give incorrect results? If so, please define what results you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Also, I assume that ptEncounterEncounterDetailTemp has the same structure as ptEncounterEncounterDetail?

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 7

Lynn Pettis (10/30/2008)
Just one thing missing, what should the expected results be based on the test data. Need something to check against.

Yes, same structure.

Desired results:

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 10, 1010)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 20, 1020)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 30, 1030)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 40, 1040)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 50, 1050)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 60, 1060)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 70, 1070)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 80, 1080)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 90, 1090)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(100, 10100)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(110, 10110)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(120, 10120)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(130, 10130)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(140, 10140)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(150, 10150)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(160, 10160)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 70, 10170)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 70, 10180)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES( 70, 10190)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(100, 10200)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(100, 10210)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(120, 10220)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(100, 10230)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(100, 10240)

INSERT INTO ptEncounterEncounterDetail_New ([EncounterID], [EncounterDetailID]) VALUES(120, 10250)

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 8

GilaMonster (10/30/2008)
MrBaseball34 (10/30/2008)
Well, that doesn't seem to work either.
Please define 'that doesn't seem to work'. Did it throw an error? If so, what error. Does it give incorrect results? If so, please define what results you want.

Returns too many records.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Sorry for taking a lot of space, but check out the following:

--We need to build a new ptEncounterEncounterDetail from the EncounterID,

--EncounterDetailID combinations where the Migration values are in the

--old ptEncounterEncounterDetail. Create scripts and test data below.

CREATE TABLE [dbo].[ptEncounter](

[EncounterID] [int] NOT NULL,

-- Other non vital columns here

[Migration] [int] NULL,

CONSTRAINT [PK_ptEncounter] PRIMARY KEY CLUSTERED

(

[EncounterID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[ptEncounterDetails](

[EncounterDetailID] [int] NOT NULL,

-- Other non vital columns here

[Migration] [int] NULL,

CONSTRAINT [PK_ptEncounterDetails] PRIMARY KEY CLUSTERED

(

[EncounterDetailID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[ptEncounterEncounterDetail](

[EncounterID] [int] NOT NULL,

[EncounterDetailID] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[ptEncounterEncounterDetailTemp](

[EncounterID] [int] NOT NULL,

[EncounterDetailID] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[ptEncounterEncounterDetail_New](

[EncounterID] [int] NOT NULL,

[EncounterDetailID] [int] NOT NULL

) ON [PRIMARY]

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 10, 310)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 20, 320)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 30, 330)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 40, 340)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 50, 350)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 60, 360)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 70, 370)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 80, 380)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES ( 90, 390)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (100, 3100)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (110, 3110)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (120, 3120)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (130, 3130)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (140, 3140)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (150, 3150)

INSERT INTO ptEncounter ([EncounterID], [Migration]) VALUES (160, 3160)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1010, 1310)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1020, 1320)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1030, 1330)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1040, 1340)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1050, 1350)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1060, 1360)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1070, 1370)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1080, 1380)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES ( 1090, 1390)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10100, 13100)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10110, 13110)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10120, 13120)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10130, 13130)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10140, 13140)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10150, 13150)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10160, 13160)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10170, 13170)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10180, 13180)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10190, 13190)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10200, 13200)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10210, 13210)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10220, 13220)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10230, 13230)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10240, 13240)

INSERT INTO ptEncounterDetails ([EncounterDetailID], [Migration]) VALUES (10250, 13250)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 310, 1310)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 320, 1320)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 330, 1330)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 340, 1340)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 350, 1350)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 360, 1360)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 370, 1370)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 380, 1380)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 390, 1390)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3100, 13100)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3110, 13110)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3120, 13120)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3130, 13130)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3140, 13140)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3150, 13150)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3160, 13160)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 370, 13170)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 370, 13180)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES( 370, 13190)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3100, 13200)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3100, 13210)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3120, 13220)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3100, 13230)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3100, 13240)

INSERT INTO ptEncounterEncounterDetail ([EncounterID], [EncounterDetailID]) VALUES(3120, 13250)