HDTenEightyP/NetNews huggingface dataset:
From: charleen@presto.ig.com (Charleen Bunjiovianna Stoner)
Newsgroups: comp.databases.informix
Subject: Need help with a lookup join that shouldn't be
Date: 20 Aug 92 19:46:51 GMT
Organization: IntelliGenetics Inc., Mountain View, California

I'm running INFORMIX-SQL Version 2.10.03F.

I have a screen form that uses a table, info.  There's a state column
in info.  One of my users wants to see territories (which will depend 
on state) on this form.  He wants the territory information to reside
in another table, salestate, which has a state column, and three
territory columns.

No problem, right?  You just do a lookup join:

[attribute] = info.state = salestate.state,
	lookup [ter1] = salestate.territory1 joining *salestate.state,
	lookup [ter2] = salestate.territory2 joining *salestate.state,
	lookup [ter3] = salestate.territory3 joining *salestate.state;

...and it looks fine:

Territory     WE  WE  WE
[...portion of form deleted]
City         [San Francisco            ]  State [CA]      Zip [94110     ]

No problem, except that this user wants to be able to query on the
territory fields (to get all the records of the appropriate states), 
which we all know you can't do with a lookup field.

Is there a way to do something *like* a lookup without actually using
a lookup join?  

Sorry if this is a dumb question or if I'm missing something obvious...

Charleen
-- 
I'm not fat; I'm gravitationally-challenged.
From: maint72@saad-emh1.ARMY.MIL (Michael Gordon)
Newsgroups: comp.databases.informix
Subject: Lookup and Query.
Date: 21 Aug 92 23:18:03 GMT
Reply-To: maint72@saad-emh1.ARMY.MIL (Michael Gordon)
X-Informix-List-ID: <list.1392>



REPLY TO: CHARLEEN STONER

REFERENCE: LOOKUP ROWS AND QUERY.


< I'm running INFORMIX-SQL Version 2.10.03F.
< I have a screen form that uses a table, info.  There's a state column
< in info.  One of my users wants to see territories (which will depend 
< on state) on this form.  He wants the territory information to reside
< in another table, salestate, which has a state column, and three
< territory columns.
< No problem, right?  You just do a lookup join:
< [attribute] = info.state = salestate.state,
<	lookup [ter1] = salestate.territory1 joining *salestate.state,
<    lookup [ter2] = salestate.territory2 joining *salestate.state,
<	lookup [ter3] = salestate.territory3 joining *salestate.state;
< ...and it looks fine:
< Territory     WE  WE  WE
< [...portion of form deleted]
< City         [San Francisco            ]  State [CA]      Zip [94110     ]
< No problem, except that this user wants to be able to query on the
< territory fields (to get all the records of the appropriate states), 
< which we all know you can't do with a lookup field.
< Is there a way to do something *like* a lookup without actually using
< a lookup join?  
< Sorry if this is a dumb question or if I'm missing something obvious...
< Charleen


Charleen,

( The ONLY REALLY dumb question, is the question NOT asked because it was
thought to be a DUMB QUESTION.)

It sounds to me like you need to use a MASTER OF and DETAIL relationship
between the two tables you have used for the lookup commnad.

The master/detail relationship simplifies cross-table queries, especially when
one row of the master table is associated with several rows of the detail
table.

(Above statement drawn from the Informix Reference Manual for SQL v. 2.1.0)

In this manner the user can query the master table for the state in question,
obtain the necessary basic information, execute the MASTER/DETAIL command to
shift over to the detail table and query the territories fields.

Good luck.



					Michael S. Gordon
					Foreman, Gyroscope Section
					Avionics Division 

 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 *  Michael Gordon                           Sacramento Army Depot           *
 *  Foreman, Gyroscope Section               Directorate For Maintenance     *
 *  E-Mail: maint72@saad-emh1.army.mil       Attn: SDSSA-MAV-320             *
 *  DSN:    839-2367                         Michael S. Gordon               *
 *  Comm.:  (916) 388-2367                   Sacramento, CA 96813-5036       *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
From: davek@informix.com (David Kosenko)
Newsgroups: comp.databases.informix
Subject: Re: porting a database to ano
Date: 20 Aug 92 16:05:06 GMT
Organization: Informix Software, Inc.

In article <9349@emory.mathcs.emory.edu> dave.snyder@snide.com (Dave Snyder) writes:
>Quoting David Kosenko...
>
>> (as dbimport and dbexport ship only with OnLine).  The second solution
>
>Hmmm... now thats interesting, all my SE's came with dbexport/dbimport.  My
>platforms include Motorola Delta Series, IBM RS/6000, and SCO Unix.

I suppose I stand corrected on this.  Since these utilities were originally
designed with migration to/from Turbo/OnLine in mind, I thought that we
didn't package them with SE.  Dave's experience refutes this, so I yield
to the empirical evidence.

Dave
-- 
Disclaimer: These opinions are not those of Informix Software, Inc.
**************************************************************************
The heart and the mind on a parallel course, never the two shall meet.
						-E. Saliers
Newsgroups: comp.databases.informix
From: neil@salmon.demon.co.uk ("Neil S. Briscoe")
Subject: Redeclaration of local variables (?)
Date: Wed, 19 Aug 1992 15:13:41 +0000

Platform: Sun 3/100, SunOS4.1
INFORMIX-4GL Version 4.00.UD3 with an INFORMIX-SQL Version 4.00.UD4
Standard Engine.

I had the message:
ut_merge.c Line 4351 redeclaration of prepare_sel_

followed by the usual fatal error diagnostics from make.  The same
module makes fine under Version 4.1 on a SPARC and on 1.10.00B on a
SunOS3.5 machine.

When I checked the source module, prepare_sel_ is a *local* variable
defined in three separate functions.  It's used for the same purpose
in each, to put the results of a CONSTRUCT statement in.  In two of
these functions it was defined as a CHAR(300) and in one as a
CHAR(500).  It shouldn't matter surely.  As a local definition in each
function, it should get it's own allocation irrespective of any other
local variable of the same name.

Yes, I know this is 4.0 and we did try to avoid getting this because
of what I've read in this group before.  However, I'm told that
Informix have not ported 4.1 to this platform and, as it's going out
of fashion, I doubt if they ever will.

I got round the problem by changing the defines in the other two
functions to CHAR(500), but sheesh, this is probably only the first of
many strange bugs that I'm going to find.

Anybody got the list of defacto bugs for this release?

Regards
Neil

Neil S. Briscoe				Telephone: +44 252 376737
System Administrator			Fax:       +44 252 376644
                                        Email: neil@salmon.demon.co.uk
                                               nbriscoe@cix.compulink.co.uk
Bartec Medical Systems Ltd.
Impression House
Invincible Road				"!##@!** Listen who swears
Farnborough				 Christopher Robin has fallen downstairs"
Hants GU14 7NP				-- The Goodies Book of Criminal Records
England
"A man is incomplete until he's married -- then he's finished!"
-- Seen on a desktop calendar.
From: manmetha@gauss.rutgers.edu (Rajesh Malhotra)
Newsgroups: comp.databases.informix
Subject: Estimating size of DB Chunk
Keywords: Numbers from dbschema -- reliable??
Date: 21 Aug 92 17:08:56 GMT
Organization: Rutgers Univ., New Brunswick, N.J.


Hiya fellow Informixers,

	A question on estimating the size of a DB chunk.

	How good are the numbers reported by the dbschema utility
on the row size, # of columns, and index size, in determining the space
required by a row. i.e

	does index size == actual index size + overhead

		or

	index size == actual index size.

Would these numbers represent the max size of a row in a table?

	I have a good estimate of the number of rows in my tables. Is the use
of numbers reported by dbschema a fairly standard way of estimating total
space requirements? Are there any other Informix utilities that I could
use for my purpose??

	Any and all responses would be greatly appreciated.


Raj.
-- 
===============================================================================
  __  __     .
 /   __/    /                               Raj Malhotra.
/   ( /_   /                                voice : (908)613 4313. 
        __/                                 email : manmetha@gauss.rutgers.edu

===============================================================================
Newsgroups: comp.databases.informix
From: shemminga@cix.compulink.co.uk (Stuart Hemming)
Subject: Informix I-Net problem
Reply-To: shemminga@cix.compulink.co.uk
Date: Wed, 12 Aug 1992 16:44:00 +0000


Can anyone help me? We're running a small net of 386/486 boxes. On
the 486 machine we've got Informix SE installed along with SQL and
I-Net. On the other 2 machines we've go SQL and I-Net.

We also have a number of Informix 4GL programs running. The problem
is that when a number of sqlexec processes are started the system
reports the following error messages if someone tries to start
another one:

        -932 : Error on network connection, t_open system call failed
          28 : No space left on device

The message appears on the client terminals, the server doesn't seem
to be getting upset about it. I don't know however, whether it's the
client or the server that's generating the error message.

Can anyone tell me why this is happening, what the device in question
is and whether I can do anything about it?

I'd really appreciate *any* help!
--
These opinions are mine -- let's face it who'd want them

Send me mail -- No reasons, just send it.

stuart@cix.compulink.co.uk            Tel +44 778 426444
[Stuart Hemming]


Stuart
|8-)
Newsgroups: comp.databases.informix
From: shemminga@cix.compulink.co.uk (Stuart Hemming)
Subject: Re:  Informix I-Net problem
Reply-To: shemminga@cix.compulink.co.uk
Date: Thu, 20 Aug 1992 09:39:00 +0000



