Re: "Lost in Select"

[Cathlin DeRosa <cathlind@NSIGHTINC.COM>]

I'm thinking your email address field should be the unique field..  Afterall
we can't have the same email address,  but we certainly can have more then
one of those..

> -----Original Message-----
> From: Discussion of SQR, Brio Technology's database reporting language
> [mailto:SQR-USERS@list.iex.net]On Behalf Of Pragya Chandra
> Sent: Thursday, May 11, 2000 12:35 PM
> To: SQR-USERS@list.iex.net
> Subject: Re: "Lost in Select"
>
>
> what you can do...and I am not saying that it is the most efficient way of
> doing it is select distinct email addresses and then in the second select
> get the member name...I'll try to use the code you gave for an example:
>
>         select distinct a.email
>                do second-select
>         from member a, group_member b
>         where  a.member_id = b.member_id
>             and b.group_id     ='41'
>             and b.member_since = (select max(b1.member_since) from
> group_member b1 )<--you need to join the keys in the subselect
>
> begin-procedure second-select
>
> begin-select
> a1.member_name
> from member a1
> where a1.email = &a.email
>  and rownum = 1
> end-select
>
> end-procedure second-select
>
> rownum is what you use in Oracle to get one row of data only..(if you only
> want one name)...check for your own database
>
> > -----Original Message-----
> > From: Deborah DeCarvalho [SMTP:ddecarvalho@IBELONG.COM]
> > Sent: Thursday, May 11, 2000 2:27 PM
> > To:   SQR-USERS@list.iex.net
> > Subject:      "Lost in Select"
> >
> > Afternoon all -
> > I seem to be having a problem getting a very minor select to work and I
> > have
> > no idea what I'm  missing - Now, I have been staring at this
> for well over
> > 3
> > hours so I'm thinking I am either missing something so dumb
> that I'll end
> > up
> > looking like an a**  or I'll leave immediately on vacation, but here
> > goes..
> >
> > I have 2 tables I need to query for a sweepstakes promotion. ( I said it
> > was
> > minor !)
> > Since we have multiple sweepstakes on the web portals, I need my SQR to
> > select based on the group and on the date they registered on
> the portal -
> >
> > I'm all set with the SQR prompting for the group and the dates,
> but here's
> > the problem, our code produces a "member_id" when a person registers on
> > the
> > portal - They MUST use a unique member_name when registering otherwise,
> > they're told to try again - (member_name is a key field)
> > But, you can register as many times as you WANT as long as you use a
> > different member_name each time. Thats where my problem starts - I have
> > multiple people with the same email address but different user names - I
> > only want unique email names.
> > I was using this
> >
> > select distinct(a.First_Name||','||a.Last_name||','||a.email)
> from member
> > a,
> > group_member b
> >     where a.member_id = b.member_id
> >     and b.group_id='41'
> >     and a.email not like '%ibelong%'
> >
> > But that doesn't work - I get duplicate email addresses
> > I also tried
> >
> >      select a.email,a.member_name, b.member_since from member a,
> > group_member b where
> >      a.member_id=b.member_id
> >      and b.group_id='41' and
> >      b.member_since = (select max(b.member_since) from group_member b )
> >
> > This HAS to be sooo simple, that I'll take my SQR manuals and throw them
> > out
> > the window right?
> > Any suggestions??? (BTW, the group number is there for testing
> - it isn't
> > actually in the select - There is a prompt for that)
> >
> > TIA
> >
> > Deborah deCarvalho
> > Operations/Technology
> > IBelong Networks
> > www.ibelong.com
> > 781.672.8349
> > mailto:ddecarvalho@ibelong.com
>