Solved

Sybase 12.5.1 vs 12.5.3

Posted on 2009-07-02
5
774 Views
Last Modified: 2012-06-27
In Sybase version 12.5.1 the select statement as attached throws error but in Sybase 12.5.3 version it runs fine.
I further checked and saw that when I comment below line :-

AND exists(select 1 from zzzzzz c where td.something_cd=c.something and td.something2 between c.something4StartRange and c.something4EndRange)    

and run the query in 12.5.1 it runs fine. I think it is version issue .can you please confirm this and with some detail ( any sybase bug link) then it will be really great. a quick answer is highly appreciated.
Issue.txt
0
Comment
Question by:KAVINASH1313
5 Comments
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 24761112
Hi,

In you sub query

you are misisng the table alias for the td. You are using td.something_cd (but you didnt define the table name)

select 1 from zzzzzz c where td.something_cd=c.something and td.something2 between c.something4StartRange and c.something4EndRange
0
 
LVL 19

Accepted Solution

by:
grant300 earned 300 total points
ID: 24764266
12,5,1 had some flakey behavior around the derived table syntax.  For example, a derived table with a GROUP BY ("td" in your case) will not work in an SELECT statement that uses the UNION operator.  You seem to have uncovered yet another bug with derived tables with GROUP BY operators.  Unfortunately, you are stuck.  The solution is to patch up to 12.5.3 or, even better, 12.5.4

You really want to be on 12.5.3 or 12.5.4 if you can get there.  This is just one example of why, not to mention that you are flying naked with no support at this point.  If you are doing monetary processing, you really should be on a supported version of your critical software products.

Regards,
Bill
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24769013
(I can't download the attachmt ... but the alias refers to a table in the outer query, which by definition is not in the subquery.)

Both 12.5.1 and 12.5.3 are quite ancient, six and seven years out-of-date, respectively.  Is there some reason you are unable to use the current version 12.5.4 of 20 Apr 2009 ?

IIRC, yesm, there were changes specifically to handling subqueries and derived tables between 12.5.1 vs 12.5.2 vs 12.5.3 vs 12.5.4, when the issue finally settled down.  Good code was fine, but sub-standard code and code that took advantage of undocumented operation (which was corrected in some later version or demand-changed due to ANSI compliance issue) did break.  And if it wasn't corrected properly in 12.5.2, then it broke again in 12.5.3.  (The same thing is happening with 15.0 and that certainly has not settled down yet; I am not recommending it.)  The nasty bit was, there were no errors, just a changed result set.  

IIRC It was documented in the New Features Guide and the Cover Letter, but I no longer have such ancient versions handy.

I can still find a few ancient CRs (Solved Cases) that relate to the problem, but this is for supported customers and partners only (if you had a support contract, I am sure you could get it yourself).  But I did not find anything in the public domain.  Maybe you can spend some time googling.
0
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 200 total points
ID: 24769391
Trying to "confirm with some detail" is turning out to be harder than I thought, since you do not have access to Sybase Support (where it would be easy, and you can get the info yourself).  I cannot give you the info from same without breaking contractual agreements with Sybase (we are Sybase BSA Partners).  Note the lock symbol.

Here is the first page only, showing the search criteria for solved cases that I used, "12.5.1" through "12.5.4" excluding "11" and "15", including "select" (and "subquery" in the second attachment) ... there are 377 solved cases to go through.  That's solved cases, not reported problems.  Even the first few items are telling, and indicative.

Anyone who stays with a version older than 12.5.4 EDS #8, while knowing this information, needs to have some documented reasons for doing so.  But trying to work out problems re the operation of select or subqueries being changed between 12.5..1/12.5.2/12.5.3/12.5.4, is now a documented (from Sybase), complete and total waste of time.

12-5-Select-CR.png
12-5-Subquery-CR.png
0
 

Author Closing Comment

by:KAVINASH1313
ID: 31599088
I agree with you guys.....that I am stuck the prod is the older one...can't change :-(....however, I did a workaround with temp tables....avoided the derived table and managed to get it working. Thanks for your information though...I really appreciate.
0

Featured Post

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Google always has something new and amazing up its sleeve, and the most current thing that they have been working on is another step in the evolution of Google Search, from machine learning to its brilliant successor, deep learning.
Adding Computers to AD groups through an SCCM Task Sequence
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question