Gary -
I don't know if you noticed it, but you change the return columns since the
original CHOOSE statement you sent. In the first one, you were returning
the (CTXT(rrateid)) , which wouldn't work since you had multiple values
returned, and in the last one, you are returning ((CTXT(pplanid)) &
(CTXT(rtier))) which would work since the Tier will be unique.
I imagine you can return just (CTXT(rtier)) and it should work just fine.
Sami
----- Original Message -----
From: "Gary L. Winzeler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 6:37 PM
Subject: Re: Choose with distinct
> had no effect using extra ( )'s :-((
>
> Also Ron the temp table thing also had no effect.
>
> Data is real simple,
>
> Example of data:
> pplanid rrateid tier aareaid
> 1 1 2 1
> 1 2 3 1
> 1 3 3 2
> 1 4 3 3
>
> Example of what I want returned from above data: (distinct pplanid and
> tier #) independent of the area (I get the area from another lookup)
> pplanid rrateid tier
> 1 1 2
> 1 2 3
>
> seems like this should be easy but it is leaving me scratching my head why
> the distinct will not work.
>
> I did a work around by using the pplanid and the tier and SSub and it
works
> but ?????? not sure why the other does not work - would be easier if it
did.
>
> CHOOSE vxpplanid FROM #VALUES FOR DISTINCT +
> (LJS(pname,14) & LJS(ptosdesc,45) & LJS(ptype,8) & 'Tier' &
CTXT(rtier)), +
> ((CTXT(pplanid)) & (CTXT(rtier))) +
> FROM vwplans +
> WHERE rcarid = .vrcarid +
> AT 18,28 +
> TITLE 'Select a Plan (Esc when Done)' +
> CAPTION 'Plans' +
> LINES 19 FORMATTED
>
> SET VAR vssub1 = (SSUB(.vxpplanid,-1))
> SET VAR vssub2 = (SSUB(.vxpplanid,-2))
>
>
> at 01:55 PM 7/3/01 -0500, you wrote:
> >Gary -
> >
> >Try putting an extra pair of parens around the concatenated fields, like:
> >
> >CHOOSE vxrrateid FROM #VALUES FOR DISTINCT +
> > ((LJS(pname,14) & LJS(ptosdesc,45) & LJS(ptype,8) & 'Tier' &
> >CTXT(rtier))), +
> > (CTXT(rrateid)) +
> > FROM vwplans +
> > WHERE rcarid = .vrcarid +
> > AT 18,28 +
> > TITLE 'Select a Plan (Esc when Done)' +
> > CAPTION 'Plans' +
> > LINES 19 FORMATTED
> >
> >If that doesn't work, send sample data and the view definition.
> >
> >Sami
> >
> >Sami Aaron
> >Software Management Specialists
> >13214 W. 62nd Terr, #139
> >Shawnee KS 66216
> >913-915-1971
> >http://www.softwaremgmt.com
> >
> >----- Original Message -----
> >From: "Gary L. Winzeler" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Tuesday, July 03, 2001 12:48 PM
> >Subject: Re: Choose with distinct
> >
> >
> > > Yes, that is what I'm doing - just showing the descriptive data and
trying
> > > to return the id, but when you have the id in the return location it
> > > appears to use it in the distinct as well. (??)
> > >
> > > Am I doing something wrong here??
> > >
> > > GARY
> > >
> > > At 10:31 AM 7/3/01 +0100, you wrote:
> > > >Gary,
> > > >
> > > >I know it's obvious, but is it a requirement to show the ID... could
> > > >you just base your choose on the descriptive data and just return
> > > >the ID?
> > > >
> > > >Ben Petersen
> > > >
> > > >
> > > >On 3 Jul 2001, at 10:04, GARY Winzeler wrote:
> > > >
> > > > > I am trying to do a choose and limit the rows returned by from a
> > > > view. The
> > > > > view is a two table view from Plans (pplanid pri key) and Rates
> >(rrateid
> > > > > pri key).
> > > > >
> > > > > each plan has many rates and the rates have tiers which is in the
> >rates
> > > > table.
> > > > >
> > > > > CHOOSE vxrrateid FROM #VALUES FOR DISTINCT +
> > > > > (LJS(pname,14) & LJS(ptosdesc,45) & LJS(ptype,8) & 'Tier' &
> > > > CTXT(rtier)), +
> > > > > (CTXT(rrateid)) +
> > > > > FROM vwplans +
> > > > > WHERE rcarid = .vrcarid +
> > > > > AT 18,28 +
> > > > > TITLE 'Select a Plan (Esc when Done)' +
> > > > > CAPTION 'Plans' +
> > > > > LINES 19 FORMATTED
> > > > >
> > > > > as you can see I am trying to return the rrateid which is the
primary
> >key
> > > > > in the rate table, but with this in the choose it returns all
records
> >in
> > > > > the view because rrateid is unique. Is there a way to limit the
rows
> > > > > distinct for each plan and tier then return the rrateid?
> > > > >
> > > > >
> > > > > THANKS
> > > > > GARY
> > > > >
> > > > >
> > >
> > > Regards,
> > >
> > > Gary L. Winzeler
> > >
> > > DAQtech, Inc.
> > > Data Acquisition Technology
> > > <mailto:[EMAIL PROTECTED]>
> > > <http://www.daqtech.com/>
> > >
> > > Office 408-847-4800
> > > Fax 408-847-4097
> > > Cellular 408-483-7739
> > >
> > >
> > >
>
> Regards,
>
> Gary L. Winzeler
>
> DAQtech, Inc.
> Data Acquisition Technology
> <mailto:[EMAIL PROTECTED]>
> <http://www.daqtech.com/>
>
> Office 408-847-4800
> Fax 408-847-4097
> Cellular 408-483-7739
>
>
>
>
