Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    41

    Question Unanswered: Outer Join syntax does not work after upgrade to 12.5

    The following SQL works perfectly in ASE 11.9.2
    We recently upgraded to ASE 12.5.0.3 ESD#3
    Now the SQL fails with
    12:29:29.125 DBMS CAIDEV -- Error: Number (11055) Severity (16) State (2) Server (caisyb01d) Query contains an illegal outer-join request.

    Has anybody come across such issues? And if so, how did you resolve this?

    TIA

    declare @expense char(7)
    declare @posn_type char(1)
    declare @amount_type char(2)
    declare @begin int
    declare @end int
    declare @fundName_short char(10)
    select @expense = "AccrExp"
    select @posn_type = "Z"
    select @amount_type = "PY"
    select @begin=20030913
    select @end=20031019
    select @fundName_short="AAAENERGY"

    select t1.navDate, t1.dataKey,
    t4.columnLongName,
    t1.columnName,
    t2.indicator_value,
    case when patindex(@expense, t1.columnName) = 1
    then (t1.columnValue - (select isnull(sum(amount), 0.0)
    from fpadb..fpts_ledger_cash
    where business_date =* convert(smalldatetime, convert(varchar, t1.navDate), 112)
    and t1.dataKey *= branch_cd + acct_base
    and posn_type = @posn_type
    and amount_type = @amount_type
    group by business_date, branch_cd + acct_base)
    )
    else t1.columnValue
    end
    from NAVInformation t1, FundExpenseIndicators t2,
    AccountFundAssociation t3, ColumnNameValues t4
    where t1.navDate between @begin and @end
    and t1.columnName = t2.indicator_name
    and t2.fundName_short = @fundName_short
    and t2.fundName_short = t3.fundName_short
    and t1.dataKey = t3.accountNum
    and t1.columnName = t4.columnName
    order by t1.navDate, t1.dataKey
    ====================
    Rajesh

  2. #2
    Join Date
    Sep 2003
    Posts
    17
    Hi,

    Have a look at the Sybooks for Version 12.5, Book "Transact-SQL User Guide", Chapter5: Subqueries: Using Queries within other queries. There is this new behaviour mentioned and explained in detail.

    Hope it helps.

    Regards,
    Ulrike

  3. #3
    Join Date
    Oct 2003
    Posts
    41

    How to check all the stored procedures after upgrade

    This is one of the procedure that failed. Is there a command that can be used to check for all the procedures in a database (and could be automated using shell), withouth testing each procedure after upgrade?

    TIA
    ====================
    Rajesh

  4. #4
    Join Date
    Sep 2003
    Posts
    17
    According to the manuals you could use the trace flag 298 and obtain with version 12.5 the same behaviour as with previous versions.
    I would suggest to use -T298 in the RUN_server file.

    Regards,
    Ulrike

  5. #5
    Join Date
    Jun 2003
    Posts
    140
    there a numerous changes in joins compared to 11.9 ansd 12.x kindly follow the sybase user manuals

  6. #6
    Join Date
    Oct 2003
    Posts
    41
    We are planning to use the Trace flag 298 for now in prod. But still, I want to find all the stored procedures, that could error out. sp_recompile on a table, only compiles the procedure/trigger, the next time it is called. But I do not want to go through each and every procedure/trigger manually.

    How do I recompile procedures/triggers without executing them?
    TIA
    ====================
    Rajesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •