PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: (Fwd) Mailing list or file server error
Date: Apr 17 1998 5:06PM

Beth Breidenbach writes:
 > Mr. Peppler,
 > 
 > I apologize for sending this to you directly.  I am subscribed to 
 > SYBPERL-L, and receive messages from the list.  However, 
 > when I attempt to post this message to SYBPERL-L@trln.lib.unc.edu
 > my message is rejected.  I have sent an email to the administrator 
 > re this problem.  In the meantime, would you mind a) forwarding this 
 > to the list and b) reviewing my sybperl difficulty?

Looks like your post went through after all....

 > 
 > Thank you for your time, Beth Breidenbach
 > 
 > ------- Forwarded Message Follows -------
 > 
 > 
 > Help!
 > I must be missing something obvious....but I can't find anything 
 > similar in the archive.  
 > 
 > The desired result from the query below is a single row, with 
 > MITD_ICD=1660 and the highest-valued MITD_PREFIX associated with 
 > the 1660 ICA  (prefixes are unique)
 > 
 > This sql code, when run directly in Sybase's isql, returns 
 > the desired single row.  However, when the sql is embedded into dbcmd 
 > as shown in this script, the entire table is returned.  The issue 
 > appears to be the use of 'max().  If I want all rows with ICA=1660, 
 > everything's fine, but adding the max criteria freaks out the script.
 > 
 > I've tried multiple variants of this syntax.  What am I missing???

A group by clause. Without the group by you get a cartesian product.

 > select max(a.MITD_PREFIX) as Prefix
 > 	, a.MITD_ICA as ICA
 > 	, b.MITD_NAME
 > from MC_Cross_Ref a,
 > 	MC_Acquiring_Contact b
 > where a.MITD_PREFIX in
 > 	(select max(MITD_PREFIX)
 > 	from MC_Cross_Ref 
 > 	where (MITD_ICA='1660' 
 > 		or MITD_PREFIX='1660')
 > 	)
 > 	and a.MITD_ICA=b.MITD_ICA
group by a.MITD_ICA, b.MITD_NAME

Hope this helps,

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@datamig.com    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com