In <9341@emory.mathcs.emory.edu> baskett@hermes1.sps.mot.com
(Bob Baskett) writes

> if you honestly think the support for informix products stinks, i suggest
> you try oracle.  then, i think you will fully appreciate the outstanding
> support that informix offers and the quality and consistency of products
> that informix brings to the market place.

I don't want anyone to think *I* said that! What I will say is that
I've found that dealers aren't as helpful as they might be. This
generally comes from lack of product knowledge on their part.

The problem seems to be that users aren't supposed to take to
Informix (or distributors) directly, at least not in the UK. Once the
problem gets progressed up the line that far thinks start to happen.
Unfortunately this can introduce a delay of up to a couple of weeks
8-(.


--------------------------------------------------------------
| These opinions are mine -- let's face it who'd want them   |
| Send me mail... Now send me some more                      |
|------------------------------------------------------------|     
| Stuart Hemming             | shemminga@cix.compulink.co.uk |
| Tudor Labels Ltd           |                               |
| Roman Bank                 | Tel :  (+44) 778 426444       |
| Bourne                     | Fax :  (+44) 778 421862       |
| Lincs PE10 9QL                                             |
| United Kingdom                                             |


Stuart
|8-)
From: widener!obelix.informix.com!johnl@emory.mathcs.emory.edu (Jonathan Leffler)
Newsgroups: comp.databases.informix
Subject: Re:  Unknown error 32766 occurs?
Date: 13 Aug 92 01:33:11 GMT
Reply-To: widener!obelix.informix.com!johnl@emory.mathcs.emory.edu (Jonathan Leffler)
X-Informix-List-ID: <list.1373>


INFORMIXDIR is not set and INFORMIXDIR is not /usr/informix?
If your system supports symbolic links, set /usr/informix as a symbolic 
link to the real installation.  Otherwise, ensure that INFORMIXDIR
gets set before running the command.  Have fun.


Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>
From: RUCHI%CFRVM.BITNET@VTVM2.CC.VT.EDU (Ruchi Patel)
Newsgroups: comp.databases.informix
Subject: Question - Window
Date: 21 Aug 92 13:28:04 GMT
Reply-To: RUCHI%CFRVM.BITNET@VTVM2.CC.VT.EDU (Ruchi Patel)
X-Informix-List-ID: <list.1388>


Hi! I am displaying the data in a window. INFORMIX-4GL allows you to scroll
page-up (using F3) and page-down (using F4 key). Is there anyway I can
change these keys so user can use the Page Up and Page Down keys instead?

I found a chapter which describes the CTRL keys but I can't seem to find
Page Up and Page Down key. Is there a any system varaible that lets you check
what key is pressed?

Thanks in advance.

Ruchi.

Ruchi Patel
Senior Programmer Analyst     Email: ruchi@cfrvm.cfr.usf.edu
Division of Telecommunications, OPM 128
Tampa, FL 33820-7549
From: cris@logic.dsg.ti.com (Cris Schramm)
Newsgroups: comp.databases.informix
Subject: DOS Informix - ISAM error number -24 (fwd)
Date: 17 Aug 92 19:03:06 GMT
Reply-To: cris@logic.dsg.ti.com (Cris Schramm)
X-Informix-List-ID: <list.1380>

> From: axcess@axcess.demon.co.uk (Axcess Systems)
> Subject: DOS Informix - ISAM error number -24
> Date: 17 Aug 92 15:43:07 GMT
> 
> Program error at cbak.4gl line 180
> SQL statement error number -229
> ISAM error number -24
> Could not open or create a temporary file.

Mark,
A new feature of Informix 5.0 is a utility that will give you text about
errors.  Here's what it has to say about -24:

	/tmp [335] finderr 24
	-24     Too many open files.

	An operating system error code with the meaning shown was unexpectedly
	returned to the database server. This error probably reflects a limit 
	configured in your operating system. Look for operating system error 
	messages that may give more information.

Regards,
Cris Schramm    Texas Instruments, Inc. 	     	#include <disclaimers>
                Internet                Cris.Schramm@hub.dsg.ti.com
		TI Telephone    	(512) 250-4424
		Fax Phone		(512) 250-6051
From: proberts@informix.com (Paul Roberts)
Newsgroups: comp.databases.informix
Subject: Re: conditional select sum() based on field question
Keywords: select sum
Date: 13 Aug 92 00:31:28 GMT
Organization: Informix Software, Inc.

In article <297@praeda.UUCP.UUCP> pete@praeda.UUCP.UUCP (Pete Frehner) writes:
>
>I have a question regarding selecting a sum from a table of amounts and
>depending upon the type of record, either adding or subtracting the amount
>from the sum.  We are using Informix 4.0 ISQL and R4GL.
>
>I could do this in two select statements and the difference would be the
>net amount, but this requires that the data be read twice (once for each
>select statment).  
>
>   select sum(amount) into total_credits
>       from orders where order_type = "CREDIT"
>
>   select sum(amount) into total_normal_orders 
>       from orders where order_type = "REG"
>
>   let net_amount = total_normal_orders - total_credits 
>
>
>Is there a way that this could be done in one select statment??  Two select
>statments is no big deal for a small database, but this database contains
>hundreds of thousands of orders.  If the data could be read only once it would
>pick up performance quite a bit.
>

The way I would choose to do it is: store the amounts
as negative numbers on "CREDIT" orders. i.e. treat
the credit memo as an order which happens to be for a
negative sum of money.

Then you can just sum() the lot and not worry about
distinguishing the two types.

If this is not an option (e.g. because the system is
already in place, and the dollar amounts on credit memos
are positive, even though Paul Roberts thinks they should
be negative), then there is this approach:

create a temp table called "fudge" and put exactly
two rows into it, like so:

temp table fudge:
-----------------

     char(6)       smallint 
    order_type     f_factor
    ----------     --------

      REG             1
      CREDIT         -1

and then do this select:

select sum(orders.amount * fudge.f_factor)
  from orders, fudge
 where fudge.order_type = orders.order_type

The idea is that the f_factor will change the sign on
the dollar amount to a negative value on the credit
memos, just as the gods intended.

Whether this will help performance is another question
though, since you do have to do the join (albeit to a
very small table) and the arithmetic.

Might be worth a try though.

Paul

p.s. Tell your friends: qty_shipped and dollar_amount
     should *both* be negative for credit memos! "price"
     stays +ve, though. Have I suffered because I worked
     on a system where this isn't the case? Yes, I have.
From: alfonso@mtecv2.mty.itesm.mx
Newsgroups: comp.databases.informix
Subject: Informix OnLine help needed
Date: 19 Aug 92 00:18:25 GMT
Reply-To: alfonso@mtecv2.mty.itesm.mx
Distribution: alt


  I'm using Informix OnLine 4 on an RS/6000 under AIX 3.1. I have only one
application running with the OnLine, and sometimes it generates this error message:

  Cannot red system catalog (systables)
  Cannot open file

  Why is this happening? This problem doesn't arise on the other machine
where I have OnLine.
  Thanks in advance.

  Alfonso Trevino
  ITESM
  alfonso@mtecv2
Newsgroups: comp.databases.informix
From: rdekker@do.raet.nl (R. Dekker)
Subject: Replaying Logical Logs
Date: Sun, 16 Aug 1992 17:45:33 GMT
Distribution: world
Organization: RAET Decentrale Overheid, The Netherlands


A customer of ours has a large Online 4.10 database (6 Gb !).
There is also a second database on an other machine, which is a copy
of the first machine's backup from the previous day.
The second database only handles queries, no updates.
Does anyone out there know if it is possible to replay the Logical
Logs from the update-database on the query-database, so that the
customer doesn't have to do the timeconsuming restore.
 
 
Roel Dekker                             RAET Decentrale Overheid
Internet : rdekker@do.raet.nl           Attn: Mr. R. Dekker
UUCP     : do.raet.nl!rdekker           P.O. Box 131
Phone    : +31 5220 56969               7940 AC  MEPPEL
					The Netherlands
From: dave.snyder@snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: Question - Window
Date: 21 Aug 92 20:13:06 GMT
Reply-To: dave.snyder@snide.com (Dave Snyder)
X-Informix-List-ID: <list.1391>

