Solved

"Expression too complex" error with a query

Posted on 2004-08-20
26
1,533 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
  • 9
  • 7
  • 4
  • +3
26 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
Are you doing any calculating in it?
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
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
Comment Utility
err... unhandled nulls
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
is anyone going to post some sql..???
0
 
LVL 36

Expert Comment

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

0
 
LVL 1

Author Comment

by:lghaman
Comment Utility
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
Comment Utility
i'm not calculating but i'm using some First functions
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
Arrgg....

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

Ooops,

s46.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:lghaman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
what does nz do?
0
 
LVL 17

Expert Comment

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

Walt
0
 
LVL 1

Author Comment

by:lghaman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
thanks so much!  u guys are great
0
 
LVL 9

Expert Comment

by:solution46
Comment Utility
you're welcome.

cheers for the points!

s46
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now