System resource exceeded error when run an Union Query (1 Viewer)

[dim]

Hi,

I have a very basic union query.
I use it to merge the rows from others 10 queries.
When I run the union query is working fine, if I use it for max 7 queries.
If I add more than 7, I get the error: “System resource exceeded.”
Here is the code of my union query:

SELECT v_Revision_Agreements.ID, v_Revision_Agreements.Tag
FROM v_Revision_Agreements;
UNION ALL
SELECT v_Revision_Arbec.ID, v_Revision_Arbec.Tag
FROM v_Revision_Arbec;
UNION ALL
SELECT v_Revision_Boisaco.ID, v_Revision_Boisaco.Tag
FROM v_Revision_Boisaco;
UNION ALL
SELECT v_Revision_BTB.ID, v_Revision_BTB.Tag
FROM v_Revision_BTB;
UNION ALL
SELECT v_Revision_BTBSpec.ID, v_Revision_BTBSpec.Tag
FROM v_Revision_BTBSpec;
UNION ALL
SELECT v_Revision_Hardwood.ID, v_Revision_Hardwood.Tag
FROM v_Revision_Hardwood;
UNION ALL SELECT v_Revision_INV.ID, v_Revision_INV.Tag
FROM v_Revision_INV;
UNION ALL
SELECT v_Revision_Moisan.ID, v_Revision_Moisan.Tag
FROM v_Revision_Moisan;

So in the above code if I remove anyone of my 8 queries sources, the union query will run correctly.
Can you please help me to find a solution?

Thanks

 

[vbaInet]

What is the full error message?
And how many joins does each one of them have?

In addition to the problem you're experiencing, it seems like you also have a normalisation problem.

 

[dim]

Thank You for reply,

The message is only System resource exceeded and nothing else.
Each one of those 8 queries was created with others complex queries.
Can you please explain more detailed concerning the normalisation problem, and if is the case, what I have to do to fix it?

Thank You.

 

[vbaInet]

The normalisation problem is only a hunch, I don't know for sure without seeing all your tables so let's forget about that for now.

Let me see the SQL statements of two of your most complex and longest queries.

 

[dim]

Ok, I'll show first the Agreement query:

SELECT IIf([PMP]=0,[SPMPO],[PMP]) AS PMP_New, v_Revision_Agreements_Old.*, "Agreement" AS Tag
FROM v_Revision_Agreements_Old LEFT JOIN v_Revision_Agreements_PMP ON (v_Revision_Agreements_Old.Commande = v_Revision_Agreements_PMP.Commande) AND (v_Revision_Agreements_Old.NoFacture = v_Revision_Agreements_PMP.NoFacture);

---------------------------------------------------------------
and than the old Agreement:
----------------------------------------------------------------------

SELECT Round(v_AllRevisionBoscus_Old.margebrute*0.3,2) AS commission, v_AllRevisionBoscus_Old.*, IIf(IsNumeric([entrepot])=False Or novendeur="240",30,IIf(CInt([entrepot])>=0 And CInt([entrepot])<=379,25,28)) AS commPourc
FROM v_AllRevisionBoscus_Old
WHERE (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.EscompteVenteRev)<=-10 Or (v_AllRevisionBoscus_Old.EscompteVenteRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.VenteNetteRev)<=-10 Or (v_AllRevisionBoscus_Old.VenteNetteRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.CoutBrutRev)<=-10 Or (v_AllRevisionBoscus_Old.CoutBrutRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.EscompteCoutRev)<=-10 Or (v_AllRevisionBoscus_Old.EscompteCoutRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.ChargeAchatRev)<=-10 Or (v_AllRevisionBoscus_Old.ChargeAchatRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.ChargeVenteRev)<=-10 Or (v_AllRevisionBoscus_Old.ChargeVenteRev)>=10)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.CoutNetRev)<=-10 Or (v_AllRevisionBoscus_Old.CoutNetRev)>=10)) OR (((IsNull([agreement]))=False) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.EscompteVenteRev)<=-10 Or (v_AllRevisionBoscus_Old.EscompteVenteRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.VenteNetteRev)<=-10 Or (v_AllRevisionBoscus_Old.VenteNetteRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.CoutBrutRev)<=-10 Or (v_AllRevisionBoscus_Old.CoutBrutRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.EscompteCoutRev)<=-10 Or (v_AllRevisionBoscus_Old.EscompteCoutRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.ChargeAchatRev)<=-10 Or (v_AllRevisionBoscus_Old.ChargeAchatRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.ChargeVenteRev)<=-10 Or (v_AllRevisionBoscus_Old.ChargeVenteRev)>=10) AND ((IsNull([exception]))<>False)) OR (((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.CoutNetRev)<=-10 Or (v_AllRevisionBoscus_Old.CoutNetRev)>=10) AND ((IsNull([exception]))<>False)) OR (((v_AllRevisionBoscus_Old.exception) Not In ("Mexique","Future")) AND ((IsNull([agreement]))=False) AND ((v_AllRevisionBoscus_Old.MBRev)<=-10 Or (v_AllRevisionBoscus_Old.MBRev)>=10)) OR (((IsNull([agreement]))=False) AND ((IsNull([exception]))<>False) AND ((v_AllRevisionBoscus_Old.MBRev)<=-10 Or (v_AllRevisionBoscus_Old.MBRev)>=10));

-----------------------------------------------------------------
Sure if is the case I can continue until I'll arrive to the Main Table....

Thank You

 

[vbaInet]

That's a pretty hefty query.

Here are some comments:

1. I can see a lot of repetition which could have been written better.
2. You're using functions like IsNull() to filter out Nulls when you should be using Is Null
3. You need to rewrite your queries to make them less complex.

I can't look at your entire query because there's a lot going on there but here's a way to rewrite some parts.
Old:

OR (
   	(
	(v_AllRevisionBoscus_Old.exception) 
		NOT IN 	("Mexique","Future")
	)
		AND 	((IsNull([agreement])) = False)
		AND 	((v_AllRevisionBoscus_Old.VenteNetteRev) <= - 10
		OR 	(v_AllRevisionBoscus_Old.VenteNetteRev) >= 10
	)
    )
OR 	((
	(v_AllRevisionBoscus_Old.exception) 
		NOT IN 	("Mexique","Future"))
		AND 	((IsNull([agreement])) = False)
		AND 	((v_AllRevisionBoscus_Old.CoutBrutRev) <= - 10
		OR 	(v_AllRevisionBoscus_Old.CoutBrutRev) >= 10
	)
   )

New:

OR	(
	[exception] 
	NOT IN 		("Mexique","Future")
	AND 		[agreement] Is Not Null
	AND		(
				(    VenteNetteRev <= - 10
		   		OR   VenteNetteRev >= 10
		    		)
			OR
		  	 	(    CoutBrutRev <= - 10
		    		OR   CoutBrutRev) >= 10
				)
			)
  	)

 

[dim]

Thank You, I'll try it.

 

[vbaInet]

I don't think that's enough to solve your problem. I think you need to re-think the structure of the other queries.

 

[dim]

For the moment I combined all rows into one new table using some appends queries.
I'll use like this for now.
Thanks

 

[vbaInet]

Oki dok!