on 2013 Aug 25 2:22 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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...
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."
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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>
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
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.
TSQL outer joins may be ambiguous sometimes, hence the message. Solution: rewrite them using ANSI outer join syntax.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.