statement has a syntax error

Question

User-2123122692 posted

Could someone please help me figure out why I have a syntax error. Thanks.

(Syntax error (missing operator) in query expression ‘DataSource3.publisher = DataSource1.publisher LEFT OUTER JOIN (SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT……….)

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT, 
IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign 
FROM (SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign FROM  evaluations GROUP BY publisher) AS DataSource1 LEFT OUTER  JOIN
                          (SELECT publisher, COUNT(ps_design) AS CountDesign
                            FROM  evaluations AS Table_1_2
                            WHERE (ps_design <> "na")
                            GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher LEFT OUTER  JOIN
                          (SELECT  publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT  FROM evaluations AS Table_1_1
                            WHERE (ps_projectmgmt <> "na")  GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher;

Answer 1

User-1199946673 posted

Try:

SELECT          DataSource1.publisher,
                IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,
                IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign 
FROM            ((SELECT   publisher,
                           SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT,
                           SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign
                  FROM     evaluations
                  GROUP BY publisher) AS DataSource1
LEFT OUTER JOIN  (SELECT   publisher,
                           COUNT(ps_design) AS CountDesign
                  FROM     evaluations AS Table_1_2
                  WHERE    (ps_design <> "na")
                  GROUP BY publisher) AS DataSource3
ON               DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT   publisher,
                          COUNT(ps_projectmgmt) AS CountProjectMGMT
                 FROM     evaluations AS Table_1_1
                 WHERE    (ps_projectmgmt <> "na")
                 GROUP BY publisher) AS DataSource2
ON              DataSource2.publisher = DataSource1.publisher;

Answer 2

User-2123122692 posted

Thank you, that removed the syntax error. I have one other question. I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

WHERE dateSent>=#01/01/2011#

Any idea where I can add the piece above?

Thanks

Answer 3

User-1199946673 posted

I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

WHERE dateSent>=#01/01/2011#

Any idea where I can add the piece above?

In which table(s) do you want to check the dateSent field? 

Answer 4

User-2123122692 posted

The dateSent field is in my evaluations table. Could we check it somehow in the following block of code:

(SELECT   publisher,
                           COUNT(ps_design) AS CountDesign 
                  FROM     evaluations AS Table_1_2 
                  WHERE    (ps_design <> "na") 
                  GROUP BY publisher) AS DataSource3
ON               DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT   publisher,
                          COUNT(ps_projectmgmt) AS CountProjectMGMT 
                 FROM     evaluations AS Table_1_1 
                 WHERE    (ps_projectmgmt <> "na")
                 GROUP BY publisher

I tried something like this but the output is not right

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#) 

Thanks

Answer 5

User-1199946673 posted

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#) 

This is correct syntax

I tried something like this but the output is not right

The only way to solve this is that you try to explain in words which result you want. Show also some records and the result you want

Answer 6

User-2123122692 posted

Hello,

Here is the link to my other thread that explains it better.

http://forums.asp.net/t/1723766.aspx/1?Display+correct+data+in+GridView

The answer from 'mezzanine74' works fine I just need to figure out how to add the dateSent field.

Thank you so much.

Answer 7

User-1199946673 posted

I really don't understand what result you want. But to help you a little bit further:

When a field can contain either a numeric value, NULL of "na", You can use the following IIF function to change NULL and "na" in 0, so you can calculate with it:

IIF(IsNumeric([ps_projectmgmt]), [ps_projectmgmt], 0)

Answer 8

User-2123122692 posted

Thanks Hans, it's really difficult to explain the problem I am having. For some reason when I add the dateSent field some output for AvgProjectMGMT and AvgDesign are weird numbers like 170 and 19. But it should be the average like 5 or 4.5 or ...) When I remove the dateSent field from my query everything works great. I just don;t understand why the dateSent field would mess things up. Could you please take  look again? Thanks.

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),'n/a', ROUND(Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT),1)) AS AvgProjectMGMT, IIf(IsNull([CountDesign]),'n/a', ROUND(Val(DataSource1.SumDesign/DataSource3.CountDesign),1)) AS AvgDesign FROM ((SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]='na',0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_design]) OR [ps_design]='na',0, Val([ps_design]))) AS SumDesign FROM  evaluations  GROUP BY publisher) AS DataSource1 LEFT OUTER JOIN (SELECT publisher, COUNT(ps_design) AS CountDesign FROM  evaluations AS Table_1_2 WHERE ((ps_design <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher) LEFT OUTER JOIN (SELECT  publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT  FROM evaluations AS Table_1_1 WHERE ((ps_projectmgmt <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher";