RE: [U2] selection of multivalued field based on range
>>WHEN only works with BY.EXP and is not associated with WITH . You can have >>bunches of WITH's, but only the first WHEN gets evaluated. At least with >>the versions I've worked with. >>And that's on Unidata! Thanks Allen. I have always thought that WHEN was a synonym for WITH and used WITH. I have learned something today. I'm going home now. My limit is one thing a day. Charles Shaffer Senior Analyst NTN-Bower Corporation --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Guys To return to my earlier post: WHEN is an output limiter. It limits the output of values, so it will work if you want to LIST those records by suppressing the output of the individual values that don't match. But it won't work in a SELECT in this case, unless you are already exploding, since the select will still vector each half of the test independently. Sadly, RetrieVe doesn't support BETWEEN - but SQL does. And it can generate a select list, so you can use that. SELECT @ID TO SLIST 0 WHERE (QTY BETWEEN 1000 AND 2000); >> nn records selected (Why does nobody ever use the SQL in UniVerse? It's not like using it in Oracle you know...) Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Maybe it works better in 7.1.9. I can remember getting odd results before with MV's and with's. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bill Haskett Sent: Wednesday, December 19, 2007 17:13 To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range Allen: In UniDate v7.1.9 on Windows 2K3, in "ECLTYPE P" mode (using UniData mode), I do the following: 3 Dev (0)-> list APOPEN WHEN AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS list APOPEN WHEN AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS 17:04:23 Dec 19 2007 1 APOPEN INV-DATE DESCRIPTION ACCT. ACCT/AMTS 830*38953 02-21-07 WTR/SWR, 108942-515683 3070 76.01 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
It's been a super long time since I used WHEN, but now that you mention it, I remember doing that a long long time ago in a galaxy far far away :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Larry Hiscock Sent: Wednesday, December 19, 2007 16:42 To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range Actually, WHEN works for a normal list as well. If you have multiple associated multi-valued fields, you can restrict which multivalues display using WHEN. For example: LIST ORDERS ITEM QTY PRICE WHEN QTY GT 10 Assuming ITEM, QTY and PRICE are associated, the result will only show those lines whose quantity is greater than 10. To answer Russ's original question: I don't think UniVerse supports BETWEEN, but the following may work (it works in UniData): SELECT FILE WITH ANY QTY >= 1000 AND WITH ANY QTY <= 2000 The ANY keyword examines each value independently. If you use WITH QTY >= ... Then the entire QTY attribute (which may look like 2500}2500}300) is evaluated. HTH Larry Hiscock Western Computer Services --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Allen: In UniDate v7.1.9 on Windows 2K3, in "ECLTYPE P" mode (using UniData mode), I do the following: 3 Dev (0)-> list APOPEN WHEN AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS list APOPEN WHEN AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS 17:04:23 Dec 19 2007 1 APOPEN INV-DATE DESCRIPTION ACCT. ACCT/AMTS 830*38953 02-21-07 WTR/SWR, 108942-515683 3070 76.01 3 Dev (0)-> list APOPEN WITH AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS list APOPEN WITH AMTS BETWEEN "7500""8000" INVDATE COMMENT ACCTS AMTS 16:52:49 Dec 19 2007 1 APOPEN INV-DATE DESCRIPTION ACCT. ACCT/AMTS 830*38953 02-21-07 WTR/SWR, 108942-515683 3060 90.98 3070 76.01 ...which indicates I don't need the "BY.EXP" keyword (note the "list" command). However, in Pick mode I can: 3 Dev (0)>> LIST APOPEN BY-EXP AMTS > "7500" AND < "8000" INVDATE COMMENT ACCTS AMTS LIST APOPEN BY-EXP AMTS > "7500" AND < "8000" INVDATE COMMENT ACCTS AMTS 17:07:16 Dec 19 2007 1 APOPEN INV-DATE DESCRIPTION ACCT. ACCT/AMTS 830*38953 02-21-07 WTR/SWR, 108942-515683 3070 76.01 ...which does the same thing. Bill >-Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of Allen >E. Elwood >Sent: Wednesday, December 19, 2007 3:47 PM >To: u2-users@listserver.u2ug.org >Subject: RE: [U2] selection of multivalued field based on range > >WHEN only works with BY.EXP and is not associated with WITH . You can have >bunches of WITH's, but only the first WHEN gets evaluated. At least with >the versions I've worked with. > >And that's on Unidata! > >-----Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] Behalf Of >[EMAIL PROTECTED] >Sent: Wednesday, December 19, 2007 12:39 >To: u2-users@listserver.u2ug.org >Subject: RE: [U2] selection of multivalued field based on range > > >Allen, > >I am from a Unidata background. I am guessing that you work >in Universe? >I am curious about how Universe works. > >>> as only one WHEN per statement works. > >Does that mean you can only use one WHEN verb in a SELECT statement? > >In Unidata you can use multiple WITH (OR WHEN) verbs in a statement. >Example. > >SELECT WIP-BATCH-DETAIL-HIST_MA WITH WORK.DATE >= "10/1/07" AND WITH >WORK.DATE <= "10/31/07" > >I believe it was the same in the old PICK days. Anyone >remember Ultimate >PICK? It was OK, but it didn't live up to its name. > >Charles Shaffer >Senior Analyst >NTN-Bower Corporation >--- >u2-users mailing list >u2-users@listserver.u2ug.org >To unsubscribe please visit http://listserver.u2ug.org/ >--- >u2-users mailing list >u2-users@listserver.u2ug.org >To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Actually, WHEN works for a normal list as well. If you have multiple associated multi-valued fields, you can restrict which multivalues display using WHEN. For example: LIST ORDERS ITEM QTY PRICE WHEN QTY GT 10 Assuming ITEM, QTY and PRICE are associated, the result will only show those lines whose quantity is greater than 10. To answer Russ's original question: I don't think UniVerse supports BETWEEN, but the following may work (it works in UniData): SELECT FILE WITH ANY QTY >= 1000 AND WITH ANY QTY <= 2000 The ANY keyword examines each value independently. If you use WITH QTY >= ... Then the entire QTY attribute (which may look like 2500}2500}300) is evaluated. HTH Larry Hiscock Western Computer Services -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Allen E. Elwood Sent: Wednesday, December 19, 2007 3:47 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range WHEN only works with BY.EXP and is not associated with WITH . You can have bunches of WITH's, but only the first WHEN gets evaluated. At least with the versions I've worked with. And that's on Unidata! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, December 19, 2007 12:39 To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range Allen, I am from a Unidata background. I am guessing that you work in Universe? I am curious about how Universe works. >> as only one WHEN per statement works. Does that mean you can only use one WHEN verb in a SELECT statement? In Unidata you can use multiple WITH (OR WHEN) verbs in a statement. Example. SELECT WIP-BATCH-DETAIL-HIST_MA WITH WORK.DATE >= "10/1/07" AND WITH WORK.DATE <= "10/31/07" I believe it was the same in the old PICK days. Anyone remember Ultimate PICK? It was OK, but it didn't live up to its name. Charles Shaffer Senior Analyst NTN-Bower Corporation --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
WHEN only works with BY.EXP and is not associated with WITH . You can have bunches of WITH's, but only the first WHEN gets evaluated. At least with the versions I've worked with. And that's on Unidata! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, December 19, 2007 12:39 To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range Allen, I am from a Unidata background. I am guessing that you work in Universe? I am curious about how Universe works. >> as only one WHEN per statement works. Does that mean you can only use one WHEN verb in a SELECT statement? In Unidata you can use multiple WITH (OR WHEN) verbs in a statement. Example. SELECT WIP-BATCH-DETAIL-HIST_MA WITH WORK.DATE >= "10/1/07" AND WITH WORK.DATE <= "10/31/07" I believe it was the same in the old PICK days. Anyone remember Ultimate PICK? It was OK, but it didn't live up to its name. Charles Shaffer Senior Analyst NTN-Bower Corporation --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] selection of multivalued field based on range
How about: select myfile with every qty ge "1000" and le "2000" works for me on UV 10.1.4. Note that in D3, I think your original query would work as you had hoped. /Scott Ballinger Pareto Corporation Edmonds WA USA 206 713 6006 On Dec 19, 2007 7:37 AM, Russ Watson <[EMAIL PROTECTED]> wrote: > I want to select records to report if *one* of the multivalues falls > within a range. > > For example: select with qty >= 1000 and =< 2000 > > If I use the example I get records where none of the values satisfies > the range condition. > > @ID qty > --- --- > 11292,500.0 >2,500.0 > 300.0 > > > Russ > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
You wouldn't get all records, just those that contained one or more quantity >= 1000 and also one or more quantity <= 2000. If the phase "with qty >= 1000 and =< 2000" is actually interpreted as "with qty >= 1000 OR =< 2000" when a multi-valued field is evaluated, then I would have expected to get all records - which does not happen. Russ --- --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Universe RELLEVEL 001 X 002 10.1.4 003 PICK 004 PICK.FORMAT 005 10.1.4 Russ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, December 19, 2007 3:48 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] selection of multivalued field based on range Are we talking UV or UD here? Bruce M Neylon Health Care Management Group --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Allen, I am from a Unidata background. I am guessing that you work in Universe? I am curious about how Universe works. >> as only one WHEN per statement works. Does that mean you can only use one WHEN verb in a SELECT statement? In Unidata you can use multiple WITH (OR WHEN) verbs in a statement. Example. SELECT WIP-BATCH-DETAIL-HIST_MA WITH WORK.DATE >= "10/1/07" AND WITH WORK.DATE <= "10/31/07" I believe it was the same in the old PICK days. Anyone remember Ultimate PICK? It was OK, but it didn't live up to its name. Charles Shaffer Senior Analyst NTN-Bower Corporation --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Thanks to all who replied. The quandary was how to *select* the appropriate records without having to change the ensuing basic report. I was hoping for something like BETWEEN. But between does not work on multi-valued fields. By-exp would have required additional programming changes. In the end it was easier to do the quantity check in basic and discard the exceptions. If the phase "with qty >= 1000 and =< 2000" is actually interpreted as "with qty >= 1000 OR =< 2000" when a multi-valued field is evaluated, then I would have expected to get all records - which does not happen. Russ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Allen E. Elwood Sent: Wednesday, December 19, 2007 2:01 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] selection of multivalued field based on range Hi Russ, To get a listing of just the data you want you'll probably need to use the BETWEEN key word along with BY.EXP and WHEN Something along the lines of: LIST filename BY.EXP fieldname WHEN fieldname BETWEEN "1000" "2000" I'm not sure of the BETWEEN syntax so you'll need to look that up. But you need to use BETWEEN, as only one WHEN per statement works. hth, Allen E. Elwood www.tortillafc.com Quality Code Since 1978 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Russ Watson Sent: Wednesday, December 19, 2007 07:37 To: u2-users@listserver.u2ug.org Subject: [U2] selection of multivalued field based on range I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] selection of multivalued field based on range
Are we talking UV or UD here? Bruce M Neylon Health Care Management Group "Russ Watson" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/19/2007 10:37 AM Please respond to u2-users@listserver.u2ug.org To cc Subject [U2] selection of multivalued field based on range I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] selection of multivalued field based on range
How about SELECT WITH QTY >= 1000 AND WITH QTY <= 2000? Charles Shaffer Senior Analyst NTN-Bower Corporation "Russ Watson" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/19/2007 09:37 AM Please respond to u2-users To: cc: Subject: [U2] selection of multivalued field based on range I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ This email was Anti Virus checked by Astaro Security Gateway. http://www.astaro.com --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Hi Russ, To get a listing of just the data you want you'll probably need to use the BETWEEN key word along with BY.EXP and WHEN Something along the lines of: LIST filename BY.EXP fieldname WHEN fieldname BETWEEN "1000" "2000" I'm not sure of the BETWEEN syntax so you'll need to look that up. But you need to use BETWEEN, as only one WHEN per statement works. hth, Allen E. Elwood www.tortillafc.com Quality Code Since 1978 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Russ Watson Sent: Wednesday, December 19, 2007 07:37 To: u2-users@listserver.u2ug.org Subject: [U2] selection of multivalued field based on range I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] selection of multivalued field based on range
Russ, I use this form: >select file by-exp qty >= "1000" and <= "2000" Antonio Carlos Departamento TC)cnico Integral Sistemas Telefone 11 3205-6000 - Fax 11 3205-6001 www.integral.com.br __ GPG signed/encrypted mail welcome. OpenPGP ID: 0xC1FD2134 Fingerprint: 294C DF90 1648 D58B C279 0C9A 46B9 7DE2 C1FD 2134 Russ Watson wrote: I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Because there are some that match the first criteria and some that match the second. You can LIST these out, like this: list filename WHEN ASD(QTY >= "1000" AND QTY <= "2000") -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russ Watson Sent: Wednesday, December 19, 2007 10:37 AM To: u2-users@listserver.u2ug.org Subject: [U2] selection of multivalued field based on range I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
Russ RetrieVe doesn't have BETWEEN but SQL does, so for clarity you can use this form: SELECT @ID TO SLIST 0 FROM yourfile WHERE (qty BETWEEN 1000 AND 2000); One of those rare times SQL is more helpful... If you're listing, rather than selecting, you can use RetrieVe substituting the WHEN clause, but that won't help you here. Brian I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] selection of multivalued field based on range
If UV, then try WHEN instead of WITH Mark --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] selection of multivalued field based on range
I want to select records to report if *one* of the multivalues falls within a range. For example: select with qty >= 1000 and =< 2000 If I use the example I get records where none of the values satisfies the range condition. @ID qty --- --- 11292,500.0 2,500.0 300.0 Russ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/