Correlation Error when Adding an Inner Join

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 2005 Forums

Transact-SQL (2005)

Correlation Error when Adding an Inner Join

Author

Topic

jauner
Starting Member

19 Posts

Posted - 2009-10-08 : 16:57:04

I have the following SQL in a Stored Procedure. I apologize for the length of it:


SELECT 
  ALTSCHED.STARTDATE
, ALTSCHED.ENDDATE
, RESSCHDXXX.RESID
, RESSCHDXXX.GROUPID
, RGRPXXX.DESCR
, JOBXXX.LOADID
, JOBXXX.LOADSIZE
, JOBXXX.STARTDATE
, JOBXXX.ENDDATE

-- jobroute.job
,dbo.ApsDeriveJobSp(ORDERXXX.OrderTable,job.suffix,job.type,job.job,job.ps_num,ORDERXXX.ORDERID,jobitem.job,ORDERXXX.ORDTYPE)

-- jobroute.suffix
, (CASE WHEN ORDERXXX.OrderTable = 'job' THEN
      CASE WHEN job.suffix = 0 AND job.type = 'S' THEN ''
           ELSE job.suffix
      END
         WHEN ORDERXXX.OrderTable = 'rcpts' THEN ''
         WHEN ORDERXXX.OrderTable = 'jobitem' THEN jobitemjob.suffix
    END)

, jobroute.oper_num

-- job.type
, dbo.GetApsOrderType(ORDERXXX.ORDERID)

-- job.stat
, (CASE WHEN ORDERXXX.OrderTable = 'job' THEN
      CASE WHEN job.suffix = 0 AND job.type = 'S' THEN 'P'
           ELSE job.stat
      END
         WHEN ORDERXXX.OrderTable = 'rcpts' THEN 'P'
         WHEN ORDERXXX.OrderTable = 'jobitem' THEN jobitemjob.stat
           ELSE 'P'
    END)

,ORDERXXX.PARTID

, job.description

-- Qty at Current WC
, dbo.MaxQty( 0.0, ( ISNULL(jobroute.qty_received, 0.0) -
                     ISNULL(jobroute.qty_scrapped, 0.0) -
                     ISNULL(jobroute.qty_moved, 0.0) ) )
-- Previous WC
, NULL

-- Current WC
, jobroute.wc

-- Next WC
, NULL

, ROUND(CONVERT(REAL,DATEDIFF(mi, JOBXXX.ENDDATE, ORDERXXX.DUEDATE))/60.0/24.0, 8)

,GAI_Previous_Job_Info.Uf_ItemDieNumber 		
,GAI_Previous_Job_Info.Uf_ItemLength			
,GAI_Previous_Job_Info.Uf_ItemFinish			
,GAI_Previous_Job_Info.Uf_ItemAlloy			
,GAI_Previous_Job_Info.Uf_ItemTemper			
,job.ord_num					-- lake 11/4/2008 djs  6100
,job.ord_line					-- lake 11/4/2008 djs  6100
,coitem.Uf_ShippingTolerance	-- lake 11/4/2008 djs  6100
,item.drawing_nbr			-- lake 1/15/09 djs  6100
,(Select name from custaddr where cust_num = job.cust_num AND cust_seq = 0)	-- lake 11/4/2008 djs  6100
, RESSCHDXXX.SEQNUM		-- lake 12/26/2008 djs  6100
,item.unit_weight			-- lake 1/15/2009 djs
,CASE WHEN co.stat = 'P' THEN ROUND(item.unit_weight * jobroute.qty_received, 0) ELSE ROUND(item.unit_weight * (jobroute.qty_received - jobroute.qty_scrapped - jobroute.qty_moved), 0) END	-- lake 1/15/2009 djs
,coitem.qty_ordered		-- lake 1/15/2009 djs
,(select end_date from job_sch where job = job.job and suffix = job.suffix)	-- lake 1/15/2009 djs
, job.RowPointer		-- GAI 06/09/2009
, jobroute.RowPointer		-- GAI 06/09/2009
, job.NoteExistsFlag		-- GAI 06/10/2009
, jobroute.NoteExistsFlag	-- GAI 06/10/2009
FROM
   RESSCHD000 RESSCHDXXX
