Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
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
 > 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 Peppler         -||-  Data Migrations Inc.    -||-
Int. Sybase User Group  -||-