syntax error in ASP Classic SQL Query when using conditional IF

I am trying to sort out different elements in a ASP Classic SQL Query using IF and THEN, and have tried many different ways to get it to work .. the "problem child" is this:

StatsSQL = "SELECT COUNT(v.col) as num_not_null, COUNT(v.col) * 1.0 / COUNT(*) * 100 as percent_not_null, COUNT(*) as toltalColsNeedsFilled FROM EFP_EmploymentUser t CROSS APPLY (VALUES (t.ITAdvicedFirst),(t.ITAdvicedSecond),(t.ITDepartmentDone),(t.CFOAdvicedFirst),(t.CFOInfoProvided),(t.CFOAdvicedSecond),(t.CFODone),(t.EconomyAdviced),(t.EconomyDone)," & IF objFlowNotDone("Academy") = "yes" THEN Response.Write("(t.AcademyAdviced),(t.AcademyDone),") END IF IF objFlowNotDone("Publicator") = "yes" THEN Response.Write("(t.PublicatorAdviced),(t.PublicatorDone),") END IF & "(t.PortraitAdviced),(t.PortraitDone)) v(col) WHERE ID = '19';"

The Error I get:

Microsoft VBScript compilation error '800a03ea'

Syntax error

/flow.asp, line 271

Can anyone please help me in the right direction to solve this?

Best Regards

Stig :-)

Solution

You shouldn't be using Response.Write unless you actually want the contents of the Response.Write statement to be displayed in your output, and you really need to start a new line for your conditional statements. Here's what you appear to be trying to do. I'm assuming that your SQL is correct and the only issue here is with your VBScript.

StatsSQL = "SELECT COUNT(v.col) as num_not_null, COUNT(v.col) * 1.0 / COUNT(*) * 100 as percent_not_null, COUNT(*) as toltalColsNeedsFilled FROM EFP_EmploymentUser t CROSS APPLY (VALUES (t.ITAdvicedFirst),(t.ITAdvicedSecond),(t.ITDepartmentDone),(t.CFOAdvicedFirst),(t.CFOInfoProvided),(t.CFOAdvicedSecond),(t.CFODone),(t.EconomyAdviced),(t.EconomyDone)"

If objFlowNotDone("Academy") = "yes" THEN 
  StatsSQL = StatsSQL & ",(t.AcademyAdviced),(t.AcademyDone)"
End If

If objFlowNotDone("Publicator") = "yes" THEN
  StatsSQL = StatsSQL & ",(t.PublicatorAdviced),(t.PublicatorDone)"
End If

StatsSQL = StatsSQL & ",(t.PortraitAdviced),(t.PortraitDone)) v(col) WHERE ID = '19';"