December 2, 2010 at 7:54 am
Hi,
I want to run the following query
select nr_praktijk, naam_praktijk, jaar_k, kwartaal_k, aantal from lvr_praktijken
inner join zsk_kwartalen on nr_praktijk = praktijk_nummer_k
left outer join (select praktijk_nummer, a.jaar, kwartaal, count(*) as aantal from zsk_bestanden a
inner join zsk_deliver on volgnummer = volgnummer_del group by praktijk_nummer, a.jaar, kwartaal)
on nr_praktijk = praktijk_nummer and jaar_k = a.jaar and kwartaal_k = kwartaal
where cast((cast(jaar_k as char(4)) + cast(kwartaal_k as char(1))) as int) <= cast((cast(datepart(yy, GETDATE ()) as char(4)) + cast(datepart(q, GETDATE ()) as char(1))) as int)
order by nr_praktijk, jaar_k, kwartaal_k
The purpose of the part
-------------------------------------------------------------------------------------------------------------
select praktijk_nummer, a.jaar, kwartaal, count(*) as aantal from zsk_bestanden a
inner join zsk_deliver on volgnummer = volgnummer_del group by praktijk_nummer, a.jaar, kwartaal
-------------------------------------------------------------------------------------------------------------
is to calculate the number (of surveys) for a certain supplier (praktijk_nummer), in certain year / quarter combination
The purpose of the part
-------------------------------------------------------------------------------------------------------------
select nr_praktijk, naam_praktijk, jaar_k, kwartaal_k, aantal from lvr_praktijken
inner join zsk_kwartalen on nr_praktijk = praktijk_nummer_k
-------------------------------------------------------------------------------------------------------------
is to make a combination of suppliers (nr_praktijk) with year / quarter (jaar_k, kwartaal_k)
The purpose of the part
-------------------------------------------------------------------------------------------------------------
left outer join ...................................................
......................................................................
on nr_praktijk = praktijk_nummer and jaar_k = a.jaar and kwartaal_k = kwartaal
-------------------------------------------------------------------------------------------------------------
is to combine the
number (of surveys) for a certain supplier (praktijk_nummer), in certain year / quarter combination
with
the combination of suppliers (nr_praktijk) with year / quarter (jaar_k, kwartaal_k)
When I want to run this query I get the following error message
--------------------------------------
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'on'.
--------------------------------------
I have tried all sorts of things but no solution
Any help is very much appreciated.
Regards
John
December 2, 2010 at 9:21 am
I think you need an alias name for that select statement on the LEFT OUTER JOIN.
-- Kit
December 7, 2010 at 6:08 am
Hi Kit,
I have tried using several aliases but without succes.
Using 2 tempoary tables and joining proofed to be a good solution but I would have preferred working withou these tables.
Regards,
John
code:
---------------------------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('zsk_ovz_prak_kwart','U') IS NOT NULL DROP TABLE zsk_ovz_prak_kwart
IF OBJECT_ID('zsk_ovz_prak_kwart_aant','U') IS NOT NULL DROP TABLE zsk_ovz_prak_kwart_aant
SELECT a.nr_praktijk, a.naam_praktijk, b.jaar, b.kwartaal INTO dbo.zsk_ovz_prak_kwart FROM lvr_praktijken a
INNER JOIN zsk_kwartalen b ON a.nr_praktijk = b.praktijk_nummer
SELECT b.praktijk_nummer, a.jaar, a.kwartaal, count(*) AS aantal INTO dbo.zsk_ovz_prak_kwart_aant FROM zsk_bestanden a
INNER JOIN zsk_deliver b ON a.volgnummer = b.volgnummer_del GROUP BY b.praktijk_nummer, a.jaar, a.kwartaal
SELECT a.nr_praktijk, a.naam_praktijk, a.jaar, a.kwartaal, ISNULL(b.aantal, 0)
FROM zsk_ovz_prak_kwart a LEFT OUTER JOIN zsk_ovz_prak_kwart_aant b
ON a.nr_praktijk = b.praktijk_nummer AND a.jaar = b.jaar AND a.kwartaal = b.kwartaal
WHERE CAST((CAST(a.jaar AS char(4)) + CAST(a.kwartaal AS char(1))) AS int) <= CAST((CAST(datepart(yy, GETDATE ()) AS char(4)) + CAST(DATEPART(q, GETDATE ()) as char(1))) as int)
ORDER BY a.nr_praktijk, a.jaar, a.kwartaal
---------------------------------------------------------------------------------------------------------------------------------------------------------------
December 7, 2010 at 6:41 am
Kit G (12/2/2010)
I think you need an alias name for that select statement on the LEFT OUTER JOIN.
Looks about right to me too.
Table aliases throughout would be ideal.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2010 at 8:26 am
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'on'.
Since you're getting a syntax error, that indicates you're missing something in your code which is why I suggested a table alias because SQL sometimes needs a table name, not an expression. Have you checked to make sure you have all the open and closed parentheses you need?
There is something missing near where you are getting your syntax error, either something isn't paired up correctly, a JOIN statement and the ON statement or something else is messing up the parsing of the query.
You might need to just test each section to make sure it parses and then put it together bit by bit to find out where the parser suddenly doesn't like it.
If you want any further testing, please supply some CREATE scripts and sample data (see Chris Morris' post for the link to an article detailing the necessary info). Without that, I can't really see where things are failing.
-- Kit
December 7, 2010 at 8:36 am
Isn't it just that you need to alias your subquery (as Kit originally suggested) - something like this:
select nr_praktijk, naam_praktijk, jaar_k, kwartaal_k, aantal from lvr_praktijken
inner join zsk_kwartalen on nr_praktijk = praktijk_nummer_k
left outer join (select praktijk_nummer, a.jaar, kwartaal, count(*) as aantal from zsk_bestanden a
inner join zsk_deliver on volgnummer = volgnummer_del group by praktijk_nummer, a.jaar, kwartaal) s
on nr_praktijk = s.praktijk_nummer and jaar_k = s.jaar and kwartaal_k = s.kwartaal
where cast((cast(jaar_k as char(4)) + cast(kwartaal_k as char(1))) as int) <= cast((cast(datepart(yy, GETDATE ()) as char(4)) + cast(datepart(q, GETDATE ()) as char(1))) as int)
order by nr_praktijk, jaar_k, kwartaal_k
John
December 7, 2010 at 8:39 am
Like this?
select nr_praktijk,
naam_praktijk,
jaar_k,
kwartaal_k,
aantal
from lvr_praktijken t1
inner join zsk_kwartalen t2
on t1.nr_praktijk = t2.praktijk_nummer_k
left outer join (select praktijk_nummer,
a.jaar,
kwartaal,
count(*) as aantal
from zsk_bestanden a
inner join zsk_deliver
on volgnummer = volgnummer_del
group by praktijk_nummer, a.jaar, kwartaal) t3 --<< an alaias here was missing!
on t1.nr_praktijk = t3.praktijk_nummer
and jaar_k = t3.jaar
and kwartaal_k = t3.kwartaal
where cast((cast(jaar_k as char(4)) + cast(kwartaal_k as char(1))) as int) <= cast((cast(datepart(yy, GETDATE ()) as char(4)) + cast(datepart(q, GETDATE ()) as char(1))) as int)
order by nr_praktijk, jaar_k, kwartaal_k
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply