?
Solved

"Expression too complex" error with a query

Posted on 2004-08-20
26
Medium Priority
?
1,548 Views
Last Modified: 2008-02-26
Hi;

I have this query that has many, many fields but doesn't really do anything out of the ordinary.  Normally it works fine but if I switch on the Group By thing, when I try to access it i get an error that says the expression is too complex to be evaluated.

Please help!
thanks
0
Comment
Question by:lghaman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
  • +3
26 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 11856032
Most SQL queries can only go 256 characters.  Anything beyond that you may get 'Query too complex' errors.  

I can't think of too many examples where you'd need a query with more than five or six 'Group By' fields before using a Sum, Count, etc.

Try getting rid of 'Groyp by' fields that are not necessary to calculate your Sum, Count, etc.

Hope this helps.
-Jim
_________
Jim Horn
Small Business Software Consultant
http://www.jimhorn.biz 
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11856228
What you may want to consider is creating a new query which uses your current query as its source.  In the new one, try your grouping.

It's an idea.

Walt
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11856235
Are you doing any calculating in it?
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 36

Expert Comment

by:SidFishes
ID: 11856402
Most SQL queries can only go 256 characters...huh?...i've got sql with thousands of characters...256 FIELDS perhaps you meant?


anyways...most query too complex errors are as a result of a null value trying to be evaluated...check your data for null
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11856412
err... unhandled nulls
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11856415
is anyone going to post some sql..???
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11856535
SELECT BEER FROM FRIDGE WHERE Label = "has my name on it"

0
 
LVL 1

Author Comment

by:lghaman
ID: 11857021
sorry, this may sound dumb, but what exactly constitutes a null value?  like i know that it means it contains nothing but what about if i have a field that doesn't have anything in it?  like a text field that doesn't have anything in it?  i copied and pasted a bunch of tables out of an excel spreadsheet in to a big database i just finished developing.
0
 
LVL 1

Author Comment

by:lghaman
ID: 11857023
i'm not calculating but i'm using some First functions
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11857156
a null has a "value" which doesn't exist  ...which is different than an empty string

var = null is different than var = "" or var = 0

if you are using any kind of calculation (including first i believe), you must handle nulls

if there is a possibilty of a null, you should always handle it...

iif(isnull(field1), "0", field1) for a numeric field or iif isnull(field1) "", Field1)  for a text field



0
 
LVL 1

Author Comment

by:lghaman
ID: 11857206
i tried this and i'm still getting "Invalid Use of Null" errors...i can't figure out what's doing it...is there a way to just go through the entire table and handle all the nulls before the form opens?  that would probably be a rather cumbersome thing to do...
0
 
LVL 9

Accepted Solution

by:
solution46 earned 1000 total points
ID: 11858098
To view all Nulls...

SELECT * FROM [table]
WHERE [RelevantField] IS NULL

Note IS NULL rather than = Null or similar. Null implies there is no known value, so it cannot be equal to anything. It's a bit like NaN (Not a Number) and similar used in other languages.

You'll have to do this for all fields, but it will give you al rows that have a null in them.

An alternative to the IIf(IsNull([field]),value,[field]) approach is the nz([field],default_value) function. Has exactly the same result but is a bit less complicated, e.g. Expr1: nz([numeric_field],0,[numeric_field]) or Expr1: nz([text_field],"",[text_field])

s46.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11858100
Arrgg....

I mean,
e.g. Expr1: nz([numeric_field],0) or Expr1: nz([text_field],"")

Ooops,

s46.
0
 
LVL 1

Author Comment

by:lghaman
ID: 11870762
i removed all the fields from the query that used the First function and i'm still getting "Invalid Use of Null" errors...the only other function i'm using is group by
0
 
LVL 1

Author Comment

by:lghaman
ID: 11870789
perhaps i'm not understanding what the iif(isnull(field1), "0", field1) function thingie exactly does...could someone pleaze explain what happens if the field is/isn't null in something like:

iPTH2: First(IIf(IsNull([iPTH]),"0",[iPTH]))


thanks
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11870797
Right and it is having problems grouping on the nulls.  Use the above advice and place the nz function in fields that contain nulls.  

Walt
0
 
LVL 1

Author Comment

by:lghaman
ID: 11870873
what does nz do?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11870950
NZ(field1, 0)
says "wherever you see a null in field1, make it a zero instead"

Walt
0
 
