Help with clearning syntax error in Complex SQL Server Query

The problem is a big confusion with the SELECTs and their Alias. I don't know why you are using the (SELECT 1 A) part and so many times but that doesn't make sense. I'm going to put here an example of how you are using it and how it can be simplified so you just need to do the same for the rest of the similar cases.

Example of bad usage:

(SELECT * FROM #tmp_ontsqeview_111 WHERE sched_type_id=1) AS V INNER JOIN (SELECT 1 A)  tfltr_DATE ON DATE BETWEEN '20180201' AND '20180201') DQS

Select allOpen in new window

Example on how to correct it:

(SELECT * 
FROM #tmp_ontsqeview_111 
WHERE sched_type_id=1 AND V.DATE BETWEEN '20180201' AND '20180201') DQS

Select allOpen in new window

hi can you please send view ont_sqe_view_energysched

Scott - Your solution was very close to fixing it. Now my only issue is down in the area of the select below for the following columns.
MQS.LOC_ID, PD_DQS.LOC_ID, MQS.RESOURCE, PD_DQS.RESOURCE, MQS.AGGREGATE, PD_DQS.AGGREGATE..etc..MQS.entity1_id,  MQS.start_dt, PD_DQS.entity1_id, PD_DQS.start_dt

It is saying "The multi-part identifier <XXXX> could not be bound"


  UNION ALL SELECT V.*, CASE WHEN ISNULL(ANN.data_id,0) = 0 THEN 0 ELSE 1 END as has_comm_flg, ANN.ann_id  
   FROM (  SELECT  
   'DAILY' SOURCE,  ISNULL(ISNULL(ISNULL(DQS.LOC_ID,MQS.LOC_ID),PD_DQS.LOC_ID),PDR_DQS.LOC_ID) LOC_ID,  
   ISNULL(ISNULL(ISNULL(DQS.RESOURCE,MQS.RESOURCE),PD_DQS.RESOURCE),PDR_DQS.RESOURCE) RESOURCE,  
   ISNULL(ISNULL(ISNULL(DQS.AGGREGATE,MQS.AGGREGATE),PD_DQS.AGGREGATE),PDR_DQS.AGGREGATE) AGGREGATE,  
   ISNULL(ISNULL(ISNULL(DQS.STATION,MQS.STATION),PD_DQS.STATION),PDR_DQS.STATION) STATION,  
   ISNULL(ISNULL(ISNULL(DQS.STATION_GROUP,MQS.STATION_GROUP),PD_DQS.STATION_GROUP),PDR_DQS.STATION_GROUP) STATION_GROUP,  
   ISNULL(ISNULL(ISNULL(DQS.SUBMP,MQS.SUBMP),PD_DQS.SUBMP),PDR_DQS.SUBMP) SUBMP,  
   ISNULL(ISNULL(ISNULL(DQS.DATE,MQS.DATE),PD_DQS.DATE),PDR_DQS.DATE) DATE,  
   ISNULL(ISNULL(ISNULL(DQS.HOUR,MQS.HOUR),PD_DQS.HOUR),PDR_DQS.HOUR) HOUR,  
   ISNULL(ISNULL(ISNULL(DQS.INTERVAL,MQS.INTERVAL),PD_DQS.INTERVAL),PDR_DQS.INTERVAL) INTERVAL,  
   ISNULL(ISNULL(ISNULL(DQS.YEARMONTH,MQS.YEARMONTH),PD_DQS.YEARMONTH),PDR_DQS.YEARMONTH) YEARMONTH,    
   ISNULL(ISNULL(DQS.CODE,MQS.CODE),PD_DQS.CODe) CODE,  
   ISNULL(ISNULL(DQS.CODE2,MQS.CODE2),PD_DQS.CODE2) CODE2,    
   ISNULL(ISNULL(ISNULL(DQS.TAX_ZONE,MQS.TAX_ZONE),PD_DQS.TAX_ZONE),PDR_DQS.TAX_ZONE) TAX_ZONE,    
   ISNULL(ISNULL(ISNULL(DQS.TP_ZONE,MQS.TP_ZONE),PD_DQS.TP_ZONE),PDR_DQS.TP_ZONE) TP_ZONE,    
   DQS.INT_QEI DQSI,  
   MQS.INT_QEI MQSI,  
   DQS.INT_QEW DQSW,  
   MQS.INT_QEW MQSW,  
   PD_DQS.INT_QEI PD_DQSI,  
   PD_DQS.INT_QEW PD_DQSW,  
   PDR_DQS.INT_QEI DA_DQSI,  
   PDR_DQS.INT_QEW DA_DQSW,  
   DQS.SCHED_ID DQS_SCHED_ID,  
   MQS.SCHED_ID MQS_SCHED_ID,  
   PD_DQS.SCHED_ID PD_DQS_SCHED_ID,  
   PDR_DQS.SCHED_ID PDR_DQS_SCHED_ID,  
   PDR_DQS.IHO  
   FROM ( SELECT V.* FROM (SELECT * FROM #tmp_ontsqeview_211 WHERE sched_type_id=1) V  INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') DQS  
  FULL OUTER JOIN    
   (SELECT V.* FROM (SELECT * FROM #tmp_ontsqeview_1116) V  INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') PDR_DQS    
   ON (DQS.entity1_id = PDR_DQS.entity1_id  
   AND DQS.start_dt = PDR_DQS.start_dt)  
   OR (MQS.entity1_id = PDR_DQS.entity1_id    
   AND MQS.start_dt = PDR_DQS.start_dt)
   OR (PD_DQS.entity1_id = PDR_DQS.entity1_id  
   AND PD_DQS.start_dt = PDR_DQS.start_dt) ) V  LEFT OUTER JOIN (SELECT data_list_id.data_id, dla.ann_txt, dla.ann_id

Could be helpful to know specifically what "xxxx" was.  

You could try selecting all columns, in case column "A" (with 1 in it) is being used.  So, instead of V.*, use just *:

/* SELECT * rather than V.* wherever it appears */
FROM (SELECT * FROM #tmp_ontsqeview_211 WHERE sched_type_id=1) V  INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') DQS  
  FULL OUTER JOIN

Sorry XXXX was these fields MQS.LOC_ID, PD_DQS.LOC_ID, MQS.RESOURCE, PD_DQS.RESOURCE, MQS.AGGREGATE, PD_DQS.AGGREGATE..etc..MQS.entity1_id,  MQS.start_dt, PD_DQS.entity1_id, PD_DQS.start_dt  but not all of them.

Didn't make a difference. Same issue. Here is the latest SQL, just left the V.* in.
TEST100.sql

Weird.  The error is get is:

Msg 207, Level 16, State 1, Line 206
Invalid column name 'IHO'.

Which corresponds to:

  PD_DQS.SCHED_ID PD_DQS_SCHED_ID,  
  PDR_DQS.SCHED_ID PDR_DQS_SCHED_ID,  
 PDR_DQS.IHO  
  FROM ( SELECT V.* FROM (SELECT * FROM #tmp_ontsqeview_111 WHERE sched_type_id=1) AS V INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') DQS  
   FULL OUTER JOIN    

which makes sense, because the #tmp_ontsqeview_1116 table indeed does not have a column with name "IHO".

Hold on I think I am missing a part of the query at the bottom and that may fix it.

Yeah I fixed that problem about IHO, had to create another column in and bring the data.

Thanks Scott. Your solution worked for me after I fixed up the remaining stuff. Now to optimize the darn thing!

I can't run the code further because I don't have the other tables:
sett_data_list, etc.