LEFT JOIN RESRC000 RESRCXXX ON RESRCXXX.RESID = RESSCHDXXX.RESID
INNER JOIN GAI_PREVIOUS_JOB_INFO job ON GAI_Previous_Job_Info.Job = Job.Job And GAI_Previous_Job_Info.Suffix = (Job.Suffix - 1)
INNER JOIN JOB000 JOBXXX ON RESSCHDXXX.JOBTAG = JOBXXX.JOBTAG
INNER JOIN ORDIND000 ORDINDXXX ON JOBXXX.ORDERTAG = ORDINDXXX.ORDERTAG
LEFT JOIN JOBSTEP000 JOBSTEPXXX ON JOBXXX.JSID = JOBSTEPXXX.JSID AND ORDINDXXX.PROCPLANID = JOBSTEPXXX.PROCPLANID
LEFT JOIN jobroute jobroute ON jobroute.RowPointer = JOBSTEPXXX.RefRowPointer
LEFT JOIN ORDER000 ORDERXXX ON ORDINDXXX.ORDERID = ORDERXXX.ORDERID
LEFT OUTER JOIN job job on job.RowPointer = ORDERXXX.OrderRowPointer
LEFT OUTER JOIN jobitem jobitem on jobitem.RowPointer = ORDERXXX.OrderRowPointer
LEFT OUTER JOIN job jobitemjob on jobitemjob.job = jobitem.job AND
                                  jobitemjob.suffix = jobitem.suffix AND
                                  jobitemjob.item = jobitem.item
LEFT JOIN RGRP000 RGRPXXX ON RESSCHDXXX.GROUPID = RGRPXXX.RGID
LEFT JOIN ALTSCHED ON ALTSCHED.ALTNO = 0
LEFT JOIN item ON job.item = item.item			-- lake 11/4/2008 djs  6100
LEFT JOIN itemcust ON job.cust_num = itemcust.cust_num AND job.item	= itemcust.item  -- lake 11/4/2008 djs  6100
LEFT JOIN coitem ON job.ord_num = coitem.co_num AND job.ord_line = coitem.co_line AND job.ord_release = coitem.co_release	-- lake 11/4/2008 djs  6100
LEFT JOIN co ON co.co_num = coitem.co_num		-- lake 1/15/2009 djs
WHERE
   (RESSCHDXXX.STARTCD = 'A' OR RESSCHDXXX.STARTCD = 'K')
   AND CHARINDEX( RGRPXXX.SLTYPE, @ResourceGroupType) > 0
   AND RESSCHDXXX.RESID BETWEEN @ResourceStarting AND @ResourceEnding
   AND RESSCHDXXX.GROUPID BETWEEN @ResourceGroupStarting AND @ResourceGroupEnding
   AND RESRCXXX.RESTYPE BETWEEN @ResourceTypeStarting AND @ResourceTypeEnding
   -- Changed by feedback from FrontStep
   -- AND RESSCHDXXX.STARTDATE >= @ScheduleDateStarting AND RESSCHDXXX.ENDDATE <= @ScheduleDateEnding
   AND RESSCHDXXX.STARTDATE <= @ScheduleDateEnding
   AND RESSCHDXXX.ENDDATE >= @ScheduleDateStarting
ORDER BY RESSCHDXXX.GROUPID, RESSCHDXXX.SEQNUM, RESSCHDXXX.STARTDATE, JOBXXX.STARTDATE ASC

On the line that starts with: INNER JOIN GAI_PREVIOUS_JOB_INFO
I am getting This error:
The correlation name 'job' is specified multiple times in a FROM clause.

Not sure how to resolve it.

If you need more code I can post it if need be

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 17:03:54

You are referencing two different tables with the same alias as 'job'...

quote:
INNER JOIN GAI_PREVIOUS_JOB_INFO job
.....
LEFT OUTER JOIN job job on job.RowPointer = ORDERXXX.OrderRowPointer

