error query

Question

error query

ayreshp

SSC Enthusiast

Points: 114
More actions
November 30, 2007 at 3:08 am

#62317

Folks
I have a query with error 🙁
ssql = "SELECT b.pn_bnc, b.model_bnc, Count(b.pn_bnc) AS Bounce, (SELECT COUNT(Bounce.laudo_new) FROM Bounce WHERE (Bounce.laudo_new = 'Sem defeito') AND (Bounce.bnc90d = b.bnc90d)) AS NTF FROM Bounce AS b WHERE(((b.bnc90d) = 'VERDADEIRO')) GROUP BY b.pn_bnc, b.model_bnc, b.bnc90d"
when I run the query, for "NTF" return the sum but isn't group by pn_bnc
what can be?
Tks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

It would actually group the rows by columns b.pn_bnc, b.model_bnc and b.bnc90d. And also you have a sub-query executing for each row, will not scale well in production.

Here is another way do this...

SELECTb.pn_bnc, b.model_bnc, b.bnc90d, COUNT( b.pn_bnc ) AS Bounce,

COALESCE( t.Total, 0 ) AS AS NTF

FROMBounce b

LEFT JOIN

(

SELECTbnc90d, COUNT( Bounce.laudo_new ) AS Total

FROMBounce

WHERElaudo_new = 'Sem defeito'

GROUP BY bnc90d

) t on b.bnc90d = t.bnc90d

WHEREb.bnc90d = 'VERDADEIRO'

GROUP BY b.pn_bnc, b.model_bnc, b.bnc90d, t.Total

--Ramesh

ayreshp SSC Enthusiast Points: 114 More actions · Answer 2

Tks for answer

but I don't how why ... when run the query faile!

I'm using BD access, theses comand suport th access?

this query I'm runing in the VB.net hehe

this is error IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

🙁

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

The solution provided is for SQL Server 2000 or higher and wouldn't run in MS Access or any other RDBMS.

--Ramesh

ayreshp SSC Enthusiast Points: 114 More actions · Answer 4

Thanks for answer again!

I will wait to news answer hehe

Tks!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Or, you could try posting in the "Access" forum 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)