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;

[Breck_Carter]

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'