quote:

---

Originally posted by Kristen

& "LEFT OUTER JOIN " _& "(SELECT ClientNo, JoinDate AS AUD, NULL AS CSS, NULL AS 
                     ^^----------

Assuming there is NO line break in there, as you have shown it, I reckon that's the problem.

How you can hope to debug your code with such dreadful formatting is beyond my ken.

You should use YYYYMMDD style string dates, and no YYYY-MM-DD

I can't work out why you have OUTER JOINS and then an IS NOT NULL test in your WHERE clause

Or why your nested sub-selects are including dummy columns with dummy values which are unreferenced.

Unless you have only a handful of records I reckon this query will run like a dog!

Here's how I formatted your code so I had a chance of seeing what was going on

SELECT	Client.ClientNo, 
	Client.Company, 
	VIEW_CLIENT_DETAILS.[Group], 
	VIEW_MRAUD.AUD, 
	VIEW_MRAUD.AUDFees, 
	VIEW_MRAUD.noofAUD, 
	VIEW_MRTAX.TAX, 
	VIEW_MRTAX.TAXFees,
	VIEW_MRTAX.noofTAX, 
	VIEW_MRCSS.CSS, 
	VIEW_MRCSS.CSSFees, 
	VIEW_MRCSS.noofCSS,
	VIEW_MRSA.SA,
	VIEW_MRSA.SAFees, 
	VIEW_MRSA.noofSA, 
	dbo.VIEW_CLIENT_DETAILS.CoStatus, 
	dbo.VIEW_CLIENT_DETAILS.ReferredBy, 
	dbo.VIEW_CLIENT_DETAILS.RefFeePayable, 
	dbo.VIEW_CLIENT_DETAILS.RefFeeScheme,
	dbo.VIEW_CLIENT_DETAILS.Name AS Contact, 
	dbo.VIEW_ALL_EXSCV.ExAud, 
	dbo.VIEW_ALL_EXSCV.ExTax,
	dbo.VIEW_ALL_EXSCV.ExSec, 
	VIEW_AIC_PIC.Initial AS AIC, 
	VIEW_SIC_PIC.Initial AS SIC,
	VIEW_TIC_PIC.Initial AS TIC, 
	VIEW_PIC_RP.Initial AS RP,
	VIEW_PIC_CSP.Initial AS CSP 
