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]

The SELECT expression "IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol" may be the culprit because it causes the predicate "and (symbol=s.fi_sym)" to throw the SQLCODE -680 error. The same problem occurs in Version 10 as Version 11, so it's not new behavior.

It may be difficult to convert the Transact SQL right outer join =* to an ANSI RIGHT OUTER JOIN because of the IfNull... I tried and failed, possibly because I have no idea what the IfNull is trying to accomplish.

I am guessing that the code sometimes works and sometimes fails for you because sometimes the failing subquery is optimized out of existence when your large SELECT is executed, depending on the data.

Here is a bit of test code...

CREATE TABLE pf_daily_holdg_detl ( fi_sym INTEGER, subAcct_ID INTEGER );
CREATE TABLE fi_security ( fi_sym INTEGER );
INSERT pf_daily_holdg_detl VALUES ( 1, 1 );
INSERT pf_daily_holdg_detl VALUES ( 2, 1 );
INSERT fi_security VALUES ( 1 );
INSERT fi_security VALUES ( 2 );
COMMIT;
SET TEMPORARY OPTION TSQL_OUTER_JOINS = 'ON';
SELECT @@VERSION;

-------------------------------
-- Fails in V10...

@@VERSION
'10.0.1.3579'

SELECT IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, *
  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.subAcct_ID=b.subAcct_ID);

Invalid expression in WHERE clause of Transact-SQL outer join
SQLCODE=-680

-------------------------------
-- Works in V10...

SELECT IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, *
  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.subAcct_ID=b.subAcct_ID);

symbol,fi_sym,subAcct_ID,fi_sym,subAcct_ID,fi_sym
1,1,1,1,1,1
1,1,1,1,1,2
2,2,1,2,1,1
2,2,1,2,1,2

-------------------------------
-- Fails in V11...

@@VERSION
'11.0.1.2960'

SELECT IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, *
  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.subAcct_ID=b.subAcct_ID);

Invalid expression in WHERE clause of Transact-SQL outer join
SQLCODE=-680

-------------------------------
-- Works in V11...

SELECT IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, *
  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.subAcct_ID=b.subAcct_ID);

symbol,fi_sym,subAcct_ID,fi_sym,subAcct_ID,fi_sym
1,1,1,1,1,1
1,1,1,1,1,2
2,2,1,2,1,1
2,2,1,2,1,2

[Former Member]

thanks to trying. the if null statement is selecting a non null value if possible.

what is happening is that we try to compare entries in 2 dates. for a given fi_sym may be added in one day (brought) or removed (sold) in another day. buying and selling may be partial not always all or nothing and daily holding may contain one, zero or many( typically up to 70, more often only 30 to 50 for most accounts) so the if null is trying to compute the right non missing value at the same time market value changes over time. the requirement is also to list the one the reach 85 % of target price or subtantial change from one day to another

hmmm, wonder if I have to use cursor or temp tables to resolve the problem or the problem be broken into several select with union

actually this stored proc is relatively simple compared to another that try to look for between date range to find any change in qty. I picked this trying to resolve the outer join first.

[VolkerBarth]

I would think (or rather guess) that's a particular "out-of-order" problem to define an aliased expression in the SELECT list (here "symbol") and to use that in the WHERE clause as a join condition, simply as the join would need to be done before any other filtering...

In general, SQL Anywhere allows expressions in the SELECT list to be used in other clauses of the SELECT statements (say, in the WHERE or GROUP BY or ORDER BY clauses), as documented here as "Alias references", and that's a big advantage imn my experience, but I would guess this does not hold for what is truly a join condition stated in the WHERE clause...