Help with clearning syntax error in Complex SQL Server Query

I need some help With SQL Server query. Getting some errors with this query I am trying to re-write. Can someone help to remove the syntax errors? The query in question is the SELECT query...the large one.

Here are some of the syntax errors I am getting in the main query.

Msg 102, Level 15, State 1, Line 209
Incorrect syntax near 'DQS'.
Msg 156, Level 15, State 1, Line 211
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 211
Incorrect syntax near 'tfltr_DATE'.
Msg 156, Level 15, State 1, Line 215
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 215
Incorrect syntax near 'tfltr_DATE'.
Msg 102, Level 15, State 1, Line 221
Incorrect syntax near 'V'.
Msg 102, Level 15, State 1, Line 221
Incorrect syntax near 'tfltr_DATE'.
Msg 102, Level 15, State 1, Line 239
Incorrect syntax near 'ann'.
Msg 102, Level 15, State 1, Line 270
Incorrect syntax near 'DQS'.
Msg 102, Level 15, State 1, Line 272
Incorrect syntax near 'V'.
Msg 102, Level 15, State 1, Line 272
Incorrect syntax near 'tfltr_DATE'.
Msg 102, Level 15, State 1, Line 289
Incorrect syntax near 'ann'.
TEST99.sql
LVL 1
mathew_sAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open 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

Open in new window

Scott PletcherSenior DBACommented:
Just the SELECT has syntax issues.  You need a leading SELECT not within () to make it valid syntax:


SELECT 0, 0, V.[RESOURCE],V.[HOUR],CONVERT(VARCHAR,V.[DATE],101) as DATE,AVG(V.[DQSI]),AVG(V.[DQSW]),AVG(V.[MQSI]),AVG(V.[MQSW]), SUM(V.has_comm_flg), MAX(V.ann_id)      
FROM (  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_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    
  ( SELECT V.* FROM (SELECT * FROM #tmp_ontsqeview_111 WHERE sched_type_id=4) AS V  INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') MQS    
  ON (DQS.entity1_id = MQS.entity1_id  
  AND DQS.start_dt = MQS.start_dt)
  FULL OUTER JOIN    
  (SELECT V.* FROM (SELECT * FROM #tmp_ontsqeview_111 WHERE sched_type_id=11) AS V  INNER JOIN (SELECT 1 A)  tfltr_DATE ON V.DATE BETWEEN '20180201' AND '20180201') PD_DQS    
  ON (DQS.entity1_id = PD_DQS.entity1_id  
  AND DQS.start_dt = PD_DQS.start_dt)  
  OR (MQS.entity1_id = PD_DQS.entity1_id  
  AND MQS.start_dt = PD_DQS.start_dt)
  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  
       FROM (SELECT data_id, MAX(dl.data_list_id) max_data_list_id FROM sett_data_list dl  
        INNER JOIN sett_data_list_data dld  
        ON dl.data_list_id = dld.data_list_id  
       INNER JOIN sett_data_list_ann dla  
        ON dld.data_list_id = dla.data_list_id  
       WHERE type = 'SCHED'  
       AND ann_state = 0          
       GROUP BY data_id) data_list_id  
       INNER JOIN sett_data_list_ann dla  
       ON data_list_id.max_data_list_id = dla.data_list_id  
       WHERE dla.ann_state = 0) ann  
      ON V.DQS_SCHED_ID = ann.data_id  
  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  
   FROM (SELECT data_id, MAX(dl.data_list_id) max_data_list_id FROM sett_data_list dl
        INNER JOIN sett_data_list_data dld  
        ON dl.data_list_id = dld.data_list_id  
       INNER JOIN sett_data_list_ann dla  
        ON dld.data_list_id = dla.data_list_id  
       WHERE type = 'SCHED'  
       AND ann_state = 0          
       GROUP BY data_id) data_list_id  
       INNER JOIN sett_data_list_ann dla  
       ON data_list_id.max_data_list_id = dla.data_list_id  
       WHERE dla.ann_state = 0) ann  
      ON V.DQS_SCHED_ID = ann.data_id  ) V GROUP BY V.[RESOURCE],V.[HOUR],CONVERT(VARCHAR,V.[DATE],101)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alpa buddhabhattiCommented:
hi can you please send view ont_sqe_view_energysched
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

mathew_sAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
mathew_sAuthor Commented:
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.
mathew_sAuthor Commented:
Didn't make a difference. Same issue. Here is the latest SQL, just left the V.* in.
TEST100.sql
Scott PletcherSenior DBACommented:
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".
mathew_sAuthor Commented:
Hold on I think I am missing a part of the query at the bottom and that may fix it.
mathew_sAuthor Commented:
Yeah I fixed that problem about IHO, had to create another column in and bring the data.
mathew_sAuthor Commented:
Thanks Scott. Your solution worked for me after I fixed up the remaining stuff. Now to optimize the darn thing!
Scott PletcherSenior DBACommented:
I can't run the code further because I don't have the other tables:
sett_data_list, etc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.