"Expression too complex" error with a query

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 

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

Are you doing any calculating in it?

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

err... unhandled nulls

is anyone going to post some sql..???

SELECT BEER FROM FRIDGE WHERE Label = "has my name on it"

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.

i'm not calculating but i'm using some First functions

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

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...

Arrgg....

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

Ooops,

s46.

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

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

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

what does nz do?

NZ(field1, 0)
says "wherever you see a null in field1, make it a zero instead"

Walt

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?

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

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);

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

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

thanks so much!  u guys are great

you're welcome.

cheers for the points!

s46