SQL Statement Mystery

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Sam G
    #1

    SQL Statement Mystery

    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!
    Tags: None
    • David Portas
      #2
      Re: SQL Statement Mystery

      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" <moby@spamhole. com> wrote in message
      news:3fa0ffd9$0 $198$75868355@n ews.frii.net...[color=blue]
      > 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![/color]


        Comment

        Previous template Next
        Working...