locked
statement has a syntax error RRS feed

  • 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;
    Monday, October 3, 2011 9:36 AM

All replies

  • 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;

    Monday, October 3, 2011 9:55 AM
  • 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

    Tuesday, October 4, 2011 12:09 PM
  • 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? 

    Tuesday, October 4, 2011 5:12 PM
  • 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

    Wednesday, October 5, 2011 9:22 AM
  • 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

    Wednesday, October 5, 2011 9:38 AM
  • 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.

    Wednesday, October 5, 2011 9:44 AM
  • 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)

    Wednesday, October 5, 2011 9:54 AM
  • 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";
    Wednesday, October 5, 2011 11:22 AM