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

asked 25 Aug '13, 01:22

gg99's gravatar image

gg99
193192129
accept rate: 0%

edited 26 Aug '13, 08:59

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266

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

(26 Aug '13, 02:40) Volker Barth
Replies hidden

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

Semantics and compatibility of Transact-SQL outer joins

(26 Aug '13, 02:51) Volker Barth

TSQL outer joins may be ambiguous sometimes, hence the message. Solution: rewrite them using ANSI outer join syntax.

permanent link

answered 26 Aug '13, 04:14

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

edited 27 Aug '13, 04:47

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."

permanent link

answered 26 Aug '13, 11:33

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

Comment Text Removed

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>
(27 Aug '13, 02:06) Volker Barth

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

(27 Aug '13, 02:09) gg99
Replies hidden

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.

(27 Aug '13, 09:45) 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
permanent link

answered 27 Aug '13, 17:47

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

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.

(28 Aug '13, 01:08) gg99

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

(29 Aug '13, 05:01) Volker Barth

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

permanent link

answered 27 Aug '13, 16:33

gg99's gravatar image

gg99
193192129
accept rate: 0%

edited 27 Aug '13, 16:46

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×2
×2

question asked: 25 Aug '13, 01:22

question was seen: 3,756 times

last updated: 29 Aug '13, 05:02