Repeated subquery in sql

[Former Member]

Hi All, I am new to Sybase. But I have quiet good experience in Oracle SQL. Now I need to write some query in Sybase which has repeated inner queries. My Sybase version : @@version 7.0.4.3345

But it seems . With clause or join with subquery is not working in Sybase. Any other suggestion?

My Query:

with zero_sales_tax_ids as ( SELECT Id zero_sales_tax_ids FROM tax_table WHERE Sales_Tax_Percent =0 )   
SELECT Customer_Num, Name
  From Customer C
 Inner Join Zero_Sales_Tax_Ids Zero
 Where C.Delivered_Tax_Id =Zero.Zero_Sales_Tax_Ids
    OR c.DEFAULT_TAX_ID =zero.zero_sales_tax_ids;

[MCMartin]

Upgrade to Sybase 9.0.2 and the "with" statement is available, or even upgrade to latest version 12.0.1 for even more fun 😉

[Former Member]

That is not possible as project already Live. Any other option to prevent writing and parsing query twice and improve the performance in version 7.0.4.3345

[VolkerBarth]

As Martin has explained, common table expressions were introduced in v9.0.2 (released around 2005, methinks), and you are using a much older version. Besides "flattening" the CTE into a normal table expression, the other alternative would be to use a normal view.

However, joining with a derived query (i.e. "FROM ... INNER JOIN (select ...) dt" should be possible with v7, too.