HDTenEightyP/NetNews huggingface dataset:
From: pete@praeda.UUCP.UUCP (Pete Frehner)
Newsgroups: comp.databases.informix
Subject: conditional select sum() based on field question
Keywords: select sum
Date: 12 Aug 92 15:55:47 GMT
Organization: Praeda Management Systems, Inc.


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.

============================================================================
Pete Frehner				 Voice (519) 673-3350
Praeda Management Systems                Fax   (519) 667-1968
371 Dufferin Ave.                        uucp: .....uunet!praeda!pfrehner
London, Ontario CANADA N6B 1Z5
From: Doimic01@saad-emh1.ARMY.MIL
Newsgroups: comp.databases.informix
Subject: Re: lookup joins in perform
Date: 12 Aug 92 21:28:03 GMT
Reply-To: Doimic01@saad-emh1.ARMY.MIL
X-Informix-List-ID: <list.1371>

In Informix-List <news.1650>, Alan K Biocca writes:

>I have tables that join on multiple columns, and would like to do lookups
>in perform to display info from the other table when entering data.  Perform
>doesn't appear able to handle more than one column joins.

Please excuse my language, but I believe this comes under the heading of RTFM.
Specifically, check the Formbuild INSTRUCTIONS Section in the manual titled
"COMPOSITES".  A composite index is an index based on multiple columns.

Todd Glidden     doimic01@saad-emh1.army.mil    Standard Disclaimers Apply
From: Doimic01@saad-emh1.ARMY.MIL
Newsgroups: comp.databases.informix
Subject: Shell Archive
Date: 12 Aug 92 21:58:07 GMT
Reply-To: Doimic01@saad-emh1.ARMY.MIL
X-Informix-List-ID: <list.1372>

Several of you have used something called "shell archive" (shar)
to send code through e-mail.  We currently have a requirement to
send sql command files, 4gl code, and per screens through e-mail.
Is shar a share-ware routine, or did you purchase it commercially,
or was it included with your version of Unix?  We really don't want
to create our own shell archive if it already exists.  Tanks!

Todd Glidden     CSC Inc.    Sacramento Army Depot    Standard Disclaimers Apply

"There is a new Operating System called DOS/Perot.  When you boot it up, it
prints a message saying it is thinking of running.  Then it checks to see if
there are any competing operating systems.  If so, it quits immediately."
 - INFO WORLD
From: dave.snyder@snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: Informix Support Problem
Date: 22 Aug 92 12:43:08 GMT
Reply-To: dave.snyder@snide.com (Dave Snyder)
X-Informix-List-ID: <list.1393>

Quoting Sima Hashemifar...
> 
> Who cares? You could be dead for all I care. I am complaining that I have
> a $2000.00 dollar program that hangs up my machine and Informix is not
> doing much about it.
> 
Just my $.02...  GET A REAL MACHINE!  What do you expect from MESS DOS?  ;-)

(Boy this should really start a flame war :-)

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.7
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: zill@hermes1.sps.mot.com (Mike Zill)
Newsgroups: comp.databases.informix
Subject: Re:  Informix I-Net problem
Date: 20 Aug 92 16:28:05 GMT
Article-I.D.: emory.9351
Reply-To: zill@hermes1.sps.mot.com (Mike Zill)
X-Informix-List-ID: <list.1384>

I would raise this UK support issue to the top of INFORMIX asap.  If this
is common in the UK, the top guys at INFORMIX need to know.  
Fix problems at the root and problems they are no more.

Mike
From: "clay irving" <clay.irving@execnet.com>
Newsgroups: comp.databases.informix
Subject: porting a database to ano
Date: 16 Aug 92 09:50:41 EST
Reply-To: "clay irving" <clay.irving@execnet.com>
Distribution: comp
Organization: Executive Networks Information

-=> Quoting Steve Wallick to All on 08-12-92 <=-

->What is the best way to move a DATABASE from one machine to
->another?

This is probably a RTFM question, but I've asked the same question 
before... I use DBEXPORT to export the db schema and the data.  DBEXPORT 
and go to disk or tape.  On the other system, use DBIMPORT to create and 
load the database.

