Hey folks.. My database utilizes a VB frontend that filters based on
user input, date, geo etc. It takes this user input and places it
wherever i place a /*whereclause*/ in my sql. What I am trying to do
for one report is grab the date that the user enters and use it in an
if statement as follows:

IF StatYear = (YEAR(CONVERT(DATE,substring('/*WHERECLAUSE*/',
CHARINDEX('>=','/*WHERECLAUSE*/') + 5, 4), 111)) -3)

when i run it, i get the following error:

[Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
violation: near '2002' in ...((([datDateSel] >= '[2002]-1-1' ) and
([...

I don't know if it's the single quotes that are messing it up or
what... any ideas? If you need more info, just let me know.

dave

Dave Foster wrote:
>
> Hey folks.. My database utilizes a VB frontend that filters based on
> user input, date, geo etc. It takes this user input and places it
> wherever i place a /*whereclause*/ in my sql. What I am trying to do
> for one report is grab the date that the user enters and use it in an
> if statement as follows:
>
> IF StatYear = (YEAR(CONVERT(DATE,substring('/*WHERECLAUSE*/',
> CHARINDEX('>=','/*WHERECLAUSE*/') + 5, 4), 111)) -3)
>
> when i run it, i get the following error:
>
> [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> violation: near '2002' in ...((([datDateSel] >= '[2002]-1-1' ) and
> ([...
>
> I don't know if it's the single quotes that are messing it up or
> what... any ideas? If you need more info, just let me know.

It looks like it the square brackets around the year component
that's the problem. That's definitely wrong as far as ASE is
concerned. Note that you can pass dates as a string and have
ASE parse them. You can even do a like pattern match with them.

-am © 2003

Dave Foster wrote:

> That's a good point.. I don't know where those brackets are coming
> from. That /*whereclause*/ should result in something like
> WHERE (([datDateSel] >= '2002-1-1' ) and ([datDateSel] >= '2002-12-31' ))

What's putting the square brackets around the field name?
[datDateSel] is also invalid - unless this is a place marker
for substitution within VB (although your original post showed
this in the returned ODBC error message). I also just noticed
the the ODBC error message says -

[Sybase][ODBC Driver][Adaptive Server Anywhere]

so this is ASA and not ASE, so some of my comments may be incorrect.
If ASA accepts the square brackets, the problem is elsewhere. I'm
not sure what ASA's date format styles are.

Or, could it be that you are using the wrong ODBC drive? ASA and
ASE are not interoperable.

-am © 2003

Dave Foster wrote:
>
> I'm not sure where the brackets are coming from, but the other reports
> that use the same whereclause work without a hitch. It's when I try
> to pull values out of it with this string manipulation stuff that it
> coughs up an error.

OK, the original message was complaining about the brackets around
"[2002]". Since you're using ASA, my other comment probably doesn't
apply. If it works elsewhere, the syntax should be fine. You could
try it manually against the database to confirm that its not the
database itself that's having the problem.

> Yes, we use ASA, I should have clarified that sooner.

OK, ASA has its own ODBC driver - which you are using (based on
the string in the error message). I'd just check the version of
the ODBC driver against the backend. If ASA is as fussy as ASE,
a wrong driver version could be causing the problem.

-am © 2003

OK, it seems the brackets appear around whatever the error handling
has a problem with, so they provide nothing more than a highlight on
the broken spot. What I think may be the issue is the fact that the
phrase that gets substituted for the /*WHERECLAUSE*/ comment holds the
date as a string (ie '2002-01-01'), so my string is actually ending
before the date.

Example:
IF StatYear = (YEAR(CONVERT(DATE,substring('/*WHERECLAUSE*/',
CHARINDEX('datDateSel','/*WHERECLAUSE*/') + 15, 4), 111)) -3)

Will become:
IF StatYear = (YEAR(CONVERT(DATE,substring('WHERE ((datDateSel >=
'2002-01-01' ) and (datDateSel <= '2002-12-31' )),
CHARINDEX('datDateSel','WHERE ((datDateSel >= '2002-01-01' ) and
(datDateSel <= '2002-12-31') + 15, 4), 111)) -3)

I think it would work if I could specify the date in that where clause
without the single quotes.



Anthony Mandic <qe@hotmail.com> wrote in message news:<3F2B33DE.34E86DC0@hotmail.com>...
> Dave Foster wrote:
> >
> > I'm not sure where the brackets are coming from, but the other reports
> > that use the same whereclause work without a hitch. It's when I try
> > to pull values out of it with this string manipulation stuff that it
> > coughs up an error.
>
> OK, the original message was complaining about the brackets around
> "[2002]". Since you're using ASA, my other comment probably doesn't
> apply. If it works elsewhere, the syntax should be fine. You could
> try it manually against the database to confirm that its not the
> database itself that's having the problem.
>
> > Yes, we use ASA, I should have clarified that sooner.
>
> OK, ASA has its own ODBC driver - which you are using (based on
> the string in the error message). I'd just check the version of
> the ODBC driver against the backend. If ASA is as fussy as ASE,
> a wrong driver version could be causing the problem.
>
> -am © 2003

Allright, actually I got this to work using two single quotes in the
date filtering section of the WHERE clause.
Thanks for the help :-)

Now I have another question: Do you know of a way that I can
manipulate the WHERE clause itself in SQL? As I've said, we
substitute a WHERE clause in our sql whereever we put the comment
/*WHERECLAUSE*/. What I need to do now is grab just the first part of
the WHERE clause that results and use that. I can grab exactly what I
need using the substring function, but only in the SELECT section.
So, I've grabbed what I need and stored it in a variable... is there a
way to call that variable for use in a WHERE clause? Say I've stored
it in the variable WHERESTRING. Can I do something like

WHERE WHERESTRING AND blah blah blah?

(I've tried that with no results)