LVL 1

Author Comment

by:lghaman
ID: 11871504
ok, i got the source of the problem...

Age2: NZ([Age],0)

is a field in the query i'm having issues with.

[Age] is a calculated field: Age: (CInt(DateDiff("d",[Date of Birth],[OP Date 1]))/365

[Date of Birth],[OP Date 1] are two Date/Time fields from a table and there is the possibility that they would be null, but even if i set them to be adjusted if they are null, it would still cause an error when [Age] is calculated, is there any way i can fix this?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11872224
I thought you weren't calculating!
Could you post the SQL code as it stands?
We're going to have to handle the nulls in a different way but I'd like to see the context of the Age field.

Thanks
Walt

0
 
LVL 1

Author Comment

by:lghaman
ID: 11872395
i wasn't calculating in the query, but i'm using calculated fields from a different query...i wasn't thinking about that

[Age] is used in PXSearchEngineQueryResults but the actual calculation is done in Patient Information Main



Patient Information Main:
SELECT [Main PX Data].StudyNum, [Main PX Data].[First Name], [Main PX Data].[Last Name], [Main PX Data].[Jackson #], [Main PX Data].[Sylvester #], [Main PX Data].Surgeon, [Main PX Data].[Address 1], [Main PX Data].[Address 2], [Main PX Data].[Primary Phone], [Main PX Data].[Other Phone], [Main PX Data].Sex, [Main PX Data].[Date of Birth], [Main PX Data].[Referring Doctor], [Main PX Data].[Ref Doctor Phone], [Main PX Data].[Calcium Trial], [History and Condition].[Bone Pain], [History and Condition].[Stones/History], ......About 30 other fields...... [Follow Up General].[PostOp Date], [Follow Up General].[PostOp Cal], [Follow Up General].[PostOp iPTH], [Follow Up General].[PostOp UCal], [Main PX Data].Staff, CInt(DateDiff("d",[Date of Birth],[OP Date 1]))/365 AS Age, [Follow Up General].Communications
FROM (([History and Condition] INNER JOIN [Main PX Data] ON [History and Condition].StudyNum = [Main PX Data].StudyNum) INNER JOIN [Follow Up General] ON [Main PX Data].StudyNum = [Follow Up General].StudyNum) INNER JOIN [Operative Details] ON [Main PX Data].StudyNum = [Operative Details].StudyNum;




PXSearchEngineQueryResults:
SELECT NZ([StudyNum],0) AS StudyNum2, NZ([First Name],"") AS [First Name2], NZ([Last Name],"") AS [Last Name2], NZ([Surgeon],"") AS Surgeon2, NZ([Sex],"") AS Sex2, NZ([OP Date 1],"") AS [OP Date 12], PXSearchEngineQuery.[OP Date 1 Virgin], PXSearchEngineQuery.[OP Date 1 ICMA], PXSearchEngineQuery.[OP Date 1 IRMA], PXSearchEngineQuery.[OP Date 1 Fail], NZ([OP Date 2],"") AS [OP Date 22], ......About 30 other fields......PXSearchEngineQuery.[PX Previous OP], NZ([PX Previous OP Detail],"") AS [PX Previous OP Detail2], PXSearchEngineQuery.Recurrence, PXSearchEngineQuery.[Lost to FU], PXSearchEngineQuery.Expired, NZ([Expired Year],0) AS [Expired Year2], NZ([Discharge],"") AS Discharge2, NZ([Months F/U with iPTH],0) AS [Months F/U with iPTH2], PXSearchEngineQuery.[iPTH Variation], PXSearchEngineQuery.[Calcium Variation], PXSearchEngineQuery.[With iPTH], First(IIf(IsNull([Total Cal]),"0",[Total Cal])) AS [Total Cal2], First(IIf(IsNull([iPTH]),"0",[iPTH])) AS iPTH2, First(IIf(IsNull([U Cal]),"0",[U Cal])) AS [U Cal2], First(IIf(IsNull([Creatinine]),"0",[Creatinine])) AS Creatinine2, First(IIf(IsNull([PreInc 1]),"0",[PreInc 1])) AS PreInc, First(IIf(IsNull([5Min]),"0",[5Min])) AS 5Min2, First(IIf(IsNull([10Min]),"0",[10Min])) AS 10Min2, First(IIf(IsNull([20Min]),"0",[20Min])) AS 20Min2, First(IIf(IsNull([PreExcDrop]),"0",[PreExcDrop])) AS PreExcDrop2, First(IIf(IsNull([5Drop]),"0",[5Drop])) AS 5Drop2, First(IIf(IsNull([10Drop]),"0",[10Drop])) AS 10Drop2, First(IIf(IsNull([20Drop]),"0",[20Drop])) AS 20Drop2, First(IIf(IsNull([PreExc]),"0",[PreExc])) AS PreExc2, NZ([PreOp Date],"") AS [PreOp Date2], NZ([PreOp Cal],0) AS [PreOp Cal2], NZ([PreOp iPTH],0) AS [PreOp iPTH2], NZ([PostOp Date],"") AS [PostOp Date2], NZ([PostOp Cal],0) AS [PostOp Cal2], NZ([PostOp iPTH],0) AS [PostOp iPTH2], NZ([Age],0) AS Age2
FROM PXSearchEngineQuery
GROUP BY NZ([StudyNum],0), NZ([First Name],""), NZ([Last Name],""), NZ([Surgeon],""), NZ([Sex],""), NZ([OP Date 1],""), PXSearchEngineQuery.[OP Date 1 Virgin], PXSearchEngineQuery.[OP Date 1 ICMA], PXSearchEngineQuery.[OP Date 1 IRMA], PXSearchEngineQuery.[OP Date 1 Fail], NZ([OP Date 2],""), PXSearchEngineQuery.[OP Date 2 Virgin], PXSearchEngineQuery.[OP Date 2 ICMA], PXSearchEngineQuery.[OP Date 2 IRMA], PXSearchEngineQuery.[OP Date 2 Fail], NZ([OP Date 3],""), ......About 30 other fields......PXSearchEngineQuery.SPHPT, PXSearchEngineQuery.Secondary, PXSearchEngineQuery.Tertiary, PXSearchEngineQuery.MRF, PXSearchEngineQuery.[PX Cancer], PXSearchEngineQuery.Familial, PXSearchEngineQuery.Lithium, PXSearchEngineQuery.[Vit D Resistant], PXSearchEngineQuery.[MEN 1], PXSearchEngineQuery.[MEN 2], PXSearchEngineQuery.[Initial OP], PXSearchEngineQuery.[TX Previous OP], PXSearchEngineQuery.[PX Previous OP], NZ([PX Previous OP Detail],""), PXSearchEngineQuery.Recurrence, PXSearchEngineQuery.[Lost to FU], PXSearchEngineQuery.Expired, NZ([Expired Year],0), NZ([Discharge],""), NZ([Months F/U with iPTH],0), PXSearchEngineQuery.[iPTH Variation], PXSearchEngineQuery.[Calcium Variation], PXSearchEngineQuery.[With iPTH], NZ([PreOp Date],""), NZ([PreOp Cal],0), NZ([PreOp iPTH],0), NZ([PostOp Date],""), NZ([PostOp Cal],0), NZ([PostOp iPTH],0), NZ([Age],0);
0
 
LVL 9

Expert Comment

by:solution46
ID: 11873078
You can guarantee your Age field wont be a problem like...

Age: (CInt(DateDiff("d",nz([Date of Birth],0),nz([OP Date 1],0)))/365

Also, have you checked that each subquery is returning what you would expect it to? I would try running each of those first and checking you aren't getting any Nulls or other values you don't want. After all, if these aren't working, the main query has no chance.

s46
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11873830
solution46:
There's a slight problem with that.  If one is null and one isn't, you'll end up reporting a very large age since you'll be subtracting a date near today - 0.
Rather
try something like :
age: IIf([Date of Birth] Is Null Or [op date 1] Is Null,-1,DateDiff("yyyy",[date1],[date2]))

Note: I've converted the calculation to years.

Walt
0
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 1000 total points
ID: 11873846
So if either one is null, it shows an age of -1.  That way you can easily ID which ones do not have valid ages.

Sorry, I forgot to adjust the field names:
age: IIf([Date of Birth] Is Null Or [op date 1] Is Null,-1,DateDiff("yyyy",[Date of Birth],[op date 1]))

Walt
0
 
LVL 1

Author Comment

by:lghaman
ID: 11874086
thanks so much!  u guys are great
0
 
LVL 9

Expert Comment

by:solution46
ID: 11874540
you're welcome.

cheers for the points!

s46
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question