WAY WAY Back I was trying to run a query that actually required the use of a correlated subquery and I was having problems. So, I broke it down...

I think I got it now - thanks.

I was trying to do something like this:

SELECT

A.id_Account,

(SELECT SUM(H.Amount)

FROM cdiHolding H

INNER JOIN cdAccount A

ON H.id_Account = A.id_Account)

FROM cdAccount A

Or like this:

SELECT

A.id_Account,

(SELECT SUM(H.Amount)

FROM cdiHolding...

Heh-

I need a know-it-all cause I still don't understand.

Using your DDL to create the tables:

Since This(Query A):

Select H.Amount

FROM cdiHolding H, cdAccount A

WHERE H.id_Account = A.id_Account

{returns 1, 1000, 10, 100}

Is EXACTLY...

NPeeters - thanks for the reply. (I hate the howevers - but...)

Your statement:

"You shouldn't perform the join in the SELECT clause of the outer query."

confuses me as I have to...

Thanks John for the reply.

However, the key point is that not only does it take longer to run - but it returns incorrect results. Please read on...

In standalone queries,...

rmarda-

Sorry - did not mean to offend you at all - I do appreciate your help.

I'm simply frustrated w/ T-SQL and would like other people to try this simple test...

Thanks rmarda - but still no go.

You state "since the ELSE shouldn't get considered." - that is still the problem.

If you notice in my last post - the problem arises...

To clarify even further:

This works:

CASE @TotalAmt

WHEN 0 THEN 9999

ELSE (CAST(ISNULL(ValueAmt, 0) AS DECIMAL) / @TotalAmt) * 100

END AS APercent

I Get 9999

- HOWEVER -

If I have this and @TotalAmt =...

Thanks but no cigar.

@TotalAmt is in fact a decimal(38,3)

So - the WHEN 0 part DOES fire !

Replace:

CASE @TotalAmt

WHEN 0 THEN 0

ELSE 'X'

END AS APercent

- WITH -

CASE @TotalAmt

WHEN 0 THEN...

OK - I got it!!!!!!!!!!!

The problem IS the CASE statement itself. It's return value is based on the HIGHEST ORDER datatype of the return value(s). So - varchars...

Thanks Antares - but no go.

The SP will run:

1) ONLY if I pass column 5 as the sort

2) It runs - but the sort is not incorrect since it's cast...

Here is the error message I receive:

Server: Msg 8114, Level 16, State 5, Procedure XTester, Line 13

Error converting data type varchar to numeric.

Here is the Stored Proc.

Notes:

- Comment out this...