sql server (alternate): SQL Statement Mystery -- sql server (alternate)

SQL Statement Mystery

Sam G
10/30/2003 12:11:05 PM

sql server (alternate):

Hi all,

I have a statement that reads as follows:
select [Sales Ledger] - gl as difference
from
(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1
join
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06
+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2

My first nested statement however I keep getting the message:
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'Q2'.

I just cannot fathom why this is so?

Any suggestions would be most helpful.
Moby

*** Sent via Developersdex http://www.developersdex.com ***

Re: SQL Statement Mystery

David Portas
10/30/2003 1:11:31 PM

The syntax error was caused by the fact that you had missed the ON clause.
An inner join must have an ON clause.

In fact you don't need a join at all:

SELECT
(SELECT SUM(RPAAP)
FROM F03B11)/100.0
-
(SELECT SUM(GBAPYC +
GBAN01 + GBAN02 + GBAN03 + GBAN04 +
GBAN05 + GBAN06 + GBAN07 + GBAN08 +
GBAN09 + GBAN10 + GBAN11 + GBAN12)
FROM F0902
WHERE GBAID = '00667809' AND GBFY = 3)/100.0

Note the change to the division sums. Your original query would have caused
an integer division which loses precision in the result.

--
David Portas
------------
Please reply only to the newsgroup
--

[quoted text, click to view]