Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Comparing values between two dates using left outer join

    Hi could somebody help out what a beginner are missing, I like to compare fx rates for 2 days. For that I try to use a left outer join. The difference between the two select statements included is only rhe "FXRATES.PRICE_DATE". trying to execute the statement I get error "FROM keyword not found where expected".
    I could not understand what I have made incorrect in the syntax.

    Thanks,
    Robert

    SELECT T.CURCROSSES.CURCRO,T.A_FXRATES.FX_RATE_DB_FORMAT as FXRate 1,Y.A_FXRATES.FX_RATE_DB_FORMAT as FXRate 2,(T.A_FXRATES.FX_RATE_DB_FORMAT-Y.A_FXRATES.FX_RATE_DB_FORMAT) as RateChange
    FROM

    (SELECT CURCROSSES.CURCRO,A_FXRATES.FX_RATE_DB_FORMAT

    FROM SCDAT.CURCROSSES,SCDAT.A_FXRATES
    WHERE SCDAT.CURCROSSES.CURCROIK =SCDAT.A_FXRATES.CUR_CROSS_REF
    AND SCDAT.A_FXRATES.PRICE_TYPE = ( 'Bid')
    AND SCDAT.A_FXRATES.PRICE_DATE = ( '02-jan-2012')
    GROUP BY CURCROSSES.CURCRO,A_FXRATES.FX_RATE_DB_FORMAT) as T

    LEFT OUTER JOIN

    (SELECT CURCROSSES.CURCRO,A_FXRATES.FX_RATE_DB_FORMAT

    FROM SCDAT.CURCROSSES,SCDAT.A_FXRATES
    WHERE SCDAT.CURCROSSES.CURCROIK =SCDAT.A_FXRATES.CUR_CROSS_REF
    AND SCDAT.A_FXRATES.PRICE_TYPE = ( 'Bid')
    AND SCDAT.A_FXRATES.PRICE_DATE = ( '30-dec-2011')
    GROUP BY CURCROSSES.CURCRO,A_FXRATES.FX_RATE_DB_FORMAT) as Y
    ON
    T.CURCROSSES.CURCRO = Y.CURCROSSES.CURCRO

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    maybe you will benefit from reading this thread: http://www.dbforums.com/oracle/16689...rly-ended.html
    Additionally, ANSI joins were introduced in Oracle 9iR1. What is your Oracle version?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle does not like or expect space characters for/within column alias
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2012
    Posts
    2
    The Oracle version I use is 10g. //Robert

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    Oracle does not like or expect space characters for/within column alias
    Unless you use double quotes around the alias


    Quote Originally Posted by ARER01
    "FROM keyword not found where expected"
    A table alias cannot use the AS keyword. You need something like:
    Code:
    ... FROM (SELECT CURCROSSES.CURCRO...) T ...
    Note the missing AS after the closing bracket.

    Another thing: the condition PRICE_DATE = ('02-jan-2012') is bound to fail if your SQL runs in an environment that uses a different locale (i.e. date format).
    You should change that to: PRICE_DATE = to_date('02-01-2012', 'dd-mm-yyyy'). This will work correctly regardless of the environment.

    And please use [code] tags in the future to make your SQL readable

    For details see the help link at the bottom of the page:
    http://www.dbforums.com/misc.php?do=bbcode

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Althogh I'm not so familiar with Oracle,
    I thought the query might be written on DB2 like in this way ...

    Example 1:
    Code:
    SELECT cc.CURCRO
         , MAX(FXRate_1)                 as FXRate_1
         , MAX(FXRate_2)                 as FXRate_2
         , MAX(FXRate_1) - MAX(FXRate_2) as RateChange
     FROM  SCDAT.CURCROSSES  cc
     INNER JOIN
           SCDAT.A_FXRATES   af
       ON  cc.CURCROIK   =  af.CUR_CROSS_REF 
       AND af.PRICE_TYPE =  'Bid' 
       AND af.PRICE_DATE IN ('02-jan-2012' , '30-dec-2011')
     CROSS JOIN LATERAL
           (VALUES ( CASE af.PRICE_DATE
                     WHEN '02-jan-2012' THEN
                          af.FX_RATE_DB_FORMAT
                     END
                   , CASE af.PRICE_DATE
                     WHEN '30-dec-2011' THEN
                          af.FX_RATE_DB_FORMAT
                     END
                   )
           ) f(FXRate_1 , FXRate_2)
     GROUP BY
           cc.CURCRO
    ;

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    That won't work in Oracle. Oracle does not know the LATERAL keyword, nor does it support the VALUES row constructor (including the alias definition in the form f(FXRate_1, ...)

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    VALUES can be replaced by
    SELECT ... FROM dual [UNION ALL SELECT ... FROM dual [...] ]

    LATERAL keyword is used to be able to reference columns of previously joined tables(or inline views) in the following inline view.
    So I thought there is similar capability in Oracle(or Oracle allowes that without special syntax).

    "the alias definition in the form f(FXRate_1, ...)" can be replaced by
    (SELECT ... AS FXRate_1 , ... FROM dual) f

    It would be easy to meet the Example 1 in syntax of Oracle.
    Last edited by tonkuma; 01-04-12 at 16:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •