Sami,
Yes, I did notice and as I noted (sorry for not being clearer) the change
is the work around. Was a little confused with your response (tier is not
unique). Both Choose commands work the problem is with the DISTINCT. It
appears that the Distinct uses whatever you put in the position of the
"retcol" and not distinct for what you have in the position for
"display". I was under the incorrect assumption that distinct would give
you distinct on what you were displaying but it appears to rather give you
distinct on your defined display as well as your "retcol" even if the
"retcol" is not being displayed. I have not tried this yet with 6.5++ win,
I'm still using 6.5+ win.
GARY
At 05:43 PM 7/4/01 -0500, you wrote:
>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
> >
> >
> >
> >
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
