Access Error Message - Help

~J~ · 14 Oct 2006 at 16:49

Hit a problem today which I can't seem to work out or find an answer anywhere.

In a nutshell, a customer of mine has a program that can use either an Access or SQL database.

For the past 2 years, they've used Access, and today have switched over to SQL.

All has gone good, until they've received the following error message:

The minimum aggregate operation cannot take a bit data type as an argument.

I've look at both databases and the error is connected with a field called "IPPTAG". In Access, the datatype of this field is "Yes/No" in SQL it's a BIT.

Now I know they are technically the same thing, but the following SQL command is throwing a wobbler when run.

In Access, it runs perfect, in SQL it crashes with the error, removing the MIN(d.IPPTag) reference causes it to work.

Any ideas if there's a way to get it working?

Query is:

SELECT d.BasicPartNo, d.LatestPartNo, d.PartColour, MAX(d.Description) AS FirstDesc, MIN(d.IPPTag) AS MinIppTag, SUM(W1QtyRequired) AS Required FROM DeliverySchedule d WHERE d.SupplierID = 1 AND d.WarehouseID = 1 AND d.OrderType = 1 AND d.PlanCode = 'U4AF01' AND d.ShipToCode = 'SDC1' AND d.ProdPeriod = 42 AND d.DateTimeDelivery = {ts'2006-10-16 11:00:00'} AND d.Creator = 1 AND d.W1QtyRequired IS NOT NULL GROUP BY d.BasicPartNo, d.LatestPartNo, d.PartColour ORDER BY d.BasicPartNo, d.LatestPartNo, d.PartColour

toString · 14 Oct 2006 at 18:09

Well the error makes sense, why would you need to use the min function on a bit? It's either one value or another.

What is the program using this result for? Maybe you can leave the field to return as a bit, and just remove the min() function around it? And then programatically decide what to do if the returned field is true of false.

~J~ · 14 Oct 2006 at 18:56

Well the program is a bespoke piece of kit wrote be a rather large car company in Swindon, so I can't change the code as it's hard-coded into the program.

I agree, the error makes total sense, but didn't know if there was some advanced SQL server action that could convert a 1 to a Y or a 0 to a N. Likewise didn't know if there was a datatype (what I've no idea!!) in SQL that would act 'similar' to the Yes/No state in Access which I believe is a tetinary state and not binary like SQL.

toString · 14 Oct 2006 at 20:00

Ahh I see.
Hmm, can you convert the field to be int? false-->0 true-->1. That way it would work...

Otherwise you could try using case statements, or some kind of cast (at a guess).

CAST(d.IPPTag AS int(2))
or maybe a char casting, I dunno.

~J~ · 14 Oct 2006 at 20:21

Well, just converted the field type from BIT to INT and no errors reporting (can't do the CASE statement as the query is hardcoded into the program!).

No errors is a start, will have to wait now to see if the results the program returns is the same as the query.

Thanks for that, was starting to get worried!!

toString · 14 Oct 2006 at 20:32

No probs. Shame about the hard coding. I guess you don't have the source

Still can't quite work out why they were using min(). I'm not sql expert, but it doesn't make any sense to me!