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 18.104.22.16845
But it seems . With clause or join with subquery is not working in Sybase. Any other suggestion?
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 22.214.171.1246:
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 '126.96.36.1996',2,'bbb' '188.8.131.526',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 ;-)
answered 14 Feb '13, 04:01