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
