|
Home > Archive > SQL Anywhere database > June 2005 > Upsize from Access and getting "Unhandled Exception" errors.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Upsize from Access and getting "Unhandled Exception" errors.
|
|
|
| Hello everyone, I upsized an Access Database to SQL Anywhere, and I am
trying to run the following SQL statement:
SELECT LeadCust.LEADSTATUS,
LeadCust.Incomplete,
LeadCust.LASTNAME,
LeadCust.FIRSTNAME,
LeadCust.COMPANY,
LeadCust.COUNTRY,
LeadCust.PHONE,
LeadCust.FAX,
LeadCust.STATE,
LeadCust.EMAIL,
Employee.Initials,
LeadCust.LeadCustUsage,
Tickler.ContactTime AS ContactDate ,
'' AS PRODSTATUS ,
'' AS PRODITEM ,
'' AS STATUSDATE ,
'' AS ITEMDATE
FROM LeadCust,
Employee,
Tickler
WHERE Employee.ID =
IIf(Nz(LeadCust. Employee)=0,IIf(Nz(L
eadCust. Employee2)=0,48,Lead
Cust.Employee2),LeadCust.Employee)
AND Tickler.LeadCust = LeadCust.ID
AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
11:59:59 PM#)
AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
AND EXISTS (SELECT '*' FROM ProductRef
WHERE ProductRef.Customer = LeadCust.ID
AND ProductRef.Status = 0);
The upsize was done where the data (back end) was converted to SQL Anywhere
tables and the front-end still runs under Access using ODBC.
So this statement has to run under Access as part of the front-end.
This statement causes me to get an Access "Unhandled Exception Error" (with
the "Send / Don't Send" to Microsoft buttons on it).
What's weird about this is that I can modify the WHERE statement, where I
take out any one of the conditions and then it works with no problems!
Please help, I can not figure out why it's doing this - I can't trap the
error nor can I isolate the problem to any one of the conditions - because
the statement will work if I remove ANY ONE of the conditions in the where
clause (I've tried all the combinations and none of the conditions on its
own appears to be problematic - for some reason, only when they're
together). It seems as though the WHERE clause is too big ?? (which itself
doesn't make any sense to me).
I am at a loss as to what to do or where to look next, so if anyone can help
or share whether they've had the same problem and resolved it, your help
would be very greatly appreciated !
Thank you very much,
Paul Grossman
PAULGRO Consulting LLC.
| |
| Chris Keating \(iAnywhere Solutions\) 2005-06-14, 9:23 am |
| The IIf statement is the likely problem. It is not valid. Try converting the
IFF to an IF expression (as distinct from an IF statement).
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42ae50f5$1@foru
ms-1-dub...
> Hello everyone, I upsized an Access Database to SQL Anywhere, and I am
> trying to run the following SQL statement:
>
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> IIf(Nz(LeadCust. Employee)=0,IIf(Nz(L
eadCust. Employee2)=0,48,Lead
Cust.Employee2),LeadCust.Employee)
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> The upsize was done where the data (back end) was converted to SQL
> Anywhere tables and the front-end still runs under Access using ODBC.
> So this statement has to run under Access as part of the front-end.
>
> This statement causes me to get an Access "Unhandled Exception Error"
> (with the "Send / Don't Send" to Microsoft buttons on it).
>
> What's weird about this is that I can modify the WHERE statement, where I
> take out any one of the conditions and then it works with no problems!
>
> Please help, I can not figure out why it's doing this - I can't trap the
> error nor can I isolate the problem to any one of the conditions - because
> the statement will work if I remove ANY ONE of the conditions in the where
> clause (I've tried all the combinations and none of the conditions on its
> own appears to be problematic - for some reason, only when they're
> together). It seems as though the WHERE clause is too big ?? (which
> itself doesn't make any sense to me).
>
> I am at a loss as to what to do or where to look next, so if anyone can
> help or share whether they've had the same problem and resolved it, your
> help would be very greatly appreciated !
>
> Thank you very much,
>
> Paul Grossman
> PAULGRO Consulting LLC.
>
| |
|
| Hello Chris, thank you for your response.
I tried using both the IF expression and the CASE expression based on the
syntax shown in the "ASA SQL Reference". Could you check the 2 SQL
Statements and their error messages below and
let me know if I don't have the syntax right for these? I'm wondering if
Microsoft Jet just doesn't support these expressions? I'm using Access 2002
and 2003 and on both versions I got these problems:
Thank you again for your help
Sincerely,
Paul Grossman
PAULGRO Consulting LLC
For the IF expression, I got the following error message:
"Syntax Error (missing operator) in query expression
Employee.ID =
IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
ELSE 48
END IF
AND Tickler.LeadCust = LeadCust.ID
AND (Tickler.ContactTime Is Null OR
Here is the SQL I used for the IF expression:
SELECT LeadCust.LEADSTATUS,
LeadCust.Incomplete,
LeadCust.LASTNAME,
LeadCust.FIRSTNAME,
LeadCust.COMPANY,
LeadCust.COUNTRY,
LeadCust.PHONE,
LeadCust.FAX,
LeadCust.STATE,
LeadCust.EMAIL,
Employee.Initials,
LeadCust.LeadCustUsage,
Tickler.ContactTime AS ContactDate ,
'' AS PRODSTATUS ,
'' AS PRODITEM ,
'' AS STATUSDATE ,
'' AS ITEMDATE
FROM LeadCust,
Employee,
Tickler
WHERE Employee.ID =
IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
ELSE 48
END IF
AND Tickler.LeadCust = LeadCust.ID
AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
11:59:59 PM#)
AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
AND EXISTS (SELECT '*' FROM ProductRef
WHERE ProductRef.Customer = LeadCust.ID
AND ProductRef.Status = 0);
I also tried putting END IF between the nested IF thinking that was the
problem and got the same error:
SELECT LeadCust.LEADSTATUS,
LeadCust.Incomplete,
LeadCust.LASTNAME,
LeadCust.FIRSTNAME,
LeadCust.COMPANY,
LeadCust.COUNTRY,
LeadCust.PHONE,
LeadCust.FAX,
LeadCust.STATE,
LeadCust.EMAIL,
Employee.Initials,
LeadCust.LeadCustUsage,
Tickler.ContactTime AS ContactDate ,
'' AS PRODSTATUS ,
'' AS PRODITEM ,
'' AS STATUSDATE ,
'' AS ITEMDATE
FROM LeadCust,
Employee,
Tickler
WHERE Employee.ID =
IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2 ENDIF
ELSE 48
END IF
AND Tickler.LeadCust = LeadCust.ID
AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
11:59:59 PM#)
AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
AND EXISTS (SELECT '*' FROM ProductRef
WHERE ProductRef.Customer = LeadCust.ID
AND ProductRef.Status = 0);
Here is what I tried for CASE expression:
SELECT LeadCust.LEADSTATUS,
LeadCust.Incomplete,
LeadCust.LASTNAME,
LeadCust.FIRSTNAME,
LeadCust.COMPANY,
LeadCust.COUNTRY,
LeadCust.PHONE,
LeadCust.FAX,
LeadCust.STATE,
LeadCust.EMAIL,
Employee.Initials,
LeadCust.LeadCustUsage,
Tickler.ContactTime AS ContactDate ,
'' AS PRODSTATUS ,
'' AS PRODITEM ,
'' AS STATUSDATE ,
'' AS ITEMDATE
FROM LeadCust,
Employee,
Tickler
WHERE Employee.ID =
( CASE
WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
ELSE 48
END )
AND Tickler.LeadCust = LeadCust.ID
AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
11:59:59 PM#)
AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
AND EXISTS (SELECT '*' FROM ProductRef
WHERE ProductRef.Customer = LeadCust.ID
AND ProductRef.Status = 0);
And I got this error message:
"Syntax Error (missing operator) in query expression
'Employee.ID =
( CASE
WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
ELSE 48
END )
AND Tickler.LeadCust = LeadCust.ID
AND "
"Chris Keating (iAnywhere Solutions)" < Spam_NoThanks_keatin
g@iAnywhere.com>
wrote in message news:42aed2e5$1@foru
ms-2-dub...
> The IIf statement is the likely problem. It is not valid. Try converting
> the IFF to an IF expression (as distinct from an IF statement).
>
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:42ae50f5$1@foru
ms-1-dub...
>
>
| |
|
| Just one more follow-up. I saw that in one case I used "END IF". I caught
that and also changed that to "ENDIF" but I still got the syntax error from
Access as indicated below.
Thank you again
Paul Grossman
PAULGRO Consulting LLC
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42af054b$1@foru
ms-1-dub...
> Hello Chris, thank you for your response.
>
> I tried using both the IF expression and the CASE expression based on the
> syntax shown in the "ASA SQL Reference". Could you check the 2 SQL
> Statements and their error messages below and
> let me know if I don't have the syntax right for these? I'm wondering if
> Microsoft Jet just doesn't support these expressions? I'm using Access
> 2002 and 2003 and on both versions I got these problems:
>
> Thank you again for your help
> Sincerely,
> Paul Grossman
> PAULGRO Consulting LLC
>
>
> For the IF expression, I got the following error message:
>
> "Syntax Error (missing operator) in query expression
>
> Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR
>
>
>
> Here is the SQL I used for the IF expression:
>
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> I also tried putting END IF between the nested IF thinking that was the
> problem and got the same error:
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2 ENDIF
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
>
> Here is what I tried for CASE expression:
>
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> ( CASE
> WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
> WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
> ELSE 48
> END )
>
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> And I got this error message:
>
> "Syntax Error (missing operator) in query expression
>
> 'Employee.ID =
> ( CASE
> WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
> WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
> ELSE 48
> END )
> AND Tickler.LeadCust = LeadCust.ID
> AND "
>
>
> "Chris Keating (iAnywhere Solutions)"
> < Spam_NoThanks_keatin
g@iAnywhere.com> wrote in message
> news:42aed2e5$1@foru
ms-2-dub...
>
>
| |
| Mark Culp 2005-06-14, 1:23 pm |
| You've nested two if's but only gave one endif... try
Employee.ID =
IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
ELSE 48
ENDIF
ENDIF
Note that the IF _statement_ supports ELSEIF but the IF _expression_
does not.
--
Mark Culp
SQLAnywhere Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
Paul wrote:[color=darkred
]
>
> Hello Chris, thank you for your response.
>
> I tried using both the IF expression and the CASE expression based on the
> syntax shown in the "ASA SQL Reference". Could you check the 2 SQL
> Statements and their error messages below and
> let me know if I don't have the syntax right for these? I'm wondering if
> Microsoft Jet just doesn't support these expressions? I'm using Access 2002
> and 2003 and on both versions I got these problems:
>
> Thank you again for your help
> Sincerely,
> Paul Grossman
> PAULGRO Consulting LLC
>
> For the IF expression, I got the following error message:
>
> "Syntax Error (missing operator) in query expression
>
> Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR
>
> Here is the SQL I used for the IF expression:
>
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> I also tried putting END IF between the nested IF thinking that was the
> problem and got the same error:
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2 ENDIF
> ELSE 48
> END IF
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> Here is what I tried for CASE expression:
>
> SELECT LeadCust.LEADSTATUS,
> LeadCust.Incomplete,
> LeadCust.LASTNAME,
> LeadCust.FIRSTNAME,
> LeadCust.COMPANY,
> LeadCust.COUNTRY,
> LeadCust.PHONE,
> LeadCust.FAX,
> LeadCust.STATE,
> LeadCust.EMAIL,
> Employee.Initials,
> LeadCust.LeadCustUsage,
> Tickler.ContactTime AS ContactDate ,
> '' AS PRODSTATUS ,
> '' AS PRODITEM ,
> '' AS STATUSDATE ,
> '' AS ITEMDATE
> FROM LeadCust,
> Employee,
> Tickler
> WHERE Employee.ID =
> ( CASE
> WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
> WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
> ELSE 48
> END )
>
> AND Tickler.LeadCust = LeadCust.ID
> AND (Tickler.ContactTime Is Null OR Tickler.ContactTime < #12/31/2006
> 11:59:59 PM#)
> AND Tickler.ContactTime = (SELECT MAX(T1.ContactTime) FROM Tickler T1
> WHERE T1.ContactTime IS NOT NULL AND T1.LeadCust = LeadCust.ID)
> AND EXISTS (SELECT '*' FROM ProductRef
> WHERE ProductRef.Customer = LeadCust.ID
> AND ProductRef.Status = 0);
>
> And I got this error message:
>
> "Syntax Error (missing operator) in query expression
>
> 'Employee.ID =
> ( CASE
> WHEN LeadCust.Employee IS NOT NULL then LeadCust.Employee
> WHEN LeadCust.Employee2 IS NOT NULL then LeadCust.Employee2
> ELSE 48
> END )
> AND Tickler.LeadCust = LeadCust.ID
> AND "
>
> "Chris Keating (iAnywhere Solutions)" < Spam_NoThanks_keatin
g@iAnywhere.com>
> wrote in message news:42aed2e5$1@foru
ms-2-dub...
| |
|
| Hello Mark, thank you for the help. I tried your suggestion and still got
the same syntax error.
I'm wondering if this is a problem with ODBC?? If so, how can I find a
problem with ODBC? I tried turning tracing on for the ODBC DSN name, with
no success.
Thanks again !
Paul Grossman
PAULGRO Consulting LLC
"Mark Culp" < reply_to_newsgroups_
only_please_nospam_m
ark.culp@iAnywhere.com>
wrote in message news:42AF0EBC.54CDCDD5@iAnywhere.com...[color=darkred]
> You've nested two if's but only gave one endif... try
>
> Employee.ID =
> IF LeadCust.Employee IS NOT NULL THEN LeadCust.Employee
> ELSE IF LeadCust.Employee2 IS NOT NULL THEN LeadCust.Employee2
> ELSE 48
> ENDIF
> ENDIF
>
> Note that the IF _statement_ supports ELSEIF but the IF _expression_
> does not.
> --
> Mark Culp
> SQLAnywhere Research and Development
> -------------------------------------------------------------------------
> ** Whitepapers, TechDocs, bug fixes are all available through the **
> ** iAnywhere Developer Community at http://www.ianywhere.com/developer **
> -------------------------------------------------------------------------
>
> Paul wrote:
| |
| Bruce Hay 2005-06-14, 8:23 pm |
| The error:
Syntax Error (missing operator) in query expression
is not one that is generated by the SQL Anywhere server; presumably it's
coming from Jet or Access.
Perhaps you could create a view containing the query below (with syntax
corrected for SQL Anywhere) and then refer to the view in your application.
The view could contain the IF expressions, but they would be hidden from
Access.
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42af228a$1@foru
ms-2-dub...
> Hello Mark, thank you for the help. I tried your suggestion and still got
> the same syntax error.
>
> I'm wondering if this is a problem with ODBC?? If so, how can I find a
> problem with ODBC? I tried turning tracing on for the ODBC DSN name, with
> no success.
>
> Thanks again !
> Paul Grossman
> PAULGRO Consulting LLC
>
>
> "Mark Culp"
< reply_to_newsgroups_
only_please_nospam_m
ark.culp@iAnywhere.com>
> wrote in message news:42AF0EBC.54CDCDD5@iAnywhere.com...
>
> -------------------------------------------------------------------------
**[color=darkred]
**[color=darkred]
>
> -------------------------------------------------------------------------
the[color=darkred]
if[color=darkred]
am[color=darkred]
IIf(Nz(LeadCust. Employee)=0,IIf(Nz(L
eadCust. Employee2)=0,48,Lead
Cust.Employe
e2),LeadCust. Employee)[color=dark
red]
#12/31/2006[color=darkred]
T1[color=darkred]
ODBC.[color=darkred]
Error"[color=darkred]
problems![color=dark
red]
conditions[color=dar
kred]
>
>
| |
|
| Hello Bruce, thank you for your help.
I thought about doing that too but there's one thing..
The query below is generated on an "ad-hoc" basis by VBA code and can take
one of many forms. Could VBA code be used to generate a view on the SQL
Anywhere server side?
I've looked at other queries and the IIf() does work when passed via ODBC to
the SQL Anywhere server. In fact, if I were to remove any ONE of the
conditions from the WHERE clause of the original posted SQL statements, I
would NOT get the error. And that includes my leaving in the original IIf()
expression.
It's a very weird problem and I've even tried running it on another computer
thinking maybe it was an environmental problem, but I got the same error on
more than one computer - also I even tried uninstalling and reinstalling
access.
I don't think it's a SQL Server generated error either, but rather it's an
unhandled problem with the SQL Anywhere 9 ODBC driver (this is my very best
guess, I am not sure by any means that this is really the problem).
If you or anyone else reading the NG has any more thoughts please let me
know, I'd greatly appreciate it. This error is a critical one and is
holding up deployment of migration of Access to SQL Anywhere for one of my
clients.
Thank you again for your help.
Paul Grossman
PAULGRO Consulting LLC
"Bruce Hay" < h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m> wrote in message
news:42af33eb@forums
-1-dub...
> The error:
> Syntax Error (missing operator) in query expression
> is not one that is generated by the SQL Anywhere server; presumably it's
> coming from Jet or Access.
>
> Perhaps you could create a view containing the query below (with syntax
> corrected for SQL Anywhere) and then refer to the view in your
> application.
> The view could contain the IF expressions, but they would be hidden from
> Access.
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:42af228a$1@foru
ms-2-dub...
> < reply_to_newsgroups_
only_please_nospam_m
ark.culp@iAnywhere.com>
> **
> **
> the
> if
> am
> IIf(Nz(LeadCust. Employee)=0,IIf(Nz(L
eadCust. Employee2)=0,48,Lead
Cust.Employe
> e2),LeadCust.Employee)
> #12/31/2006
> T1
> ODBC.
> Error"
> problems!
> conditions
>
>
| |
| Erik Anderson 2005-06-14, 8:23 pm |
| You might try an ODBC trace to see what it is that is being sent to ASA, and
whether it is any different than what your program is trying to send...
You could also try playing with sticking the function in {fn ... } escapes
and hope that whatever is interpreting your statement doesn't realize that
IIF is not a standard ODBC function.
Can you put a begin/end around the select and see if "the interpreter" can
treat it as a blackbox batch?
I remember having previous problems with SQL before figuring out that some
of the SQL statements I was sending to ASA had single-quotes in comments
(umm, contractions anybody?) that the ODBC Driver Manager was choking on.
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42af43aa$1@foru
ms-1-dub...
> Hello Bruce, thank you for your help.
>
> I thought about doing that too but there's one thing..
>
> The query below is generated on an "ad-hoc" basis by VBA code and can take
> one of many forms. Could VBA code be used to generate a view on the SQL
> Anywhere server side?
>
> I've looked at other queries and the IIf() does work when passed via ODBC
> to the SQL Anywhere server. In fact, if I were to remove any ONE of the
> conditions from the WHERE clause of the original posted SQL statements, I
> would NOT get the error. And that includes my leaving in the original
> IIf() expression.
>
> It's a very weird problem and I've even tried running it on another
> computer thinking maybe it was an environmental problem, but I got the
> same error on more than one computer - also I even tried uninstalling and
> reinstalling access.
>
> I don't think it's a SQL Server generated error either, but rather it's an
> unhandled problem with the SQL Anywhere 9 ODBC driver (this is my very
> best guess, I am not sure by any means that this is really the problem).
>
> If you or anyone else reading the NG has any more thoughts please let me
> know, I'd greatly appreciate it. This error is a critical one and is
> holding up deployment of migration of Access to SQL Anywhere for one of my
> clients.
>
> Thank you again for your help.
>
> Paul Grossman
> PAULGRO Consulting LLC
>
>
> "Bruce Hay" < h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m> wrote in message
> news:42af33eb@forums
-1-dub...
>
>
| |
| Martin Baur 2005-06-15, 9:23 am |
| > I've looked at other queries and the IIf() does work when passed via ODBC to
> the SQL Anywhere server. In fact, if I were to remove any ONE of the
> conditions from the WHERE clause of the original posted SQL statements, I
> would NOT get the error. And that includes my leaving in the original IIf()
> expression.
Did you approach the entire where clause step by step beginning with one condition first and then adding one more condition per step?
This could help to identify the problematic expression. Once found, you can take care for future ad-hoc-queries done by Access. I always used Access as a frontend to ASA. However, I had to identify
idiosyncrasies first to get the query to work.
If your Access passes through the SQL statement to the server, this would be best. Missing functions like iff() or nz() could be built as customer functions in ASA.
FWIW.
Martin
| |
|
| Hi Martin,
I did use the approach you suggested. I previously approached the entire
where clause step by step as you suggested, and it stopped working after I
added the last condition. However, I also tried running different
combinations of the conditions (i..e. included them one-by-one in order,
then included one-by-one on an alternating basis - swapped out the 3rd one
for the 4th one, etc). and each condition worked by itself or in combination
with the others until all of the conditions were put back.
How can I have Access pass thru the SQL statement to the server? I am
looking into setting up Views and then running them as linked tables.
However, the ODBC driver behaves weird - it first gets a result of all
primary key values and then gets the detailed result sets using a handful of
primary keys at a time, resulting in many many unnecessary trips to the
server - and we're talking up to result sets of over 100k rows, so
performance is also impacted.
I'd like to be able to run the more complex SQL's on the server but without
having to go through the "multi-phased" approach that the ODBC driver uses.
I ran these statments thru I-SQL's Index Consultant (a.k.a "EXPLAIN" in the
DB2 world) and it indicates that the SQL statement is using as many indexes
as possible - it's optimized as best as it can be. So I don't think it
should have to use multiple passes as described above.
I am also wondering if the behavior of the ODBC driver is causing this
unhandled exeption problem.
Thank you for your help and if you (or anyone else) has any more thoughts,
please feel free to share them with me. Your help is always greatly
appreciated.
Paul Grossman
PAULGRO Consulting LLC.
"Martin Baur" <tinu@mindpower.com> wrote in message
news:MPG. 1d1a476e4f85fb779898
09@forums.sybase.com...
>
> Did you approach the entire where clause step by step beginning with one
> condition first and then adding one more condition per step?
>
> This could help to identify the problematic expression. Once found, you
> can take care for future ad-hoc-queries done by Access. I always used
> Access as a frontend to ASA. However, I had to identify
> idiosyncrasies first to get the query to work.
>
> If your Access passes through the SQL statement to the server, this would
> be best. Missing functions like iff() or nz() could be built as customer
> functions in ASA.
>
> FWIW.
>
> Martin
| |
| David Kerber 2005-06-15, 1:23 pm |
| In article <42b06a76$1@forums-1-dub>, paul.grossman@paulgro.com says...
....
> I'd like to be able to run the more complex SQL's on the server but without
> having to go through the "multi-phased" approach that the ODBC driver uses.
> I ran these statments thru I-SQL's Index Consultant (a.k.a "EXPLAIN" in the
> DB2 world) and it indicates that the SQL statement is using as many indexes
> as possible - it's optimized as best as it can be. So I don't think it
> should have to use multiple passes as described above.
>
> I am also wondering if the behavior of the ODBC driver is causing this
> unhandled exeption problem.
>
> Thank you for your help and if you (or anyone else) has any more thoughts,
> please feel free to share them with me. Your help is always greatly
> appreciated.
In Visual Basic, there is an optional parameter for the command which
executes the query, called "SQL passthrough", which tells the ODBC
driver to pass the query directly to the server without passing it
through its own query processor. Does Access (VBA) have such an animal
in its query statement?
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
|
| Hi David,
I tried to do a passthru using the syntax shown in an earlier post in this
thread but was not successful.
I don't think Access (DAO) allows passthru SQL or calls to stored
procedures, or if there is, I haven't found a way to do it yet.
I did notice that you can make stored procedure calls but only within an
Access Project (*.ADP) which are used for converting Access to SQL Server.
The ASA upsizing tool does not create an ADP project so I can't use that
one.
Also, one note, the query is set as the Recordsource for a report within the
code for that report.
Does anyone on the NG know of a way to either pass thru SQL or Call a stored
procedure (perhaps using the OpenRecordset method) from within native Access
/ DAO?
Thank you again,
Paul Grossman
PAULGRO Consulting LLC
"David Kerber" < ns_dkerber@ns_wraenv
iro.com> wrote in message
news:MPG. 1d1a383c9542519c9897
6f@forums.sybase.com...
> In article <42b06a76$1@forums-1-dub>, paul.grossman@paulgro.com says...
>
> ...
>
>
> In Visual Basic, there is an optional parameter for the command which
> executes the query, called "SQL passthrough", which tells the ODBC
> driver to pass the query directly to the server without passing it
> through its own query processor. Does Access (VBA) have such an animal
> in its query statement?
>
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
|
| Hi everyone:
PROBLEM SOLVED:
I stand corrected, I found how you CAN set up pass-through queries.
(when you create a query: New Query, Design View, click on "Query" menu
then select "SQL Specific" then "Pass-Through". After that, build your ODBC
DSN connection string)
The solution was that took my generated SQL, opened a query in code, set the
SQL attribute to the generated SQL, closed the query and then set the
Report's recordsource to the name of the Pass-Through query. I also changed
the code that generated the query so that instead of using IIf() I used the
SQL Anywhere IF _expression_
I can emprically conclude that the query was too complex for the ODBC
driver, and that was causing it to have the "Unhandled Exception" error. By
having the complex query run as a pass-through query (it runs on the server
and not on the client / ODBC) - I got results and improved performance !
Thank you all so much for your help - your responses greatly helped me find
the solution. I hope to repay the favor sometime.
Here's the code I used to set up the Pass-Through query:
Dim db As Database
Dim qd As QueryDef
Dim strSQL as String
Set db = CurrentDb()
Set qd = db.QueryDefs("qPassThruQueryName")
strSQL = GenerateSQLStmt()
qd.SQL = strSQL
qd.Close
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42b07de1@forums
-1-dub...
> Hi David,
>
> I tried to do a passthru using the syntax shown in an earlier post in this
> thread but was not successful.
> I don't think Access (DAO) allows passthru SQL or calls to stored
> procedures, or if there is, I haven't found a way to do it yet.
>
> I did notice that you can make stored procedure calls but only within an
> Access Project (*.ADP) which are used for converting Access to SQL Server.
> The ASA upsizing tool does not create an ADP project so I can't use that
> one.
>
> Also, one note, the query is set as the Recordsource for a report within
> the code for that report.
> Does anyone on the NG know of a way to either pass thru SQL or Call a
> stored procedure (perhaps using the OpenRecordset method) from within
> native Access / DAO?
>
> Thank you again,
> Paul Grossman
> PAULGRO Consulting LLC
>
>
> "David Kerber" < ns_dkerber@ns_wraenv
iro.com> wrote in message
> news:MPG. 1d1a383c9542519c9897
6f@forums.sybase.com...
>
>
| |
| David Kerber 2005-06-16, 7:23 am |
| In article <42b0e4f6$1@forums-2-dub>, paul.grossman@paulgro.com says...
> Hi everyone:
>
> PROBLEM SOLVED:
>
> I stand corrected, I found how you CAN set up pass-through queries.
> (when you create a query: New Query, Design View, click on "Query" menu
> then select "SQL Specific" then "Pass-Through". After that, build your ODBC
> DSN connection string)
I'm glad you got it going! These groups have been incredibly helpful to
me, too.
....
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
|
|
|
|
|