I am very new to SQL and so far have overcome all my problems with reference books and the internet.
However, I am creating a report which doesn't like something in my code (I'm pretty sure it's connected with the second derived table I've added, as without it the report runs okay, although not with the results I expect to see) and I cannot figure out why.
When I try to preview the report I get this error:
Invalid Object Name 'UNIDATA_PLANNINGL_1'.
So far my code is like this:
SELECT DISTINCT
UNIDATA_PLANNING.R0 AS [Job Record], SUBSTRING(UNIDATA_PLANNINGL.R0, 1, 3) AS [Op No], UNIDATA_PLANNINGL.R1 AS [Op Description],
UNIDATA_PLANNINGL.R43 AS [T/O], UNIDATA_WIP.R0 AS [Job No], UNIDATA_PLANNING.ASSY AS Route, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1,
3) AS [Assy Op], UNIDATA_PLANNINGL_1.R1 AS [Op Description], UNIDATA_PLANNINGL_1.R43 AS [T/O], derivedtbl_1.OPTRIG1,
UNIDATA_PLANNINGL.R0 AS INVISIBLE2, UNIDATA_PLANNINGL_1.R0 AS INVISIBLE
FROM UNIDATA_PLANNINGL INNER JOIN
UNIDATA_WIPL ON UNIDATA_PLANNINGL.R0 = UNIDATA_WIPL.JOBPLKEY INNER JOIN
UNIDATA_WIP ON UNIDATA_WIPL.R2 = UNIDATA_WIP.R0 INNER JOIN
UNIDATA_PLANNING ON UNIDATA_PLANNINGL.Assy = UNIDATA_PLANNING.R0 INNER JOIN
UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_1 ON UNIDATA_PLANNING.ASSY = UNIDATA_PLANNINGL_1.Assy AND
SUBSTRING(UNIDATA_PLANNINGL.R0, 1, 3) = SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AND
UNIDATA_PLANNINGL.R43 <> UNIDATA_PLANNINGL_1.R43 INNER JOIN
(SELECT R0, SUBSTRING(R0, 1, 3) AS OP1, SUBSTRING(R0, 1, 3) + R43 AS OPTRIG1
FROM UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_2) AS derivedtbl_1 ON UNIDATA_PLANNINGL.R0 = derivedtbl_1.R0 INNER JOIN
(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL_1 AS UNIDATA_PLANNINGL_1_1) AS derivedtbl_2 ON UNIDATA_PLANNINGL_1.R0 = derivedtbl_2.R0
WHERE (UNIDATA_PLANNINGL.R0 LIKE N'%JOB\%') AND (NOT (UNIDATA_PLANNING.ASSY LIKE N'%2-%'))
ORDER BY [Job Record]
Could someone more experienced please explain what I have done wrong and if there is a way to fix it?
Thanks,
CPH
Derived table 2
(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL_1 AS UNIDATA_PLANNINGL_1_1) AS derivedtbl_2
You alias the table as UNIDATA_PLANNINGL_1_1 then reference it in the select list as UNIDATA_PLANNINGL_1
In future if you get these warnings, pull out any sub queries and test them independantly. You might also want to use something more meaningfull to alias the table names with but thats your choice
|||Sam,
Thanks for your quick response. However, the alias UNIDATA_PLANNINGL_1_1 is something VS2005 does. If I amend it to UNIDATA_PLANNING_1 which is the table I have included VS2005 immediately changes it back to UNIDATA_PLANNINGL_1_1.
You're right about the table names, though, but unfortunately that's out of my hands.
Thanks again,
Chris
|||also in that 2nd derived table - you select from UNIDATA_PLANNINGL_1. Is this a table? you use that name as an alias further up which AFAIK will have no scope inside the sub query.
The query should be:
(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_1) AS derivedtbl_2
Sam,
Thanks again for your reply and your help. As I have the table UNIDATA_PLANNINGL twice in the query VS2005 has aliased the second occurence of the table as UNIDATA_PLANNINGL_1. I have used multiple occurrences of same tables before without any problem, but this is the first time I've tried adding derived tables.
I have tried your suggestion which has cleared the error.
Your help is much appreciated, thanks again,
Chris