Quoting Ruchi Patel...
> 
> Hi! I am displaying the data in a window. INFORMIX-4GL allows you to scroll
> page-up (using F3) and page-down (using F4 key). Is there anyway I can
> change these keys so user can use the Page Up and Page Down keys instead?
> 
This is sick but it should work...

  1) Determine the escape sequences for your PageUp and PageDown keys.
     (on a SCO Unix console they are "\E[I" and "\E[G" respectivly)

  2) Tweek the informix termcap file so that it recognises these sequences.
     I added the following to the "ansi" entry...
       :kE=\E[I:kF=\E[G:  
     (this tells 4GL that F15 and F16 sequences are identical to the PAGE keys)

  3) In the 4GL code add the following two lines some place BEFORE array access.
       OPTIONS PREVIOUS KEY F15
       OPTIONS NEXT KEY F16

I picked F15 and F16 because the SCO Unix console only has 12 function keys.
These ideas have not been tested but I see no reason why they shouldn't work.
Comments? Suggestions? Flames?

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.7
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: dave.snyder@snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: How to clear a field in a multiple table form?
Date: 13 Aug 92 14:38:02 GMT
Reply-To: dave.snyder@snide.com (Dave Snyder)
X-Informix-List-ID: <list.1376>

Quoting Kim Tran...

->   I have a form with 2 tables:
->        - payment
->        - partner
->   
->                        Payment Screen
->    
->   Payment_EIN     [f000]
->   Partner TIN     [f001]      Partner Type [p1]
->   
->   Tables
->      payment
->      partner
->   
->   Attributes
->      f000 = payment_table.EIN;
->      f001 = partner_table.tin = payment_table.tin, queryclear;
->      p1   = partner_table.type, queryclear;
->   
->  When I query and hit escape, all the values appear in the appropriate
->  fields.  But when I hit query again, the partner type field is not cleared
->  although I use "queryclear" in the attribute section.  I tried:  before
->  edit, before add, before query and after edit, after add, after query but
->  it didn't work.

Quoting from the SQL manual... "Use the QUERYCLEAR attribute to clear a
joining field on the screen when you enter a Query operation."  The reason
QUERYCLEAR is not working on the Partner Type field is because it is not
a joining field.  As for the second problem about before/after query, this
is why I prefer 4GL.  Perform is limited to one active table at a time.
If your current active table is the "payment_table", you can't do squat
to the fields that are only in the "partner_table".

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.7
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: dhepner@cup.hp.com (Dan Hepner)
Subject: Re: distributed transactions
Date: Fri, 21 Aug 1992 21:20:30 GMT
Organization: Hewlett-Packard
X-Newsreader: Tin 1.1scd1 PL4

From: mao@triplerock.CS.Berkeley.EDU (Mike Olson)

>> > If a non-blocking commit protocol is used, any transaction started at
>> > a remote site which went down can be aborted, releasing its locks, and
>> > leaving the database in a transaction-consistent state.
>>
>> Could you site some references or examples for this statement?

>the basic strategy is to add phases to the 2-phase protocol and to use
>message broadcast, so every site sees every message.  see

Maybe you can explain how this apparent dilemma is addressed:

Time 1:  node n acknowledges prepare
Time 2:  node n notes an inability to communicate with anyone else, in 
	 particular any site capable of being the transaction coordinator
Time 3:  still no communications, patience exhausted at node n

Non-blocking protocols seem all to substitute some other problem for
the blocking problem.  Commonly, the above dilemma is addressed by 
defining it away, declining to acknowledge that a node might lose all 
communication.  Other writers have imagined it valuable to describe
protocols which did not block, but were not guaranteed to get the
right answer either.

On the other hand, the rarity of actual blockage makes extreme concern
not warranted for most real world systems.  The coordinator must go
away at precisely the right time, _and then never return_.  Surely some 
way needs to be offered to resolve the tie-up, but this issue does
not justify a fundamental complaint against distributed transactions.

Dan Hepner
From: umak@atar.epps.kodak.com (Uma Krishnan)
Newsgroups: comp.databases
Subject: Re: YACC/Lex parser for SQL and Soundex algorithm
Date: 19 Aug 92 21:17:56 GMT
Article-I.D.: APS.1992Aug19.211756.13895
Reply-To: umak@atar.Atex.Kodak.COM
Organization: Atex Publishing Systems, Inc.

I remember a long time ago, somebody had posted the soundex algorithm
to another net user. Could that kind soul please pass it on to me
please ?

Also we're looking for a YACC/lex parser for SQL. A public domain
or otherwise (as long as it is reasonably priced) is sought.


Thanks a million in advance.


Uma Krishnan

PS: I also think that this should be included in the FAQ (I do
not believe that we do maintain one. Any volunteers ? If none
and there is interest, then I'll do it.) 
Newsgroups: comp.databases