which table are you joining GAI_PREVIOUS_JOB_INFO to?

jauner
Starting Member

19 Posts

Posted - 2009-10-08 : 17:28:47

The JOb table is the table I am joining to

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 22:03:35

as highlight by vijayisonly you assigned 2 different table with the same alias name 'job'. you have to change one of the to different alias. See below highlighted in red


FROM
   RESSCHD000 RESSCHDXXX
LEFT JOIN RESRC000 RESRCXXX ON RESRCXXX.RESID = RESSCHDXXX.RESID
INNER JOIN GAI_PREVIOUS_JOB_INFO job ON GAI_Previous_Job_Info.Job = Job.Job And GAI_Previous_Job_Info.Suffix = (Job.Suffix - 1)
INNER JOIN JOB000 JOBXXX ON RESSCHDXXX.JOBTAG = JOBXXX.JOBTAG
INNER JOIN ORDIND000 ORDINDXXX ON JOBXXX.ORDERTAG = ORDINDXXX.ORDERTAG
LEFT JOIN JOBSTEP000 JOBSTEPXXX ON JOBXXX.JSID = JOBSTEPXXX.JSID AND ORDINDXXX.PROCPLANID = JOBSTEPXXX.PROCPLANID
LEFT JOIN jobroute jobroute ON jobroute.RowPointer = JOBSTEPXXX.RefRowPointer
LEFT JOIN ORDER000 ORDERXXX ON ORDINDXXX.ORDERID = ORDERXXX.ORDERID
LEFT OUTER JOIN job job on job.RowPointer = ORDERXXX.OrderRowPointer
LEFT OUTER JOIN jobitem jobitem on jobitem.RowPointer = ORDERXXX.OrderRowPointer
LEFT OUTER JOIN job jobitemjob on jobitemjob.job = jobitem.job AND

KH
[spoiler]Time is always against us[/spoiler]

jauner
Starting Member

19 Posts

Posted - 2009-10-09 : 12:21:49

I am fairly new to doing Transact SQL. How do I do an inner join to the job table? Do I need to add it to the FROM clause???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 12:59:49

quote:
Originally posted by jauner

I am fairly new to doing Transact SQL. How do I do an inner join to the job table? Do I need to add it to the FROM clause???

just change the order like this


FROM
   RESSCHD000 RESSCHDXXX
LEFT JOIN RESRC000 RESRCXXX ON RESRCXXX.RESID = RESSCHDXXX.RESID
INNER JOIN JOB000 JOBXXX ON RESSCHDXXX.JOBTAG = JOBXXX.JOBTAG
INNER JOIN ORDIND000 ORDINDXXX ON JOBXXX.ORDERTAG = ORDINDXXX.ORDERTAG
LEFT JOIN ORDER000 ORDERXXX ON ORDINDXXX.ORDERID = ORDERXXX.ORDERID
LEFT OUTER JOIN job job on job.RowPointer = ORDERXXX.OrderRowPointer
INNER JOIN GAI_PREVIOUS_JOB_INFO  ON GAI_Previous_Job_Info.Job = Job.Job And GAI_Previous_Job_Info.Suffix = (Job.Suffix - 1)
LEFT JOIN JOBSTEP000 JOBSTEPXXX ON JOBXXX.JSID = JOBSTEPXXX.JSID AND ORDINDXXX.PROCPLANID = JOBSTEPXXX.PROCPLANID
LEFT JOIN jobroute jobroute ON jobroute.RowPointer = JOBSTEPXXX.RefRowPointer
LEFT OUTER JOIN jobitem on jobitem.RowPointer = ORDERXXX.OrderRowPointer
LEFT OUTER JOIN job jobitemjob on jobitemjob.job = jobitem.job AND
.......

jauner
Starting Member

19 Posts

Posted - 2009-10-09 : 14:05:31

That worked.

Thanks!

But Can you tell me why the order you did worked exactly?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:08:14

because you were refering to the tables before they were actually included in join . like you used Job.Job in second join but your job table is coming only after 8 th join. you cant forward reference tables like this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources