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

[Former Member]

thank you all. your comments and suggestion are highly appreciated and helpful.

here is the complete code with original before 20010514 chg at the end as comment ( apparently the sql was still functioning after 2013-06-21 enhancementbefore the latest upgrade to release 2960) BTW: I found out that the original code also supported query without sub-account in rare occasion. Most accounts do have sub-accounts the latest complete code in the attachment link text

The symbol table was used to link in the columns expiration date(exp_dt), target_price a.<whatever> is for items on @valuation_dt as base of comparision, while b.<whatever> is on @valuation_dt_dif