SQL Statement Mystery

[Sam G]

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 ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

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
--

"Sam G" <mo**@spamhole.com> wrote in message
news:3f*********************@news.frii.net...

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 ***
Don't just participate in USENET...get rewarded for it!