---
  WinQwk 2.0 a#480  Take my advice...I'm not using it.
--
Executive Network Information System  (914) 667-4567
International ILink Host
From: maint72@saad-emh1.ARMY.MIL (Michael Gordon)
Newsgroups: comp.databases.informix
Subject: SQL Ace Report Writer.
Date: 22 Aug 92 23:33:09 GMT
Reply-To: maint72@saad-emh1.ARMY.MIL (Michael Gordon)
X-Informix-List-ID: <list.1394>


Good Morning!

I have discovered a problem with Informix SQL v 2.10 Ace Report Writer.

I have SQL installed in a stand alone PC, and also on an Intel-310 
multi-user box.

The problem is with Ace on the PC, in the OUTPUT block of the source code
the REPORT TO instruction works well in sending the formated text/data to
a "file" or to the PRINTER.  However, the reference manual states that a
key word "PROGRAM" will send the output to a SYSTEM COMMAND.  This should
work like the Unix " REPORT TO PIPE 'cat > /usr2/login/subdirectory/filename".

THE PROGRAM KEY WORD DOSE NOT WORK AT ALL, NOT IN ANY CONFIGURATION I CAN
COME UP WITH.

Has anyone on the Net tried to use this PROGRAM keyword to successfully sen
the output of a report to a MS-DOS system command, .exe, .com, .bat?

I would appreciate any valuable suggestions along these lines.

Thanks for even reading this.






					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: coburn@informix.com (David Coburn)
Newsgroups: comp.databases.informix
Subject: Re:  Informix I-Net problem
Date: 20 Aug 92 12:41:28 GMT
Organization: Informix Software, Inc.

In article <1992Aug19.090540.17209@u.washington.edu> golbone@milton.u.washington.edu (Sima Hashemifar) writes:
>Organization: University of Washington, Seattle
>
>Meanwhile, the campaign has started:
>Please do not buy infromix products, their tech support stinks.
>The support policy will kill your business!!

Sima,

I find it interesting that there is no entry in our database of your
calling this problem in.  I searched on your last name, first name, U of W,
even for all calls since July where the phone number began with "206" and
the last name matches 'H*' and got no results.

The reason for my concern is that I work out of the Seattle office and
generally try to take care of the folks in that area.  If you have a
problem, please email me your case number and/or the problem or bug number
you were given and I will see if we can get your problem resolved.  (I
really do recommend email; I am on an extended assignment out of the office
and calling the Bellevue office could result in a several day delay in
getting in touch.)

In the meantime, please re-read your own comment.  The engineer you spoke
with obviously cared enough to work on the problem from his own home; as a
former technical support engineer, I can tell you that the very *LAST*
thing one wants to do after work is try and debug problems at home!

Thank you.


David Coburn                                 coburn@informix.com
Client Services Engineer                   ...uunet!infmx!coburn
Informix Software, Inc.
=========================================================================
Any opinions expressed here are not necessarily those of Informix 
(or for that matter, anybody else).
=========================================================================
Newsgroups: comp.databases.informix
From: cse@wetware.com (Scott Ellard)
Subject: Re: conditional select sum() based on field question
Keywords: select sum
Organization: Castle Wetware
Date: Sat, 15 Aug 1992 18:56:41 GMT

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).  
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This implies that the cardinality/selectivity of "order_type" is such
that "order_type" is not indexed.

>
>
>   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.

Try this:

    create table foo
    (
      order_type char(6),
      bar        integer
    );
    insert into foo values("CREDIT", -1);
    insert into foo values("REG",     1);

    select sum(O.amount * F.bar)
    from orders O, foo F
    where O.order_type = F.order_type
    and   O.amount is not null;

Note: foo could be an existing permanent
    table or it could be created/dropped
    on the fly.
From: baskett@hermes1.sps.mot.com (Bob Baskett)
Newsgroups: comp.databases.informix
Subject: Re:  Informix I-Net problem
Date: 19 Aug 92 16:38:03 GMT
Reply-To: baskett@hermes1.sps.mot.com (Bob Baskett)
X-Informix-List-ID: <list.1382>

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 have dealt with progress, oracle, and informix - both sales and support
personell.  informix bets the others hands down.

