Why isn't my sql code working?

Question

the debugger gets stuck at the where clause after the CASE statement:

sqlEPConn.Open()

vSQL = "select qtr, (Select count(RecordID) from cm_Main where OriginationID = 1 " _

& "and ProcDate between Quarters.StartDate and Quarters.EndDate) as sds_Count, " _

& "(select CASE when EndDate > GetDate() THEN '*' ELSE '' END) as DaysLeft from Quarters " _

& "where (select count(RecordID) from cm_Main where OriginationID = 1 and ProcDate " _

& "between Quarters.StartDate and Quarters.EndDate)"

'& "order by Quarters.StartDate desc"

sqlCmd = New SqlCommand(vSQL, sqlEPConn)

r = sqlCmd.ExecuteReader(CommandBehavior.Clo...

While r.Read

.Rows.Add(r("qtr"), r("sds_Count"))

End While

End With

sqlEPConn.Close()

End Sub

I've been working on this for HOURS and can't figure it out!!!

Answer 1

it's called MySQL not "my sql"  

Answer 2

I think it is because the Select within the Where is returning a number, Count(RecordID).

So basically your Where clause is saying "WHERE 1 ORDER By Quarters.StartDate DESC".

So you need to put in a "fieldname=" in front of that SELECT, don't you think?

Answer 3

...

where (select count(RecordID) from cm_Main where OriginationID = 1 and ProcDate between Quarters.StartDate and Quarters.EndDate)

Is the problem... maybe not the parentheses, but it doesn't seem to evaluate to anything the outside select can check against.

The query

select count(RecordID) from cm_Main where OriginationID = 1 and ProcDate between Quarters.StartDate and Quarters.EndDate

returns one or more values, I assume... what is the WHERE clause supposed to look for?

Have you tried taking the query apart and run each part directly in a Query Analyzer to see if each of these parts returns what you expect them to?

Debugging SQL code from within a dynamically created string within code is... not the most optimal way to do things.

I usually make sure I have working SQL code, built part by part, from the inside out, and then I throw the result into a string variable.

Answer 4

looks to me like the problem is the placement of your closed parenthesis mark

where (select count(RecordID) from cm_Main where OriginationID = 1 and between Quarters.StartDate and Quarters.EndDate)

try changing it to

where (select count(RecordID) from cm_Main where OriginationID = 1) and between Quarters.StartDate and Quarters.EndDate