0

I have an SQL syntax like so:

SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, a.OrderPlotPlan_F, z.OrderTrusses_S, a.OrderTrusses_F, z.OrderHeatCalcs_S, a.OrderHeatCalcs_F, z.PermitRelease_S, a.PermitRelease_F, a.IntSelectionsAppt_F, z.ExcavationFile_S, a.ExcavationFile_F, z.FramingFile_S, a.FramingFile_F, z.FinishingFile_S, a.FinishingFile_F, a.StartDate, a.Possession, c.Expected_Occupancy AS ClosingDate, m.Description, m.ActualFinish AS LastTaskCompleted FROM (SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2

But I get this error for each item in my first select:

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.Job_No" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.ContractReceived_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.DesignReview_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.OrderPlotPlan_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.OrderPlotPlan_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.OrderTrusses_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.OrderTrusses_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.OrderHeatCalcs_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.OrderHeatCalcs_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.PermitRelease_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.PermitRelease_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.IntSelectionsAppt_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.ExcavationFile_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.ExcavationFile_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.FramingFile_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.FramingFile_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "z.FinishingFile_S" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.FinishingFile_F" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.StartDate" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.Possession" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "c.Expected_Occupancy" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "m.Description" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "m.ActualFinish" could not be bound.

I am very new at SQL and I have been banging my head against the wall trying to figure this out, this is way to advanced for me, any help would be much appreciated.

I tried removing the aliases and changing them to SOD and now I get these errors:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'OrderPlotPlan_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'OrderTrusses_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'OrderTrusses_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'OrderHeatCalcs_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'OrderHeatCalcs_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'PermitRelease_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'PermitRelease_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'IntSelectionsAppt_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ExcavationFile_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ExcavationFile_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'FramingFile_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'FramingFile_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'FinishingFile_S'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'FinishingFile_F'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'StartDate'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Possession'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Expected_Occupancy'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Description'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ActualFinish'.

On this query:

SELECT SOD.Job_No, SOD.ContractReceived_F, SOD.DesignReview_F, SOD.OrderPlotPlan_S, SOD.OrderPlotPlan_F, SOD.OrderTrusses_S, SOD.OrderTrusses_F, SOD.OrderHeatCalcs_S, SOD.OrderHeatCalcs_F, SOD.PermitRelease_S, SOD.PermitRelease_F, SOD.IntSelectionsAppt_F, SOD.ExcavationFile_S, SOD.ExcavationFile_F, SOD.FramingFile_S, SOD.FramingFile_F, SOD.FinishingFile_S, SOD.FinishingFile_F, SOD.StartDate, SOD.Possession, SOD.Expected_Occupancy AS ClosingDate, SOD.Description, SOD.ActualFinish AS LastTaskCompleted FROM (SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2

UPDATE:

I have tried the following:

SELECT SOD.Job_No, SOD.ContractReceived_F, SOD.DesignReview_F, SOD.OrderPlotPlan_S, SOD.OrderPlotPlan_F, SOD.OrderTrusses_S, SOD.OrderTrusses_F, SOD.OrderHeatCalcs_S, SOD.OrderHeatCalcs_F, SOD.PermitRelease_S, SOD.PermitRelease_F, SOD.IntSelectionsAppt_F, SOD.ExcavationFile_S, SOD.ExcavationFile_F, SOD.FramingFile_S, SOD.FramingFile_F, SOD.FinishingFile_S, SOD.FinishingFile_F, SOD.StartDate, SOD.Possession, SOD.Expected_Occupancy AS ClosingDate, SOD.Description, SOD.ActualFinish AS LastTaskCompleted FROM (SELECT *, Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2

and got this error:

Msg 8156, Level 16, State 1, Line 1 The column 'Job_No' was specified multiple times for 'SOD'.

UPDATE This fixed my issue:

SELECT SOD.Job_No, SOD.ContractReceived_F, SOD.DesignReview_F, SOD.OrderPlotPlan_S, SOD.OrderPlotPlan_F, SOD.OrderTrusses_S, SOD.OrderTrusses_F, SOD.OrderHeatCalcs_S, SOD.OrderHeatCalcs_F, SOD.PermitRelease_S, SOD.PermitRelease_F, SOD.IntSelectionsAppt_F, SOD.ExcavationFile_S, SOD.ExcavationFile_F, SOD.FramingFile_S, SOD.FramingFile_F, SOD.FinishingFile_S, SOD.FinishingFile_F, SOD.StartDate, SOD.Possession, SOD.Expected_Occupancy, SOD.Description, SOD.ActualFinish FROM (SELECT dbo.ScheduledatesFF.Job_No, dbo.ScheduledatesFF.ContractReceived_F, dbo.ScheduledatesFF.DesignReview_F, dbo.scheduledatesSS.OrderPlotPlan_S, dbo.ScheduledatesFF.OrderPlotPlan_F, dbo.scheduledatesSS.OrderTrusses_S, dbo.ScheduledatesFF.OrderTrusses_F, dbo.scheduledatesSS.OrderHeatCalcs_S, dbo.ScheduledatesFF.OrderHeatCalcs_F, dbo.scheduledatesSS.PermitRelease_S, dbo.ScheduledatesFF.PermitRelease_F, dbo.ScheduledatesFF.IntSelectionsAppt_F, dbo.scheduledatesSS.ExcavationFile_S, dbo.ScheduledatesFF.ExcavationFile_F, dbo.scheduledatesSS.FramingFile_S, dbo.ScheduledatesFF.FramingFile_F, dbo.scheduledatesSS.FinishingFile_S, dbo.ScheduledatesFF.FinishingFile_F, dbo.ScheduledatesFF.StartDate, dbo.ScheduledatesFF.Possession, dbo.tblCustomers.Expected_Occupancy, dbo.maxscheddate.Description, dbo.maxscheddate.ActualFinish, Row_number() OVER ( ORDER BY dbo.ScheduledatesFF.Job_No) AS RowNum FROM dbo.ScheduledatesFF INNER JOIN dbo.tblCustomers ON dbo.ScheduledatesFF.Job_No = dbo.tblCustomers.Job_No INNER JOIN dbo.scheduledatesSS ON dbo.ScheduledatesFF.Job_No = dbo.scheduledatesSS.Job_No LEFT OUTER JOIN dbo.maxscheddate ON dbo.ScheduledatesFF.Job_No = dbo.maxscheddate.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 1 AND 2
Improve this question
4
  • Side-note: SOD.RowNum BETWEEN 0 AND 2 it can never be 0. Commented Aug 13, 2014 at 12:51
  • The problem is that you are using table aliases which are not available outside of the sub-query. Commented Aug 13, 2014 at 12:52
  • You will have to select all the columns in the subquery that you want to have in the outer query. No you are only selecting 5 columns in the subquery: a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, Row_number() OVER (ORDER BY a.Job_No) AS RowNum. Commented Aug 13, 2014 at 13:03
  • Try to insert the SOD part into temporary table and than use that temporary table to get data from. Commented Aug 13, 2014 at 13:09

5 Answers 5

Reset to default
3

The problem is that you are using table aliases which are not available outside of the sub-query. So simply remove them(or replace with SOD). 

SELECT Job_No, ContractReceived_F, DesignReview_F, OrderPlotPlan_S, OrderPlotPlan_F, OrderTrusses_S, OrderTrusses_F, OrderHeatCalcs_S, OrderHeatCalcs_F, PermitRelease_S, PermitRelease_F, IntSelectionsAppt_F, ExcavationFile_S, ExcavationFile_F, FramingFile_S, FramingFile_F, FinishingFile_S, FinishingFile_F, StartDate, Possession, Expected_Occupancy AS ClosingDate, Description, ActualFinish AS LastTaskCompleted FROM (SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, OTHER_COLUMNS ...., Row_number() OVER ( ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2

Update: there's another problem. You are selecting columns in the main query which you haven't selected in the sub-query, so they don't exist there. You need to select at least those columns you want to use in the outer query.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

He can't remove aliases, if he does this won't work at all since SQL wouldn't know which column is a part of which table
@Hoh: there's only one "table", the sub-query SOD.
Your main query is trying to pull columns that the derived table doesn't provide.
@AndriyM: you're right. I've already mentioned that below the query. Now i've added OTHER_COLUMNS to clarify it.
0

User Select * instead of 

select a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, Row_number() OVER (ORDER BY a.Job_No) AS RowNum

in inner SELECT statement.

Improve this answer

1 Comment

what would the full query look like?
0 
SELECT SOD.Job_No, SOD.ContractReceived_F, SOD.DesignReview_F, SOD.OrderPlotPlan_S, SOD.OrderPlotPlan_F, SOD.OrderTrusses_S, SOD.OrderTrusses_F, SOD.OrderHeatCalcs_S, SOD.OrderHeatCalcs_F, SOD.PermitRelease_S, SOD.PermitRelease_F, SOD.IntSelectionsAppt_F, SOD.ExcavationFile_S, SOD.ExcavationFile_F, SOD.FramingFile_S, SOD.FramingFile_F, SOD.FinishingFile_S, SOD.FinishingFile_F, SOD.StartDate, SOD.Possession, SOD.Expected_Occupancy AS ClosingDate, SOD.Description, SOD.ActualFinish AS LastTaskCompleted FROM (SELECT *, Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2
Improve this answer

2 Comments

I have updated my question with the errors I get with this query
I get this error Msg 8156, Level 16, State 1, Line 1 The column 'Job_No' was specified multiple times for 'SOD'.
0

The reason is you are trying to retrieving columns that are not providing from the sub query. So SELECT * from sub query and set alias as SOD and get all columns that return from sub query using SELECT [SOD].*.

Try as follows,

SELECT [SOD].* FROM (SELECT *, Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) [SOD] WHERE [SOD].RowNum BETWEEN 1 AND 2

Note: Row number could not be 0.

Improve this answer

1 Comment

I get this error Msg 8156, Level 16, State 1, Line 1 The column 'Job_No' was specified multiple times for 'SOD'
-1 
SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, a.OrderPlotPlan_F, z.OrderTrusses_S, a.OrderTrusses_F, z.OrderHeatCalcs_S, a.OrderHeatCalcs_F, z.PermitRelease_S, a.PermitRelease_F, a.IntSelectionsAppt_F, z.ExcavationFile_S, a.ExcavationFile_F, z.FramingFile_S, a.FramingFile_F, z.FinishingFile_S, a.FinishingFile_F, a.StartDate, a.Possession, c.Expected_Occupancy AS ClosingDate, m.Description, m.ActualFinish AS LastTaskCompleted FROM (SELECT a.Job_No, a.ContractReceived_F, a.DesignReview_F, z.OrderPlotPlan_S, a.OrderPlotPlan_F, z.OrderTrusses_S, a.OrderTrusses_F, z.OrderHeatCalcs_S, a.OrderHeatCalcs_F, z.PermitRelease_S, a.PermitRelease_F, a.IntSelectionsAppt_F, z.ExcavationFile_S, a.ExcavationFile_F, z.FramingFile_S, a.FramingFile_F, z.FinishingFile_S, a.FinishingFile_F, a.StartDate, a.Possession, c.Expected_Occupancy AS ClosingDate, m.Description, m.ActualFinish AS LastTaskCompleted Row_number() OVER (ORDER BY a.Job_No) AS RowNum FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No) AS SOD WHERE SOD.RowNum BETWEEN 0 AND 2
Improve this answer

1 Comment

I tried this and it first gave me an error: Msg 102, Level 15, State 1, Line 47 Incorrect syntax near 'Row_number'. then I added a comma after As LastTaskCompleted before Row_number() and then I got the same errors as before

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.