Solved

Sybase 12.5.1 vs 12.5.3

Posted on 2009-07-02
5
755 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
Comment Utility
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
Comment Utility
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
Comment Utility
(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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
HOW TO: Install and Configure VMware vSphere Hypervisor 6.5 (ESXi 6.5), Step by Step Tutorial with screenshots. From Download, Checking Media, to Completed Installation.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now