FROM Client 
	INNER JOIN VIEW_CLIENT_DETAILS 
		ON Client.ClientNo = VIEW_CLIENT_DETAILS.ClientNo 
	LEFT OUTER JOIN VIEW_ALL_EXSCV 
		ON Client.ClientNo = VIEW_ALL_EXSCV.ClientNo 
	LEFT OUTER JOIN VIEW_PIC_AIC 
		ON Client.ClientNo = VIEW_PIC_AIC.ClientNo 
	LEFT OUTER JOIN VIEW_PIC_SIC 
		ON Client.ClientNo = VIEW_PIC_SIC.ClientNo 
	LEFT OUTER JOIN VIEW_PIC_TIC 
		ON Client.ClientNo = VIEW_PIC_TIC.ClientNo 
	LEFT OUTER JOIN VIEW_PIC_CSP 
		ON Client.ClientNo = VIEW_PIC_CSP.ClientNo 
	LEFT OUTER JOIN VIEW_PIC_RP 
		ON Client.ClientNo = VIEW_PIC_RP.ClientNo 
	LEFT OUTER JOIN
	(
		SELECT ClientNo, 
			JoinDate AS AUD, 
			NULL AS CSS, 
			NULL AS TAX,
			NULL AS SA, 
			0 AS noofCSS, 
			COUNT(SvcCode) AS noofAUD, 
			0 AS noofTAX, 
			0 AS noofSA, 
			Fee as AUDFees 
		From VIEW_Client_SvcJoinDetail_Fees 
		WHERE	(SvcCode = 'AUD')
			AND (JoinDate >= '" & Format(asP(1), "yyyy-mm-dd") & "') 
			AND (JoinDate <= '" & Format(asP(2), "yyyy-mm-dd") & "') 
		GROUP BY ClientNo, SvcCode, Fee, JoinDate
	) CH_VIEW_MRAUD 
		ON Client.ClientNo = CH_VIEW_MRAUD.ClientNo 
	LEFT OUTER JOIN
	(
		SELECT	ClientNo, 
			NULL AS AUD, 
			NULL AS CSS, 
			JoinDate AS TAX, 
			NULL AS SA, 
			0 AS noofCSS, 
			0 AS noofAUD, 
			COUNT(SvcCode) AS noofTAX, 
			0 AS noofSA,
			Fee as TAXFees 
		From VIEW_Client_SvcJoinDetail_Fees 
		WHERE	    (SvcCode = 'TAX') 
			AND (JoinDate >= '" & Format(asP(1), "yyyy-mm-dd") & "') 
			AND (JoinDate <= '" & Format(asP(2), "yyyy-mm-dd") & "') 
		GROUP BY ClientNo, SvcCode,Fee, JoinDate
	) CH_VIEW_MRTAX
		ON Client.ClientNo = CH_VIEW_MRTAX.ClientNo 
	LEFT OUTER JOIN 
	(
		SELECT	ClientNo, 
			NULL AS AUD, 
			JoinDate AS CSS, 
			NULL AS TAX, 
			NULL AS SA, 
			COUNT(SvcCode) AS noofCSS, 
			0 AS noofAUD, 
			0 AS noofTAX, 
			0 AS noofSA,Fee as CSSFees 
		From VIEW_Client_SvcJoinDetail_Fees 
		WHERE	    (SvcCode = 'CSS') 
			AND (JoinDate >= '" & Format(asP(1), "yyyy-mm-dd") & "') 
			AND (JoinDate <= '" & Format(asP(2), "yyyy-mm-dd") & "') 
		GROUP BY ClientNo, SvcCode,Fee,JoinDate
	) CH_VIEW_MRCSS
		ON Client.ClientNo = CH_VIEW_MRCSS.ClientNo 
	LEFT OUTER JOIN 
	(
		SELECT	ClientNo, 
			NULL AS AUD, 
			NULL AS CSS, 
			NULL AS TAX, 
			JoinDate AS SA, 
			0 AS noofCSS, 
			0 AS noofAUD, 
			COUNT(SvcCode) AS noofSA, 
			0 AS noofTAX, Fee as SAFees 
		From VIEW_Client_SvcJoinDetail_Fees 
		WHERE	    (SvcCode = 'SA')
			AND (JoinDate >= '" & Format(asP(1), "yyyy-mm-dd") & "') 
			AND (JoinDate <= '" & Format(asP(2), "yyyy-mm-dd") & "') 
		GROUP BY ClientNo, SvcCode,Fee,JoinDate
	) CH_VIEW_MRSA 
		ON Client.ClientNo = CH_VIEW_MRSA.ClientNo 
where
	   CH_view_mraud.aud is not null 
	or CH_view_mrtax.tax is not null 
	or CH_view_mrcss.css is not null 
	or CH_view_mrsa.sa is not null 
order by Client.clientno

Kristen

---

quote:

---

Originally posted by Kristen

I presume we are taking about a VB syntax error, and not a SQL Syntax error - i.e. an error reported back by the server?

If its from the server knowing the exact error message may help.

(Either way I didn't see any, but its a big bit of script)

Kristen

---

quote:

---

Originally posted by Kristen

I stuck it in my colour coding editor, and it loks fine in there. But the linebreaks in what you posted are wrong, so I'm assuming that you have genuinely got linebreaks after each trailing _

So you have a Programmers Editor with Regular Expression Find tool?

If so put the cursor at the start of the

assql = "SELECT

bit and do a FIND for

[^_]$

and check that the first thing it finds is the very final double-quote. Otherwise you have a line which does NOT end in "_"

Kristen

---

quote:

---

Originally posted by jen

what's the syntax error? vb should be able to point out the line number

you could also improve debugging by creating a stored procedure instead of creating the query like that



--------------------
keeping it simple...

---

quote:

---

Originally posted by jen

what's the syntax error? vb should be able to point out the line number

you could also improve debugging by creating a stored procedure instead of creating the query like that



--------------------
keeping it simple...

---