HDTenEightyP/NetNews huggingface dataset:
From: kdomb@ctp.com (Kevin Dombroski)
Subject: Order of records when inserting
Summary: Is order preserved?
Organization: Cambridge Technology Partners
Distribution: usa
Date: Tue, 17 Nov 1992 18:57:04 GMT


Is there any guarantee of the order of records after doing several inserts
to table, and then doing a "select *" from the table (ie., no "order by")?
I've always understood that there can be no guarantee, but I can't find any-
thing in the manuals to support/dispute this, and I'm waiting for a call back
from Sybase Tech Support. For some reason, I feel like I'll get the answer here
faster...
				
I'm dealing with data coming from an IBM mainframe, and the arrangement of
the returned data must be preserved (I know, I know...at least it's not a
punch card system :-). After stuffing it into a temp table, it doesn't
appear to preserve the order. Is there some way this can be accomplished?


				KevinD


-- 
================================================================================
Kevin M. Dombroski                  | "One good thing about music,
Cambridge Technology Partners       |  When it hits, you feel no pain...." 
================================================================================
Newsgroups: comp.databases.sybase
From: lbertoli@magnus.acs.ohio-state.edu (Lawrence E Bertolini)
Subject: Re: Sybase books
Organization: The Ohio State University
Date: Tue, 17 Nov 1992 18:59:18 GMT

"A Guide to Sybase and SQL Server", D. McGoveran with C.J. Date, 1992,
Addison-Wesley Publishing Co, ISBN 0-201-55710-X.  Price is around $50.
-- 
Larry Bertolini       | "Patriotism is your conviction that this country 
Ohio State University | is superior to all other countries because
University Systems    | you were born in it." - George Bernard Shaw
From: dowen@praxis.co.uk (David Owen)
Newsgroups: comp.databases.sybase
Subject: Select * (Was: Re: multiple EXISTS ...)
Date: 18 Nov 92 12:09:19 GMT
	<1992Nov15.144258.18635@panix.com>
	<1992Nov15.225853.2269@lamont.ldgo.columbia.edu>
	<1e8fj7INN1sg@gazette.bcm.tmc.edu>
	<1992Nov16.184540.11698@lamont.ldgo.columbia.edu>
Organization: Praxis Systems, Bath, UK

>>>>> Regarding Re: multiple EXISTS subqueries under Sybase 4.8; msolda@lamont.ldgo.columbia.edu (M Solda) said:

[stuff deleted ...]

M> select last_name, first_name, salary, title
M> from employee_id i1, salary_history s1, employee_status e1
M> where not exists (select *
                     ^^^^^^^^^
M>                   from employee_status
M>                   where status_begin < '1/1/0'

[large query deleted ...]

If you were using Oracle this would be replaced by 

        "... (select null ...) ..."  

I was told on a Sybase course that this syntax is Oracle specific and the best
Sybase solution is 'select "x" from ..." for sub-queries.  Is this correct?

dowen
--
David Owen, Praxis plc, 20 Manvers Street, Bath BA1 1PX UK.
Tel:	+44-225-444700.   Email:   dowen@praxis.co.uk

 "You needn't be well to be wealthy,
  but you've got to be whole to be holy"
                              -- Greg Lake
Newsgroups: comp.databases.sybase
From: lhall@csfb1.fir.fbc.com (Lawrence Hall)
Subject: RE: Order of record when inserting 
Reply-To: uunet!csfb1!lhall
Organization: First Boston Corporation
Date: Wed, 18 Nov 1992 14:48:03 GMT

Kevin Dombroski writes :

[ Is there any guarantee of the order of records after doing several inserts
[ to table, and then doing a "select *" from the table (ie., no "order by")?
[ I've always understood that there can be no guarantee, but I can't find any-
[ thing in the manuals to support/dispute this, and I'm waiting for a call back
[ from Sybase Tech Support. For some reason, I feel like I'll get the answer here
[ faster...
[ 
[ I'm dealing with data coming from an IBM mainframe, and the arrangement of
[ the returned data must be preserved (I know, I know...at least it's not a
[ punch card system :-). After stuffing it into a temp table, it doesn't
[ appear to preserve the order. Is there some way this can be accomplished?
[ 
[ 				KevinD
[ 

I have found that the order seems to be preserved when using Fast BCP to insert
data, but not always on inserts or Slow BCP's.  However - I prefer that users 
at our site do not rely on the order of records since there is no guarantee of 
the order.  If the records come back in a certain order, and the application 
rely's on this, then the app will have problems if the clustered index is 
changed, if the data is unloaded and re-loaded or when we change DBMS versions. 

I tell our programmers to use an ORDER BY whenever they need a specific order. 
In the case you speak of, it might be necessary to use a timestamp column on 
the table and use that for the ORDER BY.  

The only time that we rely on the order of inserted records is when we are
doing a fast BCP of a large table and want to use the 'with sorted data' option
when creating the clustered index. 

" " " Lawrence


----------------------------------------------------------------------------
Lawrence E. Hall
First Boston Corp.
5 World Trade Center 9th Floor                    uunet!csfb1!phantom!lhall
NYC, NY  10048                                    lhall@csfb1.fir.fbc.com
----------------------------------------------------------------------------
The Secretary will disavow all knowledge.
From: mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons)
Newsgroups: comp.databases.sybase
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Date: 19 Nov 1992 20:41:17 GMT
Organization: Baylor College of Medicine, Houston, Tx
Distribution: world
Reply-To: mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons)
NNTP-Posting-Host: fleming.csc.bcm.tmc.edu
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Originator: mparsons@fleming.csc.bcm.tmc.edu



msolda,

	OK, let me ask you this . . . concerning the combined EXIST and
NOT EXIST . . . 

	If the EXIST test does not work, i.e., it's false, does the
NOT EXIST test come out true?

	In other words, does the NOT EXIST part work, or at least
get tested, if the EXIST part fails?

	If so, the problem could be that the server is not evaluating
the NOT EXIST portion of the test since the EXIST portion solves your
overall 'OR' test, i.e., if the EXIST is true, there's no need to 
do the NOT EXIST test.

	Of course, if that's the case, then Sybase is definitely
a 'little' screwed up because . . . . APT will evaluate both arguments
to the OR regardless of whether or not the first test is true.  It'd
be interesting to find out the the server and APT handle their OR's
and AND's differently :-(

	Hmmmmmmmm, let me confuse this a little bit more?? ;-)

	In most languages the clauses that are 'connected' via an OR
are evaluated one at a time.  As soon as one of the tests evaluates as
TRUE the testing is stopped since you only need one of them to be TRUE
for the OR to be TRUE.  Similar logic says that testing on clauses
'connected' by an AND will halt as soon as one of the tests comes up
FALSE; reason being that all clauses must be TRUE for the AND to be
TRUE soooooo, if any one of them comes up FALSE the entire AND is
FALSE and no more testing is needed.

	In APT, this is not the case, *all* clauses 'connected' by an
OR or AND, and I mean *ALL* clauses will be evaluated.  All ten clauses
in a large OR statement will be tested even if the first one evaluates
to TRUE.  And according to tech support, this is the way APT is suppose
to work.

	Now, if the server works as I've mentioned above, i.e., testing
stops as soon as one clause evaluates as TRUE(for an OR) or FALSE(for
an AND), then it would appear that Sybase is inconsistent in their 
method of evaluating ANDs and ORs??
 
Does that shed any light on the problem? Or am I beating a dead horse? ;-)

Comments from others that have 'played' with this?

Mark

	
	
Newsgroups: comp.databases.sybase
From: nigelc@cognos.com (Nigel Campbell)
Subject: Sybase books
Organization: Cognos Incorporated, Ottawa CANADA
Date: Mon, 16 Nov 1992 00:16:19 GMT

I understand that Chris Date has a new book out on Sybase (includes
V5). Who is the publisher, what is the title,isbn,price etc etc.
thanks.

-- 
Nigel Campbell          Voice: (613) 738-1338 ext 3016    P.O. Box 9707
Cognos Incorporated       FAX: (613) 738-0002             3755 Riverside Dr.
Database and Connectivity MCI: nigel campbell || 307-4729 Ottawa, Ontario
UUnet: nigelc@cognos.COM                                  CANADA  K1G 3Z4
Newsgroups: comp.databases.sybase
Subject: MS SQL server gives additional row
From: yair@huha.cs.huji.ac.il (Yair Friedman)
Date: 10 Nov 1992 18:31:45 GMT
Reply-To: yair@cs.huji.ac.il
Distribution: world
Organization: The Hebrew U. of Jerusalem, Computer Science Dep.
NNTP-Posting-Host: huha.cs.huji.ac.il

Hi,

I wrote sme application which worked well on sybase 4.2
When I ported it to MS sql server (4.2) I get for each query
an additional row with null value.  When I try to make the
query manually via isql, I get the expected normal results.
Any suggestions?

--
					Yair Friedman.
yair@cs.huji.ac.il
yair%cs@hujivms.BITNET
From: mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons)
Newsgroups: comp.databases.sybase
Subject: Re: APT and TEXT Fields
Date: 16 Nov 1992 15:50:17 GMT
Organization: Baylor College of Medicine, Houston, Tx
Distribution: usa
Reply-To: mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons)
NNTP-Posting-Host: fleming.csc.bcm.tmc.edu
Originator: mparsons@fleming.csc.bcm.tmc.edu


In article <1992Nov13.162033.4728@bnlux1.bnl.gov>, lally@pmdb.sep.bnl.gov (Keith Lally) writes:
|> 
|> I would like to display help screens throughout my APT 
|> application that are retrieved from a helptext table.
|> When a function key is pressed the help form execs a
|> stored procedure on the server that returns the help
|> text for the particular form.  Does anyone know how
|> to return a text field from the server into an APT 
|> form?  Is it possible?  

Our on-line help function just retrieves the appropriate
help message from a table via a submit-sqlbegin-sqlend
statement.  Very easy . . .  

When you say 'text', do you mean datatype TEXT, or just
a character string(char or varchar)?

I can think of two ways to return text from a stored
procedure call.

One is to send back a string as the error message for an
error that you generate via the raise-error command.  Of
course, you then have to 1) test for the occurrence of
the 'error' and then 2) parse the resulting error text to
pull out the 'overhead'.  Kinda messy for this situation.

The other is to just pass it back as a return parameter.


Hmmmmm, that's 3 ways . . . . . or did I miss something in
the question? ;-)

Mark
From: msolda@lamont.ldgo.columbia.edu (M Solda)
Newsgroups: comp.databases.sybase
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Date: 15 Nov 92 22:58:53 GMT
Reply-To: msolda@lamont.ldgo.columbia.edu
Organization: Lamont-Doherty Geological Observatory

In article <1992Nov15.144258.18635@panix.com>, dbenua@panix.com (David Benua) writes:
> In <1992Nov14.174847.29576@lamont.ldgo.columbia.edu> msolda@lamont.ldgo.columbia.edu (M Solda) writes:
> 
> >does anyone know if there is a limitation in the number of EXIST subqueries
> >that can be in a single query?  i have a query with an EXISTS and a NOT EXISTS
> >joined by an OR, but only the rows that meet the EXISTS criteria are reported.
> 
> >a separate query for each EXISTS provided the correct answer in each case.
> 
> >am i breaking syntax somewhere?
> 
> I've had the same problem with 2 EXISTS in the same subquery, I got around it
> by transforming the OR into an AND (DeMorganizing) and reversing the logic.
> But, of course, this will not work in all cases (or even most cases).

just to follow up, after posting my original article, i solved the problem by
using a UNION operator to join the results into to identical queries, except 
of course, for the EXISTS clauses.

i did try "transforming the OR into an AND", but as Dave points out, this would
not work in all cases and this was such a case.

anybody know if this is a reported bug?

msolda
Newsgroups: comp.databases.sybase
From: sommar@enea.se (Erland Sommarskog)
Subject: Re: How to connect APT-Form to different database?
Organization: Enea Data AB
Date: Sun, 15 Nov 1992 20:41:37 GMT

Klaus Schniedergers (eedksc@teamos50.ericom) writes:
>I have the following problem with the behated APT-Forms:
> I want my form to be able to connect to different databases, i.e. defining
>a database name in an environment variable then starting the form
>without any recompiling or interactive input.

Well, what exactly do you want to do? Do you want to run your
application against different instances of the same database
definition, or do you want to something more advanced? I am
assuming that you want to do the former, since we have had this
problem.

>When creating the form, the database name is encoded in several places:
>- the form-preprocessing-procedure (as ' sqlbegin use <db-name> sqlend ')
>- the sql column connected to fields (<db-name>.dbo.table)

First thing to do, is to remove all those 'use <db-name>' suckers and
rely strictly on the default database. If you are running the application
from your own startup program, the best idea is to look on the environ-
ment variable and set the default DB before anyting else. If you are
using aptexec, set the default DB in a surrounding script. Yes, this
means that the same user cannot run two different databases at the
same time, but that is what you get with such a corny concept as a
default database.

In our application, we use a slightly different approach. There is
no environment variable, and the users cannot change the database
on their own, but only the appointed super user can from a special
menu. (They are not super users in the wide Unix sense, they are
just as unexperienced of computers as the ordinary users, so neither
they have shell access.)

>A dirty solution would be running the <name>.frm file through an sed-script,
>changing the database-name to the new one. But I would like a more
>elegant solution.........

Well, you don't need the shell script, Sybase has aptmig for you to
do the task. But beware about the interface, which is very un-Unix.
If you say "aptmig -n new_db *" you will cause a mess; you have to
use find(1).

However, as long as the databases are sufficiently identicial and
accessible to everyone there is no need for migration. The only
thing you have to make sure is that everyone is a user in the
database to which the forms refer. If this is a security issue,
make this an empty database.

Yes, if the database are different this is a mess with hard-coded
database names in the forms, but when did APT make anyone happy?
-- 
Erland Sommarskog - ENEA Data, Stockholm - sommar@enea.se
From: tosma@ub-tky.ubspd.co.jp (TOSMA-GUEST)
Newsgroups: comp.databases.sybase
Subject: "Select" from one server to ther other server
Date: 18 Nov 92 05:59:10 GMT
Organization: UBS Phillips & Drew Int'l Ltd., Tokyo


					18 Nov 1992

	Dear sybase guru:


	I am looking for the the way to do 

	"select * from table where some condition" in a database in A-server,
	 ^^^^^               ^^^^^
	then transfer the data into a different database in B-server.

	Is there any way to do this kind of things without using 

	Open-client Library???


	Any suggestion will be appreciated.

	Thank you.

	Etsuko Shimabukuro :-)

	P.S.

	The Sybase manual says that "select" does not support
	"select" between different servers.

	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	Etsuko Shimabukuro 	

	tosma@ubspd.co.jp	
	KDDLAB!UBSPD.CO.JP!TOSMA@uunet.uu.net
	tosma@ubspd.cp.jp@kddlab.co.jp

	*Hope that every place in the world will 
	 be connected by e-mail soon.
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Newsgroups: comp.databases.sybase
From: brianm@meaddata.com (Brian Montgomery)
Subject: how to pass extra parameter to sybase err_handler
Organization: Mead Data Central, Dayton OH
Date: Mon, 23 Nov 1992 14:21:25 GMT
Distribution: oh



I want to pass the name (SCCS version,module etc) as a string to the 
err_handler in sybase , so that if I get any error during database
processing I would know which file generated the error.  
I would appreciate it if you email me your responses 
Thanks in advance

Newsgroups: comp.databases.sybase
From: dmenns@muug.mb.ca (Darren Enns)
Subject: Recursive Table Trigger Protection?
Organization: Manitoba Unix User Group, Winnipeg, Manitoba, Canada
Distribution: comp.databases.sybase
Date: Tue, 17 Nov 1992 00:17:11 GMT

Greetings all.  As new situations arise in our very immature Sybase environment
we DBA's try to protect the integrity of our data using triggers.  The newest
pickle we have encountered is the classic recursive table.  We feel that we
should try to protect the table from a looping recursive relationship, but
we don't know of any 'clever' way to do so.  Please help if you can.

For the specific example, we have essentially a 'region' table where regions
are contained in other regions (and are connected via foreign keys).  For
example, let me use an 'American' example:

California is a region in the United States
The United States is a region in North America
North America is a region of the World

An error would now be if the World was a region of California.

Dare
From: cgd@ecmwf.co.uk (Dick Dixon)
Newsgroups: comp.databases.sybase
Subject: Sybase contact in UK?
Date: 17 Nov 92 09:03:57 GMT
Organization: ECMWF

Can someone please give me an email address, phone number or
other point of contact in the UK? <default FAQ apologies here>

			Dick D.
Newsgroups: comp.databases.sybase
From: jbaron@higgs.ssc.gov (Jeff Baron)
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Reply-To: jbaron@higgs.ssc.gov (Jeff Baron)
Organization: Superconducting Super Collider Lab
Date: Fri, 20 Nov 1992 19:45:01 GMT

[problems with exists and not exists]

I've had this problem too, the first time, about three years ago.
[Hard to believe it's still around.]

In any event, in my case, the problem had to do with a statement
that also consited of multiple 'exists' expressions separated by
or's.  For example,

    if ((exists (select colname1 from table1 where condition1))
            or (exists (select colname2 from table2 where condition2)))
        begin
            statement
        end

When table1 was empty, the first part of the condition should
return false, of course.  In fact it may have, but this 'emptiness'
caused the large, multiple condition, regardless of the other parts, to
return false as well, and 'statement' was never executed.

--
Jeff Baron
jbaron@gauss.ssc.gov
From: kyp@stsci.edu (Mark Kyprianou)
Newsgroups: comp.databases.sybase
Subject: WANTED: SQL formatter
Date: 19 Nov 92 16:22:01 GMT
Organization: Space Telescope Science Institute


I'm looking for a SQL formatter, preferably in C or C++.  Formatting a 
"SELECT" statement is what I need most.

Sorry, if this a FAQ.

Please email.
 
-- 
     Mark        Space Telescope Science Institute, Baltimore, MD 21218
     Kyprianou   Internet: kyp@stsci.edu       UUCP: ...!noao!stsci!kyp
Newsgroups: comp.databases.sybase
From: msolda@lamont.ldgo.columbia.edu (M Solda)
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Reply-To: msolda@lamont.ldgo.columbia.edu
Organization: Lamont-Doherty Geological Observatory
Date: Fri, 20 Nov 1992 14:42:11 GMT

In article <1egu5dINNmie@gazette.bcm.tmc.edu>, mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons) writes:
> 
> 
> 	OK, let me ask you this . . . concerning the combined EXIST and
> NOT EXIST . . . 
> 
> 	If the EXIST test does not work, i.e., it's false, does the
> NOT EXIST test come out true?
> 
> 	In other words, does the NOT EXIST part work, or at least
> get tested, if the EXIST part fails?

the intention either could be true then the expression would be true.  i did
not think that if either the EXISTS not the NOT EXISTS fails, the other would
not be tested.  i would think that the converse would be true (if one is
true, then the other is not tested), meaing short-circuit logic.  but
what appears to be happening is only the EXISTS gets evaluated.  even
changing the order of the EXISTS and the NOT EXISTS around the OR does not
matter.  the results are the same as if the NOT EXISTS was not included.

> 	If so, the problem could be that the server is not evaluating
> the NOT EXIST portion of the test since the EXIST portion solves your
> overall 'OR' test, i.e., if the EXIST is true, there's no need to 
> do the NOT EXIST test.

this is not the case.  when the EXISTS not true, the NOT EXISTS should be
evaluated (short circuit logic or not) and then that could be true.

> 	In most languages the clauses that are 'connected' via an OR
> are evaluated one at a time.  As soon as one of the tests evaluates as
> TRUE the testing is stopped since you only need one of them to be TRUE
> for the OR to be TRUE.  Similar logic says that testing on clauses
> 'connected' by an AND will halt as soon as one of the tests comes up
> FALSE; reason being that all clauses must be TRUE for the AND to be
> TRUE soooooo, if any one of them comes up FALSE the entire AND is
> FALSE and no more testing is needed.

i agree with this statement.

> Does that shed any light on the problem? Or am I beating a dead horse? ;-)
> 
> Comments from others that have 'played' with this?

sorry, it does not on my end, though i may be being very dense.  does any one
know if this is a know bug?

msolda
Newsgroups: comp.databases.sybase
From: root@ssf-corp.dhl.com (Operator)
Subject: test
Date: Wed, 18 Nov 1992 17:16:15 GMT
Distribution: sa
Organization: DHL Worldwide Express, Redwood City, Calif., USA


test
Newsgroups: comp.databases.sybase
From: sommar@enea.se (Erland Sommarskog)
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Organization: Enea Data AB
Date: Fri, 20 Nov 1992 21:39:26 GMT

Hm, this discsussion reminds me of a bug that we've stumbled on.
But our case was a bit simpler. Basically we had:

     SELECT ...
     WHERE  a AND b AND (c1 OR c2) AND d AND E

where a, b etc were various conditions. The query failed in some cases
when c2 returned false, despite c1 being true. And, as you might have
guessed c2 was an EXISTS or NOT EXISTS. Things went wrong when there
were no records at all in the table searched by c2. I did also
try IN with a subquery, that did also fail.

Now what is interesting here is not the bug we had, but that the
fact it is so similar to the bug originally reported here which
was under 4.8, and we are still on 4.0.2 (AIX 3.1). I mean we
didn't spend a time reporting something so obvious, when we
have such an old version.
-- 
Erland Sommarskog - ENEA Data, Stockholm - sommar@enea.se
Newsgroups: comp.databases.sybase
From: spencer@med.umich.edu (Spencer W. Thomas)
Subject: systypes?
Date: Thu, 19 Nov 92 17:17:43 EST
Organization: University of Michigan
Distribution: comp

What's the SQL to map a user type to the corresponding system type?  I
thought I had it.  I'm using this SQL to try to extract column name,
system type, and length. 

select c.name,v.name,c.length \
	      from syscolumns c,sysobjects o,master.dbo.spt_values v \
	      where c.id=o.id and o.name="table" and c.type=v.low and\
			v.type="J"

Works fine, except that not all system types are in the
master.dbo.spt_values table.  Mine is missing image, sysname, text,
and timestamp.  Another DB I checked is also missing smalldatetime, 
smallmoney, and real.

An observation I've made is that the "usertype" for all system types
is <100.  Can I count on this?

--
=Spencer W. Thomas 		|  Info Tech and Networking, B1911 CFOB, 0704
   "Genome Informatician"	|  Univ of Michigan, Ann Arbor, MI 48109
Spencer.W.Thomas@med.umich.edu	|  313-747-2778, FAX 313-764-4133
Newsgroups: comp.databases.sybase