Invalid express in where clause of transact-sql outerjoin SQLCODE=-680, ODBC 3 state='41000' Line 1

[VolkerBarth]

The following used to work as part of a stored procedure before upgrading to the latest version of SA 11:

begin
    Declare @valuation_dt date;
    Declare @acct_grp_id integer;
    Declare @valuation_dt_dif date;
    Declare @subAcct_ID integer;
    Declare @fi_securitySybmOrShortDescCriteria varchar(80);
    set Option TSQL_OUTER_JOINS= on;
    if (@fi_securitySybmOrShortDescCriteria is null or len(@fi_securitySybmOrShortDescCriteria)=0) then
       select '2013-08-24 22:19:32', 1, '2013-08-24 22:19:32', null, null
         into @valuation_dt, @acct_grp_id, @valuation_dt_dif, @subAcct_ID, @fi_securitySybmOrShortDescCriteria;
        select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol,
               IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg,
               IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot,
               IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val,
               IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val,
               a.lst_qot as qota,b.lst_qot as qotb,
               if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then
                  (a.lst_qot-b.lst_qot)/b.lst_qot
               else 0.0
               endif as mkt_variance,
               if s.target_price<>0 then
                  last_qot/s.target_price
               else 0.0
               endif as target_pc,
               IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID,
               IfNull(a.qty,b.qty,a.qty) as lst_qty,
               s.Exp_dt,s.target_price
          from
               pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s
         where
               (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym)
           and (a.valuation_dt=@valuation_dt)
           and (a.acct_grp_id=@acct_grp_id)
           and (a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)
           and (b.valuation_dt=@valuation_dt_dif)
           and (b.acct_grp_id=@acct_grp_id)
           and (qty_chg<>0 or abs(mkt_variance)>=.1
            or abs(target_pc)>.85
            or datediff(day,current date,s.Exp_dt)<10) ;
    endif;
    set Option TSQL_OUTER_JOINS=off;
end

[VolkerBarth]

To clarify: Did the according STP work on an older v11 build (which one?) or on a pre-v11 version? Which version do you use now? (AFAIK, 11.0.1.2960 is the latest available build on Windows).

I'm not really familiar with T-SQL outer joins but possibly the fact that there are two coniditions to join a and b - one as a right join, one as an inner join - constitutes the problem:

• "a.fi_sym=*b.fi_sym"
• "a.subAcct_ID=b.subAcct_ID"

The docs do claim:

A null-supplying table cannot participate in both a Transact-SQL outer join and a regular join or two outer joins.[...]

AFAIK, the T-SQL outer join syntax is generally said to be problematic/unspecified in several situations (Glenn Paulley had written a great whitepaper on that once), so possibly a newer engine will do better checks here to avoid misinterpretations when an older one simply "joined somehow"...

Note: That's just my wild guess, I'd recommend to use the (much more comprehensible) ANSI join syntax...

[VolkerBarth]

FWIW, the whitepaper is still available (note: It dates back to SQL Anywhere 8 days...):

Semantics and compatibility of Transact-SQL outer joins

[Breck_Carter]

This is a guess; try changing

and (a.subAcct_ID=b.subAcct_ID)

to this

and (a.subAcct_ID=*b.subAcct_ID)

because SQLCODE -680 is described thusly: "An expression in the WHERE clause of a query that uses Transact-SQL syntax contains a comparison of a column from the NULL-supplying table with a subquery or an expression that references a column from another table."

[VolkerBarth]

Note: There's no simple "OUTER JOIN", you have to decide between "LEFT OUTER JOIN" and "RIGHT OUTER JOIN", and the T-SQL "=*" is a right outer join, AFAIK.

Try something like (I don't know how table alias "s" is bound to column "symbol" - say it would reference "b.symbol"):

...
FROM (pf_daily_holdg_detl as a RIGHT OUTER JOIN
      pf_daily_holdg_detl as b
        ON a.fi_sym = b.fi_sym AND a.subAcct_ID = b.subAcct_ID)
     INNER JOIN fi_security as s ON b.symbol = s.fi_sym
WHERE <the other conditions>

[Former Member]

the account_id, subAcct_ID must match, the objective is to list missing or added fi_sym, as well as derivation in qty or significant derivation in value between two dates in a given sub-account for an account. the sql listed in the post is actually a small part of the whole union I tried explicit left join but I'm afraid my skill is lacking. I got rejected replacing from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) with from pf_daily_holdg_detl as a outer join pf_daily_holdg_detl as b on(a.fi_sym = b.fi_sym), fi_security as s where The error message is: Could not execute... Syntax near 'join' on line 31 SQLCODE=-131, ODBC 3 state='42000; Line 1, column 1

[Breck_Carter]

If you want help fixing a problem, please show us ALL of the code EXACTLY like it was when the error occurred. Do not edit the code, and do not try to describe the code in English instead of posting the actual SQL.

Let me repeat: ALL of the code, EXACTLY like it was tested. Anything else is a waste of time, including your time.