Re: SET ENGINEBEHAVIOR 70?????

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chad (chad_at_teche.net)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 07:59:03 -0500

Leonid,

One query is quicker to write (and sometimes process) than two queries,
but again, you are taking some things for granted due to an assumed
knowledge of the data. You are assuming that you don't have any two (or
more) rows in the parent table where the id field is the same and f1 -
f100 fields are the same and / or different. Granted, you probably assume
this because the id field is the primary key of the parent table. Just
remember, simply because something is easier, doesn't mean it's the right
way to do it. Even with the id field being the primary key, it is indeed
possible to get data corruption and have multiple rows with the same key
(I've seen it). And again, with the 70 behavior'd query, you'd never know,
but with the proper query you would.

HTH,
Chad

On Thu, 29 Jul 2004 11:57:07 +0300, Leonid <leonid@NOgradaSPAM.lv> wrote:

> While I absolutely agree with you concerning your example, there are
> examples where with ENGINEBEHAVIOR 70 you may use more simple SQL
> statement.
> Let's consider
>
> create table Parent (id I, f1 C(1), f2 C(1), ..., f100 C(1))
> create table Child (id I, id_parent I, amount N(10,2))
>
> Your task is to get table with all fields from the Parent and total
> amount
> from Child. With ENGINEBEHAVIOR 70 you may use
>
> select Parent.*, sum(Child.amount) as Total from Parent join Child on
> Parent.id=Child.id_parent group by Parent.id
>
> and in this case you are absolutely sure that you will get right f1, ...,
> f100. With ENGINEBEHAVIOR 80 you must write
>
> select Parent.*, sum(Child.amount) as Total from Parent join Child on
> Parent.id=Child.id_parent group by Parent.id, Parent.f1, ..., Parent.f100
>
> or at least
>
> select Parent.*, sum(Child.amount) as Total from Parent join Child on
> Parent.id=Child.id_parent group by 1, 2, ..., 101
>
> or use two SQL statement
>
> select Parent.id, sum(Child.amount) as Total from Parent join Child on
> Parent.id=Child.id_parent group by 1 into cursor C1
> select Parent.*, C1.total from Parent join C1 on Parent.id=C1.id
>
> Leonid 

-- 
There are two types of people in this world:
* Those who need closure

Relevant Pages

  • Re: SET ENGINEBEHAVIOR 70?????
    ... Yes, you are right, I assume id is the primary key, so it's unique. ... absolutely sure that query with 70 behavior gives you right result. ... m.str3=m.str3+" into cursor tmp2" ... from Parent join Child on Parent.id=Child.id_parent; ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: DISTINCTROW
    ... the sql statement is querying 2 tables and 1 stored query. ... but these are not unique indexes. ... tblB does not have primary key and does not have index. ...
    (microsoft.public.access.queries)
  • Re: Create an MS Access Table in VB6
    ... You can run a simple DDL query to ... MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY, ... cn.Execute strsql ' the above sql statement ... create an AutoNumbering Index in my table as a Primary Key. ...
    (microsoft.public.vb.general.discussion)
  • referencing the outermost table from an inner view which is inside a subselect
    ... create table alm ( ... all_cod varchar2primary key, ... This mean that I have to write a SQL statement that cycles through all ... first query passing to it the values retrieved from all_cod_list instead ...
    (comp.databases.oracle.server)
  • Re: how to find out if there is any primary key in each table
    ... This query will return only those tables that do not have a primary key. ... > What is the command / stored procedure that will tell such information in ... > sql statement. ...
    (microsoft.public.sqlserver.programming)