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; |
SQL Anywhere 7 was released in the year 2000, which is two years before Oracle added the WITH clause to version 9.2.0... so comparisons with Oracle are unfair. In your case, there is no advantage to using the WITH clause instead of a derived query since "zero_sales_tax_ids" is only used once. Derived queries work in SQL Anywhere 7. When using any version of SQL Anywhere, your WHERE clause had to be changed to an ON clause, or the Inner Join had to be changed to an ordinary "," comma join, to avoid the following errors: There is more than one way to join 'C' to 'Zero' SQLCODE=-147, ODBC 3 State="42000" There is no way to join 'C' to 'Zero' SQLCODE=-146, ODBC 3 State="42000" The first message appears when the Customer tax id columns are both foreign keys to tax_table. The second message appears when there are no relationships between tax_table and Customer, and thus no way for SQL Anywhere to find a join path for the Inner Join. Here is a working example using SQL Anywhere 7.0.3.2046: CREATE TABLE tax_table ( Id INTEGER NOT NULL PRIMARY KEY, Sales_Tax_Percent DECIMAL ( 11, 2 ) NOT NULL ); CREATE TABLE Customer ( Customer_Num INTEGER NOT NULL PRIMARY KEY, Delivered_Tax_Id INTEGER NOT NULL REFERENCES tax_table ( Id ), DEFAULT_TAX_ID INTEGER NOT NULL REFERENCES tax_table ( Id ), Name VARCHAR ( 100 ) NOT NULL ); INSERT tax_table VALUES ( 1, 10.0 ); INSERT tax_table VALUES ( 2, 0.0 ); INSERT tax_table VALUES ( 3, 15.5 ); INSERT Customer VALUES ( 1, 3, 1, 'aaa' ); INSERT Customer VALUES ( 2, 2, 1, 'bbb' ); INSERT Customer VALUES ( 3, 1, 1, 'ccc' ); INSERT Customer VALUES ( 4, 1, 1, 'ddd' ); INSERT Customer VALUES ( 5, 3, 1, 'eee' ); INSERT Customer VALUES ( 6, 3, 2, 'fff' ); SELECT @@VERSION, Customer_Num, Name From Customer C Inner Join ( SELECT Id zero_sales_tax_ids FROM tax_table WHERE Sales_Tax_Percent = 0 ) AS Zero ON ( C.Delivered_Tax_Id = Zero.Zero_Sales_Tax_Ids OR c.DEFAULT_TAX_ID = zero.zero_sales_tax_ids ); @@VERSION,Customer_Num,Name '7.0.3.2046',2,'bbb' '7.0.3.2046',6,'fff' |
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 ;-) 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
(14 Feb '13, 04:09)
SyNB
Replies hidden
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.
(14 Feb '13, 04:21)
Volker Barth
|