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;
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;
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.
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#)
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:
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";
lm111
Member
89 Points
126 Posts
statement has a syntax error
Oct 03, 2011 01:36 PM|LINK
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;hans_v
All-Star
35986 Points
6550 Posts
Re: statement has a syntax error
Oct 03, 2011 01:55 PM|LINK
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;
lm111
Member
89 Points
126 Posts
Re: statement has a syntax error
Oct 04, 2011 04:09 PM|LINK
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
hans_v
All-Star
35986 Points
6550 Posts
Re: statement has a syntax error
Oct 04, 2011 09:12 PM|LINK
In which table(s) do you want to check the dateSent field?
lm111
Member
89 Points
126 Posts
Re: statement has a syntax error
Oct 05, 2011 01:22 PM|LINK
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
hans_v
All-Star
35986 Points
6550 Posts
Re: statement has a syntax error
Oct 05, 2011 01:38 PM|LINK
This is correct syntax
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
lm111
Member
89 Points
126 Posts
Re: statement has a syntax error
Oct 05, 2011 01:44 PM|LINK
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.
hans_v
All-Star
35986 Points
6550 Posts
Re: statement has a syntax error
Oct 05, 2011 01:54 PM|LINK
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)
lm111
Member
89 Points
126 Posts
Re: statement has a syntax error
Oct 05, 2011 03:22 PM|LINK
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.