Comparing values between two dates using left outer join

ARER01 · #1 (**permalink**) 01-03-12, 10:55

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

flyboy · #2 (**permalink**) 01-03-12, 11:32

Hi,
maybe you will benefit from reading this thread: ORA-00933:SQL command not properly ended
Additionally, ANSI joins were introduced in Oracle 9iR1. What is your Oracle version?

anacedent · #3 (**permalink**) 01-03-12, 14:05

Oracle does not like or expect space characters for/within column alias

ARER01 · #4 (**permalink**) 01-04-12, 04:49

The Oracle version I use is 10g. //Robert

shammat · #5 (**permalink**) 01-04-12, 05:30

Quote:

Originally Posted by anacedent

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

tonkuma · #6 (**permalink**) 01-04-12, 11:30

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
;

shammat · #7 (**permalink**) 01-04-12, 13:06

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, ...)

tonkuma · #8 (**permalink**) 01-04-12, 15:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech