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