Transact-SQL vs ANSI SQL problem

Hi all,
I have transact sql statement:
select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
productfamilydesc,
max(isnull(vp.productid,0)) as reported
from productfamily pf,product p,visitproduct vp
where pf.productfamilyid=p.productfamilyid and p.productid*=vp.productid
and vp.customerid=81310471  and vp.marketcompanyid=1
and vp.visitdate='2004-10-11'
group by pf.productfamilyid,productfamilydesc
order by ltrim(rtrim(pf.productfamilydesc))

which I believe may be rewritten in ANSI SQL as:

select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
productfamilydesc,
max(isnull(vp.productid,0)) as reported
from productfamily pf,product p left outer join visitproduct vp on
p.productid=vp.productid
where pf.productfamilyid=p.productfamilyid
and vp.customerid=81310471  and vp.marketcompanyid=1
and vp.visitdate='2004-10-11'
group by pf.productfamilyid,productfamilydesc
order by ltrim(rtrim(pf.productfamilydesc))


But it gives me different result (looks like left outer join became inner
join)

What is my mistake?
Any help will be appreciated


0
Boris
10/13/2004 7:39:51 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
589 Views

Similar Articles

[PageSpeed] 59

Predicates in the WHERE clause tend to change OUTER joins into INNER
joins... it's a characteristic error of SQL (an error that's easy to
make because of the nature of the language).

Try moving the vp.* predicates from the WHERE to the ON clause, or
even better, use them in a derived table SELECT on the right side of
the LEFT OUTER JOIN:

select pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
       productfamilydesc,
       max(isnull(vp.productid,0)) as reported
  from productfamily pf
       INNER JOIN product p 
       left outer join 
             ( SELECT * 
                 FROM visitproduct vp 
                WHERE vp.customerid=81310471  
                  and vp.marketcompanyid=1
                  and vp.visitdate='2004-10-11' ) AS vp
          on p.productid=vp.productid
 where pf.productfamilyid=p.productfamilyid
 group by pf.productfamilyid,productfamilydesc
 order by ltrim(rtrim(pf.productfamilydesc))

Breck

On 13 Oct 2004 12:39:51 -0700, "Boris" <boris@srl.co.il> wrote:

>Hi all,
>I have transact sql statement:
>select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>productfamilydesc,
>max(isnull(vp.productid,0)) as reported
>from productfamily pf,product p,visitproduct vp
>where pf.productfamilyid=p.productfamilyid and p.productid*=vp.productid
>and vp.customerid=81310471  and vp.marketcompanyid=1
>and vp.visitdate='2004-10-11'
>group by pf.productfamilyid,productfamilydesc
>order by ltrim(rtrim(pf.productfamilydesc))
>
>which I believe may be rewritten in ANSI SQL as:
>
>select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>productfamilydesc,
>max(isnull(vp.productid,0)) as reported
>from productfamily pf,product p left outer join visitproduct vp on
>p.productid=vp.productid
>where pf.productfamilyid=p.productfamilyid
>and vp.customerid=81310471  and vp.marketcompanyid=1
>and vp.visitdate='2004-10-11'
>group by pf.productfamilyid,productfamilydesc
>order by ltrim(rtrim(pf.productfamilydesc))
>
>
>But it gives me different result (looks like left outer join became inner
>join)
>
>What is my mistake?
>Any help will be appreciated
>

--
SQL Anywhere Studio 9 Developer's Guide 
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20 
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
0
Breck
10/13/2004 8:52:26 PM
Thanks, Breck

Your version of selct statement really works

(I allso add some another syntax clearance because I use this statement in
ULTRALITE.NET but it is another story ..)

Thanks again,

Boris

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:pv4rm011575j4e0jpqejha3imf32p3g388@4ax.com...
> Predicates in the WHERE clause tend to change OUTER joins into INNER
> joins... it's a characteristic error of SQL (an error that's easy to
> make because of the nature of the language).
>
> Try moving the vp.* predicates from the WHERE to the ON clause, or
> even better, use them in a derived table SELECT on the right side of
> the LEFT OUTER JOIN:
>
> select pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>        productfamilydesc,
>        max(isnull(vp.productid,0)) as reported
>   from productfamily pf
>        INNER JOIN product p
>        left outer join
>              ( SELECT *
>                  FROM visitproduct vp
>                 WHERE vp.customerid=81310471
>                   and vp.marketcompanyid=1
>                   and vp.visitdate='2004-10-11' ) AS vp
>           on p.productid=vp.productid
>  where pf.productfamilyid=p.productfamilyid
>  group by pf.productfamilyid,productfamilydesc
>  order by ltrim(rtrim(pf.productfamilydesc))
>
> Breck
>
> On 13 Oct 2004 12:39:51 -0700, "Boris" <boris@srl.co.il> wrote:
>
> >Hi all,
> >I have transact sql statement:
> >select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
> >productfamilydesc,
> >max(isnull(vp.productid,0)) as reported
> >from productfamily pf,product p,visitproduct vp
> >where pf.productfamilyid=p.productfamilyid and p.productid*=vp.productid
> >and vp.customerid=81310471  and vp.marketcompanyid=1
> >and vp.visitdate='2004-10-11'
> >group by pf.productfamilyid,productfamilydesc
> >order by ltrim(rtrim(pf.productfamilydesc))
> >
> >which I believe may be rewritten in ANSI SQL as:
> >
> >select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
> >productfamilydesc,
> >max(isnull(vp.productid,0)) as reported
> >from productfamily pf,product p left outer join visitproduct vp on
> >p.productid=vp.productid
> >where pf.productfamilyid=p.productfamilyid
> >and vp.customerid=81310471  and vp.marketcompanyid=1
> >and vp.visitdate='2004-10-11'
> >group by pf.productfamilyid,productfamilydesc
> >order by ltrim(rtrim(pf.productfamilydesc))
> >
> >
> >But it gives me different result (looks like left outer join became inner
> >join)
> >
> >What is my mistake?
> >Any help will be appreciated
> >
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


0
Boris
10/14/2004 3:30:40 PM
I am behind-the-times on UltraLite functionality, alas...can you have
a CREATE VIEW? How about a WITH clause in front of the SELECT, for a
local temporary view?

If you post a new question in sybase.public.sqlanywhere.ultralite, you
will get more help.

Breck

On 14 Oct 2004 08:30:40 -0700, "Boris" <boris@srl.co.il> wrote:

>Thanks, Breck
>
>Your version of selct statement really works
>
>(I allso add some another syntax clearance because I use this statement in
>ULTRALITE.NET but it is another story ..)
>
>Thanks again,
>
>Boris
>
>"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
>message news:pv4rm011575j4e0jpqejha3imf32p3g388@4ax.com...
>> Predicates in the WHERE clause tend to change OUTER joins into INNER
>> joins... it's a characteristic error of SQL (an error that's easy to
>> make because of the nature of the language).
>>
>> Try moving the vp.* predicates from the WHERE to the ON clause, or
>> even better, use them in a derived table SELECT on the right side of
>> the LEFT OUTER JOIN:
>>
>> select pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>>        productfamilydesc,
>>        max(isnull(vp.productid,0)) as reported
>>   from productfamily pf
>>        INNER JOIN product p
>>        left outer join
>>              ( SELECT *
>>                  FROM visitproduct vp
>>                 WHERE vp.customerid=81310471
>>                   and vp.marketcompanyid=1
>>                   and vp.visitdate='2004-10-11' ) AS vp
>>           on p.productid=vp.productid
>>  where pf.productfamilyid=p.productfamilyid
>>  group by pf.productfamilyid,productfamilydesc
>>  order by ltrim(rtrim(pf.productfamilydesc))
>>
>> Breck
>>
>> On 13 Oct 2004 12:39:51 -0700, "Boris" <boris@srl.co.il> wrote:
>>
>> >Hi all,
>> >I have transact sql statement:
>> >select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>> >productfamilydesc,
>> >max(isnull(vp.productid,0)) as reported
>> >from productfamily pf,product p,visitproduct vp
>> >where pf.productfamilyid=p.productfamilyid and p.productid*=vp.productid
>> >and vp.customerid=81310471  and vp.marketcompanyid=1
>> >and vp.visitdate='2004-10-11'
>> >group by pf.productfamilyid,productfamilydesc
>> >order by ltrim(rtrim(pf.productfamilydesc))
>> >
>> >which I believe may be rewritten in ANSI SQL as:
>> >
>> >select  pf.productfamilyid,ltrim(rtrim(pf.productfamilydesc)) as
>> >productfamilydesc,
>> >max(isnull(vp.productid,0)) as reported
>> >from productfamily pf,product p left outer join visitproduct vp on
>> >p.productid=vp.productid
>> >where pf.productfamilyid=p.productfamilyid
>> >and vp.customerid=81310471  and vp.marketcompanyid=1
>> >and vp.visitdate='2004-10-11'
>> >group by pf.productfamilyid,productfamilydesc
>> >order by ltrim(rtrim(pf.productfamilydesc))
>> >
>> >
>> >But it gives me different result (looks like left outer join became inner
>> >join)
>> >
>> >What is my mistake?
>> >Any help will be appreciated
>> >
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
>http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>

--
SQL Anywhere Studio 9 Developer's Guide 
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20 
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
0
Breck
10/14/2004 4:28:21 PM
Reply:

Similar Artilces:

Transact-SQL vs. Watcom-SQL?
This falls under the "I know I'm supposed to know this but I don't" category: what is Transact-SQL? Is my understanding correct that Transact-SQL is the SQL dialect used by Microsoft's SQL Server, while Watcom-SQL is the dialect used by Sybase's SQLAnywhere? That's correct.. Sybase ASE and MS SQLServer came from the same codebase, many moons ago. The common dialect for both products is Transact-SQL, although they've diverged somewhat over the years. Watcom-SQL is the SQLAnywhere/ASA dialect. Paul Horan Rick Charnes <rick.charnes@state.ma.us> wrote in message news:MPG.1288e6b793f0e825989681@forums.sybase.com... > This falls under the "I know I'm supposed to know this but I don't" > category: what is Transact-SQL? Is my understanding correct that > Transact-SQL is the SQL dialect used by Microsoft's SQL Server, while > Watcom-SQL is the dialect used by Sybase's SQLAnywhere? ...

Watcom-Sql vs. Transact-SQL
Hi Everyone, I'm in the process of writing some SP and.... 1) Which dialect should I concentrate on? 2) I can not figure out how to do functions in Transact-SQL Thank you for your help! "Jan K. van Dalen" <vandalen@csi.com> wrote: >Hi Everyone, > >I'm in the process of writing some SP and.... >1) Which dialect should I concentrate on? Unless you want to port to ASE, why do you care? Just write whatever works; personally, I mix the dialects :) If you want to port to ASE soon then stick to ASE. However, in the future ASE will suppor...

Help! Problems migrating transact-sql code to SQL Anywhere 5.5
Hi everyone, We are migrating an application developed to Sybase SQL Server 11.0 to SQL Anywhere 5.5 and, for now, the following SQL Anywhere limitations are killing us: 1. It does not accept rollback in triggers. 2. Raiseerror interrupts triggers execution. 3. Temporary table created in a stored procedure can not return the result rows to clients Does Anyone know how to bypass this SQL Anywhere Transact-SQL limitations?: Does anyone know if ASA SQL Anwwhere 6.0 resolves this problems? Thanks in advance, Eva Correia, Technology Director. >>3. Temporary ta...

Watcom-SQL or Transact-SQL
In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures and triggers can be "viewed" as either Watcom or T-SQL. If I understand correctly, T-SQL is more transportable, as it is used by Sybase SQl Server, and also to some degree by MS SQL. Is Watcom SQL in place simply for backward compatibility, or are there other advantages? >In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures >and triggers can be "viewed" as either Watcom or T-SQL. If I >understand correctly, T-SQL is more transportable, as it is used by >Sybase S...

linq-to-sql VS sql
 Is linq-to-sql as fast as sql in processing data? Or how percentage is linq-to-sql slower than sql? What is disadvantages of linq-to-sql compared with sql?Thanks for reading and replying my answer. From everything I have read about linq and comparing it to ado they relatively the same until you get into large recordsets.   http://beingmarkcohen.com/?p=168Please remember to mark the replies as answers if they help and unmark them if they provide no help.Chuck Catron There is a 5-10% hit on large record sets but its worth it for the type safe querys Steve Seeking the elegant s...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

Problem inserting sql query into database float datatype field using SQL Transaction
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is  (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?   Try Dim i As Integer ...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

SQL 7 vs SQL 2000
Can anyone offer a justification for recommending an upgrade from SQL 7 to SQL2k? What does one gain from the upgrade? Built-in XML support. Indexed views. Performance tweaks. These are just a few improvements that I'm aware of. For more detail, I'm sure you can visit the SQL Server site. --Ambrose--Ambrose LittleInfragistics, Inc. Hi hlaford, 1. Use MS VS .NET Documentation (.NET Framework documentation) 2. On Contents tab, From Filterd by: choose Entreprise Servers 3. Choose Microsoft SQL Server 2000 4. Click on What's New 5. You get all the 'Enhancements' ... T...

Dealing with datetime and SQL Transact-SQL
I am trying to make a stored procedure in SQLServer Express.The question is related to this stored procedure / transact - sql.  I think i am doing something wrong with datetime.Here is the stored procedure.The error i am getting is that:Msg 241, Level 16, State 1, Line 20Syntax error converting datetime from character string.  ===================================== DECLARE    @websiteID  intDECLARE    @dateFrom  datetimeDECLARE    @dateTo  datetimeDECLARE    @sortbystring  varchar (20)set @websiteID...

SQL Server Problem.(SQL-28000)
------=_NextPart_000_0033_01C12642.735DD1F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hello, I'm a newbie here and have a question regarding the use of DBI::ODBC with SQL Server. I've checked the mail archives and seen others with the same problem but have yet to come across the solution. Here's what I'm doing: (note: actual user name and password have been changed here). use DBI; my %attr = ( PrintError => 1, RaiseError => 1 ); $dbh = DBI->connect("dbi:ODBC:LocalServer", &...

SQL 2005 vs SQL 2000
HI, I Have my websites developed in sql 2005 and my host is providing SQL 2000. WHAT problem i might face will my site run ? or do i need to change things??     If you use the regular ado.net and sql server functionality , you should be good however if you use some of the advanced features in 2005 like SQLCachedependency , then they may not work in sql 2000 out of the box. You may have to do some setup. Other potential problems could be use of the new XML datatype etc.Difficult to give you an exact list. I would suggest looking at the differences between sql 2000 and 2005 an...

sql-92 vs t-sql
Hello I use sql5.0.03 with transact-sql, now I like to migrate to ASA 7 but it required SQL-92. Now I have to convert all my views to SQL-92. Do you know any software that would convert T-SQL syntax into SQL-92 syntax? thanks ASA 7.0 should understand your TSQL queries, with a couple of exceptions. Are there any particular problems you are having? -- Jason Hinsperger International and Sustaining Engineering Adaptive Server Anywhere Tuan Dinh wrote: > Hello > I use sql5.0.03 with transact-sql, now I like to migrate to ASA 7 but it > required SQL-92. >...

SQL Exception Handling and SQL Transactions
Hi guys, Does any one know how to detect when a SQL transaction has been rolled back in either a windows application or ASP.NET. My Transactions always run but when they are rolled back Visual Basic does not pick up any errors in the 'Try Catch SqlException'. Does any one know a way round this. Sorry for the lack of code. Im writing this post on a friends PC but i wil put up my code as soon as possible. Thanks in advance Matt I guess you are having sql transaction(s) in some stored-procedures. In the stored-procedure you can use RAISERROR("Transaction failed", 16, 1) af...

Informix SQL vs T-SQL
Hi, My boss has asked me to interview a SQL developer next week, but looking at the resume, it looks like this person has only worked with Informix, don't know versions or anything else. Has anyone out there worked with Informix? How close to t-sql is the sql used by informix? ...

Transact-SQL issues with SQL Anywhere...
Hello, I am trying to write a stored procedure that I can use in SQL Anywhere AND in Sybase ASE. I am having compatability issues with Watcom-SQL versus Transact-SQL. I don't think the following Watcom-SQL procedure can be written in transact-SQL in such a way so that it can be used in both environments (ASA and ASE). WATCOM-SQL: alter procedure "DBA".sp_RebuildKeyPool_copy() begin declare nextid integer; set nextid=0; truncate table s_keypool; for eachdatabaseid as databaseidcursor dynamic scroll cursor for select database_id as currdatabaseid ...

SQL Express vs SQL Enterprise ??
The data limit in SQL Express is 4 GB and SQL Enterprise is unlimited. I have SQL Express installed on my desktop machine. and SQL Enterprise on my web server. Now, I am thinking to develop solution in ASP.NET with SQL Express. and finally upload the solution on web server, where SQL Enterprise is installed. So, will there be any problem in such case ?In other words, the mdf file created by SQL Express and SQL Enterprise is same or different ?  You shouldnt have any problem . You should be able to migrate to the enterprise edition easilyJeev~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~If you ge...

sql 2000 vs sql express
hai,I just have one simple question.Can I use sql 2000 instead of sql express and why?what about the istallation for sql 2000.thanks. Sure you can use sql 2000. You can connect to a existing SQL 2000 server via the Data Explorer. You might want to try the sql express because it is the evaluation version of sql 2005.Barry------------------------------------------------------------This posting is provided "AS IS" with no warranties, and confers no rights....

Transact SQL problems
Hello, I have been working for many years with Watcom SQL, but in order to standardize, we are converting everything into Transact SQL, and I am finding some problems. One such problem is the following function that will only return an Integer, not a decimal using T-SQL, but returns a decimal correctly when converted W-SQL. Am I doing something wrong? create procedure dba.f_get_service_level_booking(@s_start_week char(6),@s_end_week char(6)) as begin declare @n_return decimal(6,2) select @n_return = convert(decimal(6,2),100.00*sum(1.00*qty_booked) / (sum(1.00*...

Sql Transaction Problem...
Hi Everybody,   I have with this sql statement in stored procedure, seems confused what is wrong?Error Message:"System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint \"FK__RelatedDo__Docum__31EC6D26\". The conflict occurred in database \"<dbname>\", table \"dbo.DocumentFiles\", column 'DocumentFileID'.\r\nCannot roll back IdeaRelatedDownLoad. No transaction or savepoint of that name was found.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is ...

ANSI SQL Problem
Hi, New to the sybase world.Does the Sybase Adaptive Server 11 supports the ANSI SQL join syntax.When I try to run a query like this, SELECT r.Symbol,r.HostFormat,s.LineFormat,r.SplPostPanel,r.SplName,r.CorpName FROM Temp1 r FULL JOIN Temp2 s ON r.Symbol *= s.Symbol AND r.HostFormat *= s.HostFormat I get the error : Incorrect Syntax near FULL. Any help or pointers will be appreciated. TIA, gaurav That would be FULL OUTER JOIN you wanted? Neither Sybase Adaptive Server Anywhere version 7.0.1 (the subject of THIS forum) nor Adaptive Server Enterprise version ...

Combine WATCOM SQL and TRANSACT SQL Syntax
The WATCOM and TRANSACT SQL syntax descriptions should appear together in the Help. There are features of both which are helpful, and it is less than productive to have to read two Help topics to read about one SQL topic. For example, the syntax for SELECT is presented in two places, and they aren't connected in the "See Also" list. Also, the Help - Index facility should always jump to the syntax description as the first choice when a keyword like SELECT is chosen, not some general tutorial or other topic. Breck Hmmm. We'll have to see about this one. Here is...

SQL: Run transaction on multiple sql servers
I have a stored procedure in which I am inserting record in multiple tables on multiple database SQL servers. At the start of the SP, I am starting Transaction and and the end commiting the transaction. Server running Stored Procedure is win 2003Second server is win 2000I am getting following error,The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::Joi...

Reference manual for watcom-sql and transact-sql
The on-line help for ASA 8 and ASA 9 have a few tidbits about watcom-sql and transact-sql... but there doesn't seem to be a language reference. At least not anywhere I can find one. Can anyone point me in the right direction? Regards, Mike Niemann For clarification, the T-SQL support in SQL Anywhere is for compatibility only. SQL Anywhere does not provide a complete implementation of the T-SQL language. For ASA9 (prior versions have equivalent documentation), see ASA SQL Reference SQL Statements Using the SQL statement reference Statement applicabili...

Web resources about - Transact-SQL vs ANSI SQL problem - sybase.sqlanywhere.general

Transact-SQL - Wikipedia, the free encyclopedia
Transact-SQL ( T-SQL ) is Microsoft 's and Sybase 's proprietary extension to SQL . SQL, the acronym for Structured Query Language, is a standardized ...

Resources last updated: 2/17/2016 6:52:45 AM