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;

asked 14 Feb '13, 00:22

SyNB's gravatar image

SyNB
31114
accept rate: 0%

edited 19 Feb '13, 10:57

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


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'
permanent link

answered 14 Feb '13, 10:12

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 14 Feb '13, 10:17

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 ;-)

permanent link

answered 14 Feb '13, 04:01

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×17

question asked: 14 Feb '13, 00:22

question was seen: 3,550 times

last updated: 19 Feb '13, 10:57