----------------------------------------------------------------------------------------
Bob Baskett
Motorola, Inc.
From: warren@hector.cc.bellcore.com (saccente,warren j)
Newsgroups: comp.databases.informix
Subject: Online 4.10 Dbexport/Dbimport
Date: 21 Aug 92 15:23:13 GMT
Reply-To: warren@hector.cc.bellcore.com (saccente,warren j)
X-Informix-List-ID: <list.1389>

Paul Mahoney writes: 
 
> I need to move databases about. Unfortunately dbexport does not
> record table extent/next extent sizes or the lock mode when it
> creates the sql necessary to recreate the exported database.
>  
> Has anyone got tools which do it right? When I asked Informix they
> said no.
 
We are struggling with the same issues right now.  I've been working on 
an awk script to changes the DBNAME.sql file such that it will put in the lock 
modes and extent sizes and change the file so that it will work with isql 
rather than dbimport.  I haven't spoken with Informix about this yet - but I 
suspect that they would consider the extent size issue as a feature rather 
than a flaw - and under most circumstances they are right.  dbimport will     
figure out an extent size such that all data to be loaded will fit into the 
first extent.  This is usually good - two cases where it is not good is 
(1) if you don't have enough available contiguous dbspace to put the entire 
table in one extent and (2) if you want the extent size to be less than 16K 
(we create our tables as 8K extents because we have many very small tables -
our databases almost double in size when we export and import them). 

Does anyone know if either of these situation exist post-4.0 or are
these flaws/features fixed?

Thanks,

Warren J. Saccente
Bellcore
From: rjsparks@lstar31.tamu.edu (Robert Sparks)
Newsgroups: comp.databases.informix
Subject: group projects in mode ANSI
Date: 13 Aug 92 19:41:00 GMT
Organization: loanstar

I would like comments on the "right" way to deal with the following:

We are using informix to manage a medium sized time-series database
(right now it is 800 points hourly ). The best model we have found
for this is one table per point (timestamp,value). We have analysis,
QA, and reporting routines that munge these reports together. We chose
to use mode ANSI for implicit transactions and better default access
security.

Now, the "ownername". restrictions of mode ANSI are biting us. To keep the
programming bearable, we have chosen a uniform "owner" for the tables that
the routines will touch. The problem is that sometimes these routines need
to create a table (If a QA routine finds some bad data it needs to pump it
out to a bad_table for that channel - if the table doesnt exist it will
create it.) Creating that table with the mother approved "ownername" is
turning out to be painful.

This strikes me as a common scenario - what is the usual way of working with it?

RjS
rjsparks@loanstar.tamu.edu
From: dave.snyder@snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: can not sort rows
Date: 23 Aug 92 00:13:07 GMT
Reply-To: dave.snyder@snide.com (Dave Snyder)
X-Informix-List-ID: <list.1395>

Quoting clay irving...
> 
> We've written a I-4GL program to process an order table contain 17,000 
> rows. The table is being joined to several other tables.  There is a good 
> amount of substring manipulation and temporary tables.  We receive a 
> message "Can not sort rows" -- "System error -5".  An Informix SE 
> indicated that temporary tables are being built in rootdbs, regardless of 
> the dbspace the working database is in.  I added a 100MB chunk to rootdbs 
> and a 150MB chunk to the working dbspace, but I still get the same error 
> message. I hate to keep throwing disk space at the problem until it 
> <hopefully> goes away -- Does anyone have an idea what's going on and a 
> possible solution?
> 
Wow, I knew this could happen but I've never heard of it until now.  First
of all, ALL tmp tables are stored in the rootdbs.  Now for the answer to
your problem...

An online table can have a maximum of ~200 extents.  The minimum size of
an extent is 16K.  Now quick math will say, "DAMN! A table can only be
3.2 meg."  That's not correct thinking though.  Every so many extents,
online doubles the size of the extent.  For example:
    Extent #     Size
    --------     ----
        1         16K
       64         32K
      128         64K
      192        128K
      etc.       etc.

Now I forget the exact numbers but you get the idea.  Anyway, when a tempory
table is created by a SELECT statement in online, it gets the default extent
size and the default next_extent size.  This will eventually give a limit
on the maximum size of a table... approx. 8 megabytes.  Although I can't
remember the above numbers, that 8meg sticks in my mind because we did
the calculations in Online Administrator's class.  This is my guess at
what your problem is based on what you said.  My suggestion is to create
the temp tables yourself (with appropriate extent sizes), select into them,
and then drop them when you are finished.

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.7
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: widener!obelix.informix.com!johnl@emory.mathcs.emory.edu (Jonathan Leffler)
Newsgroups: comp.databases.informix
Subject: Re:  Shell Archive
Date: 14 Aug 92 00:13:09 GMT
Reply-To: widener!obelix.informix.com!johnl@emory.mathcs.emory.edu (Jonathan Leffler)
X-Informix-List-ID: <list.1379>

I attach a shell archive of the shell archiver I usually use.
There are also C programs which do the same job somewhat quicker,
but they are also bigger to send.

Note the customisation for the sending organsiation -- you'll want
to change that (please!).

I hope it's useful.
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>

:	"@(#)shar.sh	1.8"
#! /bin/sh
#
#	This is a shell archive.
#	Remove everything above this line and run sh on the resulting file.
#	If this archive is complete, you will see this message at the end:
#	"All files extracted"
#
#	Created: Thu Aug 13 23:05:56 GMT 1992 by johnl at Informix Software Ltd.
#	Files archived in this archive:
#	shar
#
#--------------------
if [ -f shar -a "$1" != "-c" ]
then echo shar: shar already exists
else
echo 'x - shar (Aug characters)'
sed -e 's/^X//' >shar <<'SHAR-EOF'
X
X:	"@(#)shar.sh	1.8"
X#
X#	Create shell archive from files listed in argument list
X
Xcase "$1" in
X-s)	silent=yes
X	shift
X	;;
X--)	shift
X	;;
X-*)	echo "Usage: $0 [-s] file ..." >&2
X	exit 1
X	;;
Xesac
X
Xcase $# in
X0)	echo "Usage: $0 [-s]  file ..." >&2
X	exit 1
X	;;
Xesac
X
Xcat <<!
X:	"@(#)shar.sh	1.8"
X#! /bin/sh
X#
X#	This is a shell archive.
X#	Remove everything above this line and run sh on the resulting file.
X#	If this archive is complete, you will see this message at the end:
X#	"All files extracted"
X#
X#	Created: `date` by $LOGNAME at Informix Software Ltd.
X#	Files archived in this archive:
X!
X
Xfor file in "$@"
Xdo
X	if [ -f $file -a -r $file ]
X	then
X		echo "#	$file"
X	fi
Xdone
Xecho "#"
X
Xfor file in "$@"
Xdo
X	if [ ! -f $file ]
X	then
X		echo "$0: $file is not a file" >&2
X		continue
X	fi
X	bname=`basename $file`
X	if [ "$silent" != yes ]
X	then
X		echo "a - $bname" >&2
X	fi
X	if [ ! -r $file ]
X	then
X		echo "$0: Can't read $file" >&2
X		exit 1
X	else
X		size=`ls -l $file | sed 's/[ 	][ 	]*/	/g' | cut -f5`
X		echo "#--------------------"
X		echo "if [ -f $bname -a \"\$1\" != \"-c\" ]"
X		echo "then echo shar: $bname already exists"
X		echo "else"
X		echo "echo 'x - $bname ($size characters)'"
X		echo "sed -e 's/^X//' >$bname <<'SHAR-EOF'"
X		sed -e 's/^/X/' $file
X		echo "SHAR-EOF"
X		echo "if [ \`wc -c <$bname\` -ne $size ]"
X		echo "then echo shar: $bname unpacked with wrong size"
X		echo "fi"
X		echo "# end of overwriting check"
X		echo "fi"
X	fi
Xdone
Xecho "echo All files extracted"
Xecho "exit 0"
SHAR-EOF
if [ `wc -c <shar` -ne Aug ]
then echo shar: shar unpacked with wrong size
fi
# end of overwriting check
fi
echo All files extracted
exit 0
Newsgroups: comp.databases.informix
From: axcess@axcess.demon.co.uk (Axcess Systems)
Subject: DOS Informix - ISAM error number -24
Distribution: world
Organization: Axcess Systems
Date: Mon, 17 Aug 1992 15:43:07 +0000

I am currently in the process of porting an existing system from SCO XENIX
to DOS under Informix 4GL V3.61.  I have hit upon a problem where I
receive the following error message :-

Program error at cbak.4gl line 180
SQL statement error number -229
ISAM error number -24
Could not open or create a temporary file.

The line it stops at merely reads

OPEN cu_due

where 'cu_due' is a previously defined cursor.

The problem occurs *ONLY* if I have previously issued a 'RUN' command to
call a DOS batch file.  The batch file is used to call WordPerfect 5.1, 
although calling a batch file that does nothing will then proceed to
cause the above error.  The problem does not occur under XENIX.

I have read the manual, error number -229 tells me to look at the ISAM
error, but I cannot find any trace of error -24!

Can any one please help!

Mark.
From: cortesi@informix.com (David Cortesi)
Newsgroups: comp.databases.informix
Subject: Re: conditional select sum() based on field question
Date: 14 Aug 92 19:39:31 GMT
Reply-To: cortesi@informix.com
Organization: Informix Software, Inc.

In article <9305@emory.mathcs.emory.edu> A.Heskett@bnr.co.uk (Tony Heskett)  
writes:
> 
> Pete Frehner writes:
> 
> > [ ... add or subtract according to record type, only scan table once ... ]
>
> Call me stupid (go on, I'm waiting  :-)  but why don't you just
> do this ?    Is a cursor *much* slower ?
> 
[fetch-loop to scan table adding and subtracting amounts, omitted]

You ain't stupid (at least on present evidence :-) but a cursor
ought to be slower for this problem.  Reason: your cursor
scan requires passing every row from the engine to the program.
Each trip back and forth between engine and application costs
time.  Any solution that has the engine calculate the sum should
be faster because less stuff is shoved through the pipes.
Newsgroups: comp.databases.informix
From: sandie@mprgate.mpr.ca (Bert Sandie)
Subject: ESQL/C Program - Get First, Get Next Record in Table
Originator: sandie@atom
Keywords:  ESQL/C, Cursors, Select, First, Next
Organization: MPR Teltech Ltd., Burnaby, B.C., Canada
Date: Thu, 13 Aug 92 22:34:05 GMT

Hi Netters,

I was wondering if anyone can help me out with what I think is
a problem with using ESQL/C to perform a get first and get next
operation from a C program.

The program has two routines call Get_First and Get_Next which
are used to retrieve records from a table based on a specified
selection criteria.  The Get_First and Get_Next routines are
used in many other routines to retrieve records based on a
specified selection criteria.  The problem is if I have a
parent function as such:

FUNCTION A
Begin

   table_name = 'FOOBAR'
   selection_criteria = 'A*'

   Get_First(table_name, selection_criteria, return_record)

   While ( return_record.field1 != xx) Do
   Begin

     Get_Next(table_name, selection_criteria, return_record)

   End
End

FUNCTION Get_First
BEGIN

    Use Cursor to retrieve records which match selection_criteria

    Return First Record in cursor 

END
    
FUNCTION Get_Next
BEGIN

   Use Cursor to retrieve records which match selection_criteria
      
   Return Next record in cursor /* <=== PROBLEM */ 

END

The PROBLEM is how do you know which is the next record, after the
first record is retrieved, or for subsequent records for that matter?

Any advice would be appreciated,

Thanks,

Bert Sandie
MPR TELTECH LIMITED
Burnaby, BC, Canada
EMAIL: sandie@mprgate.mpr.ca