RE: [UD] Union Query
Gordon J. Glorfield wrote: In that case, I'm sorry to say, there is no way to logically do what you're asking with UD. Gordon, you should now better! ;^) sql SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE UNION SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE_OTHER; All you need here Ed is a VOC pointer to each file that is SQL compatible - ie no . dots etc. There is no need to build a schema or set up ODBC or do anything complicated in UniData just to query a file with SQL from the command line. Cheers, Ken -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
Indeed. One of the great things about UniVerse and UniData is that you can use SQL against your files. SQL is a great way to do quick updates too (carefull, it is a very sharp knife). If you don't have a good SQL book, get one. I have the advantage of being required to support a couple of Micro$oft SQL servers along with the UniVerse machines. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken Wallis Sent: Saturday, May 01, 2004 3:15 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query Gordon J. Glorfield wrote: In that case, I'm sorry to say, there is no way to logically do what you're asking with UD. Gordon, you should now better! ;^) sql SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE UNION SELECT sql NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE_OTHER; All you need here Ed is a VOC pointer to each file that is SQL compatible - ie no . dots etc. There is no need to build a schema or set up ODBC or do anything complicated in UniData just to query a file with SQL from the command line. Cheers, Ken -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
I will be the first to admit that I know next to nothing about SQL in the UV/UD world. Can you use the output of the SQL SELECT in UV/UD? Thanks. Steve At 05:14 PM 5/1/04 +1000, you wrote: Gordon J. Glorfield wrote: In that case, I'm sorry to say, there is no way to logically do what you're asking with UD. Gordon, you should now better! ;^) sql SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE UNION SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE_OTHER; All you need here Ed is a VOC pointer to each file that is SQL compatible - ie no . dots etc. There is no need to build a schema or set up ODBC or do anything complicated in UniData just to query a file with SQL from the command line. Cheers, Ken -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- Steven M Wagner [EMAIL PROTECTED] Cary, North Carolina, United States of America -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: [UD] Union Query
No, and Yes. Just doing a SELECT in SQL style on UV gives you a crt display of the result set. But if you use the SLIST extension, you can then access the select list (see System Description manual) as normal for the UV environment. -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On May 1, 2004, at 18:01, Steven M Wagner wrote: I will be the first to admit that I know next to nothing about SQL in the UV/UD world. Can you use the output of the SQL SELECT in UV/UD? Thanks. Steve At 05:14 PM 5/1/04 +1000, you wrote: Gordon J. Glorfield wrote: In that case, I'm sorry to say, there is no way to logically do what you're asking with UD. Gordon, you should now better! ;^) sql SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE UNION SELECT NAME ADDRESS CITY STATE ZIP FROM ORDER_FILE_OTHER; All you need here Ed is a VOC pointer to each file that is SQL compatible - ie no . dots etc. There is no need to build a schema or set up ODBC or do anything complicated in UniData just to query a file with SQL from the command line. Cheers, Ken -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- Steven M Wagner [EMAIL PROTECTED] Cary, North Carolina, United States of America -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
I understand how Unibasic could handle a list that has been merged, but how would you do a Uniquery statement? for example: SELECT ORDER.FILE WITH DATE 1/1/2004 SAVE-LIST LIST1 SELECT ORDER.FILE.OTHER WITH DATE 1/1/2004 SAVE-LIST LIST2 GET-LIST LIST1 TO 1 GET-LIST LIST2 TO 2 MERGE.LIST 1 UNION 2 SAVE-LIST LIST.ALL GET-LIST LIST.ALL What do you enter here? LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Baakkonen, Rodney [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:27 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query You could use MERGER.LIST to get one list which is a UNION of two separate selects of each file. But you would not know which file the key was from when you were done. But Unibasic or Virtual dictionary items could take the merged list and produce what you are looking for. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 9:13 AM To: 'U2 Users Discussion List' Subject: [UD] Union Query Hello, Does anyone know of a way to do the equivalent of a UNION query (really a LIST or SORT) on 2 or more different UniData files? I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Thanks in advance. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
My suggestion is to create a V type dictionary item(s) that with point to your other file. For example, if you want to look at the city name in your file, ORDER.FILE.OTHER, create the following: 001: V 002: TRANS('ORDER.FILE.OTHER', @ID, location of city in ORDER.FILE.OTHER', 'X') 003: 004: City (ORDER.FILE.OTHER) 005: 30L 006: S Also, I recommend that you check out the capabilities of TRANS. There are other return codes. Hope this helps. Grant W. Boice, Jr. Systems Administrator Benchmark Electronics, Inc. Manassas Division 8500 Phoenix Drive Manassas, VA 20110 Phone: (703) 334-0156 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web:www.bench.com http://www.bench.com -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:05 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query I understand how Unibasic could handle a list that has been merged, but how would you do a Uniquery statement? for example: SELECT ORDER.FILE WITH DATE 1/1/2004 SAVE-LIST LIST1 SELECT ORDER.FILE.OTHER WITH DATE 1/1/2004 SAVE-LIST LIST2 GET-LIST LIST1 TO 1 GET-LIST LIST2 TO 2 MERGE.LIST 1 UNION 2 SAVE-LIST LIST.ALL GET-LIST LIST.ALL What do you enter here? LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Baakkonen, Rodney [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:27 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query You could use MERGER.LIST to get one list which is a UNION of two separate selects of each file. But you would not know which file the key was from when you were done. But Unibasic or Virtual dictionary items could take the merged list and produce what you are looking for. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 9:13 AM To: 'U2 Users Discussion List' Subject: [UD] Union Query Hello, Does anyone know of a way to do the equivalent of a UNION query (really a LIST or SORT) on 2 or more different UniData files? I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Thanks in advance. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
@ID in this case points to the filename mentioned in my statement: LIST ORDER.FILE the @ID's from ORDER.FILE.OTHER are showing up as non-existent: (My merged list contains 10 items from ORDER.FILE and 10 items from ORDER.FILE.OTHER) ORDER.FILE NAME. Name.OTHER.. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:18 AM To: [EMAIL PROTECTED] Subject: RE: [UD] Union Query My suggestion is to create a V type dictionary item(s) that with point to your other file. For example, if you want to look at the city name in your file, ORDER.FILE.OTHER, create the following: 001: V 002: TRANS('ORDER.FILE.OTHER', @ID, location of city in ORDER.FILE.OTHER', 'X') 003: 004: City (ORDER.FILE.OTHER) 005: 30L 006: S Also, I recommend that you check out the capabilities of TRANS. There are other return codes. Hope this helps. Grant W. Boice, Jr. Systems Administrator Benchmark Electronics, Inc. Manassas Division 8500 Phoenix Drive Manassas, VA 20110 Phone: (703) 334-0156 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web:www.bench.com http://www.bench.com -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:05 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query I understand how Unibasic could handle a list that has been merged, but how would you do a Uniquery statement? for example: SELECT ORDER.FILE WITH DATE 1/1/2004 SAVE-LIST LIST1 SELECT ORDER.FILE.OTHER WITH DATE 1/1/2004 SAVE-LIST LIST2 GET-LIST LIST1 TO 1 GET-LIST LIST2 TO 2 MERGE.LIST 1 UNION 2 SAVE-LIST LIST.ALL GET-LIST LIST.ALL What do you enter here? LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Baakkonen, Rodney [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:27 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query You could use MERGER.LIST to get one list which is a UNION of two separate selects of each file. But you would not know which file the key was from when you were done. But Unibasic or Virtual dictionary items could take the merged list and produce what you are looking for. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 9:13 AM To: 'U2 Users Discussion List' Subject: [UD] Union Query Hello, Does anyone know of a way to do the equivalent of a UNION query (really a LIST or SORT) on 2 or more different UniData files? I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Thanks in advance. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
In that case, I'm sorry to say, there is no way to logically do what you're asking with UD. You could physically do it by creating a temp file and copy all records from both files to it. But that would be a great deal of overhead for a query. Gordon J. Glorfield Sr. Applications Developer MAMSI (A UnitedHealth Company) 301-360-8839 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burwell, Edward Sent: Friday, April 30, 2004 11:24 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query No, a record ID in one file will not exist in the other file. -Original Message- From: Jeff Schasny [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:14 AM To: U2 Users Discussion List Subject: RE: [UD] Union Query TCL (or whatever your flavor calls the queery language) is a single file oriented language. No joins. You would need to create TRANS items in the dictionary of one of the files pointing to the other. Are the record ID's in both files the same for a given record that you would want to relate? -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 8:05 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query I understand how Unibasic could handle a list that has been merged, but how would you do a Uniquery statement? for example: SELECT ORDER.FILE WITH DATE 1/1/2004 SAVE-LIST LIST1 SELECT ORDER.FILE.OTHER WITH DATE 1/1/2004 SAVE-LIST LIST2 GET-LIST LIST1 TO 1 GET-LIST LIST2 TO 2 MERGE.LIST 1 UNION 2 SAVE-LIST LIST.ALL GET-LIST LIST.ALL What do you enter here? LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Baakkonen, Rodney [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:27 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query You could use MERGER.LIST to get one list which is a UNION of two separate selects of each file. But you would not know which file the key was from when you were done. But Unibasic or Virtual dictionary items could take the merged list and produce what you are looking for. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 9:13 AM To: 'U2 Users Discussion List' Subject: [UD] Union Query Hello, Does anyone know of a way to do the equivalent of a UNION query (really a LIST or SORT) on 2 or more different UniData files? I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Thanks in advance. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
If you really must use TCL (as opposed to writing some code) you could copy the data from both files to a single temp file and report from it. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 8:24 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query No, a record ID in one file will not exist in the other file. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Memo: RE: [UD] Union Query
R. Bruce, Could you give an example? I'm having trouble envisioning this. Let's say I have these keys in a list: LOCAL.1 LOCAL.2 LOCAL.3 OTHER.1 OTHER.2 OTHER.3 The first 3 live in FILEA and the last 3 live in FILEB If I go a GET-LIST, then enter: LIST FILEA NAME What would the NAME dictionary look like? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Bruce Lunt [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 12:26 PM To: [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query What I have done in the past is concatenate the 2 translates together. One trans being blank and the other having valid data. You need to make a whole set of dicts that do the double-reads in one file-dict or the other. Then, use it as your primary file for the listing. The only problem is when the data exists in both files. But at least then you will get twice the data. It stands out pretty well if you want to spot problems. HTH R. Bruce Lunt 408.832.1900 cell From: [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: U2 Users Discussion List [EMAIL PROTECTED] Subject: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 16:49:46 +0100 Hi Ed, You could test for the record existing in one file and if it does use the dict for that file otherwise use the dict for the other file: TRANS(FILE.A,ID,1,X); IF @1 '' THEN FILEA.NAME ELSE FILEB.NAME where FILEA.NAME returns the name from FILE.A and FILEB.NAME returns the name for FILE.B this is all off the top of my head, but it should work. hth asvin Burwell, Edward [EMAIL PROTECTED] on 30 Apr 2004 16:33 Please respond to U2 Users Discussion List [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:'U2 Users Discussion List' [EMAIL PROTECTED] cc: bcc: Subject:RE: [UD] Union Query @ID in this case points to the filename mentioned in my statement: LIST ORDER.FILE the @ID's from ORDER.FILE.OTHER are showing up as non-existent: (My merged list contains 10 items from ORDER.FILE and 10 items from ORDER.FILE.OTHER) ORDER.FILE NAME. Name.OTHER.. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:18 AM To: [EMAIL PROTECTED] Subject: RE: [UD] Union Query My suggestion is to create a V type dictionary item(s) that with point to your other file. For example, if you want to look at the city name in your file, ORDER.FILE.OTHER, create the following: 001: V 002: TRANS('ORDER.FILE.OTHER', @ID, location of city in ORDER.FILE.OTHER', 'X') 003: 004: City (ORDER.FILE.OTHER) 005: 30L 006: S Also, I recommend that you check out the capabilities of TRANS. There are other return codes. Hope this helps. Grant W. Boice, Jr. Systems Administrator Benchmark Electronics, Inc. Manassas Division 8500 Phoenix Drive Manassas, VA 20110 Phone: (703) 334-0156 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web:www.bench.com http://www.bench.com -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:05 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query I understand how Unibasic could handle a list that has been merged, but how would you do a Uniquery statement? for example: SELECT ORDER.FILE WITH DATE 1/1/2004 SAVE-LIST LIST1 SELECT ORDER.FILE.OTHER WITH DATE 1/1/2004 SAVE-LIST LIST2 GET-LIST LIST1 TO 1 GET-LIST LIST2 TO 2 MERGE.LIST 1 UNION 2 SAVE-LIST LIST.ALL GET-LIST LIST.ALL What do you enter here? LIST ORDER.FILE NAME ADDRESS CITY STATE ZIP or LIST ORDER.FILE.OTHER NAME ADDRESS CITY STATE ZIP How would a virtual dictionary know which file to get the NAME ADDRESS CITY and STATE from? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Baakkonen, Rodney [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:27 AM To: 'U2 Users Discussion List' Subject: RE: [UD] Union Query You could use MERGER.LIST to get one list which is a UNION of two separate selects of each file. But you would not know which file the key was from when you were done. But Unibasic or Virtual dictionary items could take the merged list and produce what you are looking for. -Original Message- From: Burwell, Edward [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 9:13 AM To: 'U2 Users Discussion List' Subject: [UD] Union Query Hello
Re: Memo: RE: [UD] Union Query
From: Burwell, Edward [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Sent: Friday, April 30, 2004 1:41 PM Subject: RE: Memo: RE: [UD] Union Query R. Bruce, Could you give an example? I'm having trouble envisioning this. Let's say I have these keys in a list: LOCAL.1 LOCAL.2 LOCAL.3 OTHER.1 OTHER.2 OTHER.3 The first 3 live in FILEA and the last 3 live in FILEB If I go a GET-LIST, then enter: LIST FILEA NAME What would the NAME dictionary look like? Based on what he said, I'd guess something like this: 0 NAME 1 I 2 TRANS(FILEA, @ID, NAME, ): TRANS(FILEB, @ID, NAME, ) -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Memo: RE: [UD] Union Query
Does that work for you? It doesn't work for me. ORDER.FILE NAME. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Here is the dict: 001: I 002: TRANS(ORDER.FILE,@ID,3,):TRANS(ORDER.FILE.CAN,@ID,3,) 003: 004: NAME 005: 25L 006: S -Original Message- From: Allen Egerton [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 1:47 PM To: U2 Users Discussion List Subject: Re: Memo: RE: [UD] Union Query From: Burwell, Edward [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Sent: Friday, April 30, 2004 1:41 PM Subject: RE: Memo: RE: [UD] Union Query R. Bruce, Could you give an example? I'm having trouble envisioning this. Let's say I have these keys in a list: LOCAL.1 LOCAL.2 LOCAL.3 OTHER.1 OTHER.2 OTHER.3 The first 3 live in FILEA and the last 3 live in FILEB If I go a GET-LIST, then enter: LIST FILEA NAME What would the NAME dictionary look like? Based on what he said, I'd guess something like this: 0 NAME 1 I 2 TRANS(FILEA, @ID, NAME, ): TRANS(FILEB, @ID, NAME, ) -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: [UD] Union Query
In a message dated 4/30/2004 10:12:57 AM Eastern Daylight Time, [EMAIL PROTECTED] writes: I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Is there any logic in why an id would be in ORDER.FILE versus ORDER.FILE.OTHER ? If so, maybe you could setup a distributed file so that it points at both of these files? I thought I had read of someone doing something like that. Will -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UD] Union Query
What is a Distributed File? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:35 PM To: [EMAIL PROTECTED] Subject: Re: [UD] Union Query In a message dated 4/30/2004 10:12:57 AM Eastern Daylight Time, [EMAIL PROTECTED] writes: I have 2 files in separate accounts that are structured the same. I want to do something like: SORT ORDER.FILE AND ORDER.FILE.OTHER WITH DATE 01/01/2004 NAME ADDRESS CITY STATE ZIP Is there any logic in why an id would be in ORDER.FILE versus ORDER.FILE.OTHER ? If so, maybe you could setup a distributed file so that it points at both of these files? I thought I had read of someone doing something like that. Will -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: Memo: RE: [UD] Union Query
From: Burwell, Edward [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:00 PM Subject: RE: Memo: RE: [UD] Union Query Does that work for you? It doesn't work for me. ORDER.FILE NAME. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Here is the dict: 001: I 002: TRANS(ORDER.FILE,@ID,3,):TRANS(ORDER.FILE.CAN,@ID,3,) 003: 004: NAME 005: 25L 006: S I can't test it right now, I don't have access to a Universe machine until tonight. Try an ID.SUP, 'cause the ids for ORDER.FILE.CAN certainly aren't in ORDER.FILE. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Memo: RE: [UD] Union Query
In the dict of FILE.A create a dict called: NAME.LO that has: TRANS(LOCAL,ID,1,X):TRANS(OTHER,ID,1,X) What you get is a boolean result by sticking the two TRANS against each other. Let's look an example. Assume that the 6 records have the following names: LOCAL.1 = Smith LOCAL.2 = Jones LOCAL.3 = Barney OTHER.1= Green OTHER.2= Brown OTHER.3= Jones If you LIST LOCAL NAME NAME.LO you should see the following: FILE.A... NAME... NAME.LO. LOCAL.1 Smith Smith LOCAL.2 Jones Jones LOCAL.3 BarneyBarney OTHER.1 Green OTHER.2 Brown OTHER.3 Jones This is because the NAME.LO dict doesn't care where it is residing. It just gets the direction to retrieve the contents of attribute 1 from the LOCAL and OTHER files. In fact, to keep the confusion (and clutter) to a minimum you could put the dict into a work file dictionary and do: LIST FILE.A USING DICT WORK NAME.LO When I mentioned the data being in both files I meant having the same key. e.g. LOCAL.3 existing in both LOCAL and OTHER files. Does that make sense? R. Bruce Lunt 408.832.1900 cell From: Burwell, Edward [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 13:41:26 -0400 R. Bruce, Could you give an example? I'm having trouble envisioning this. Let's say I have these keys in a list: LOCAL.1 LOCAL.2 LOCAL.3 OTHER.1 OTHER.2 OTHER.3 The first 3 live in FILEA and the last 3 live in FILEB If I go a GET-LIST, then enter: LIST FILEA NAME What would the NAME dictionary look like? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Bruce Lunt [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 12:26 PM To: [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query What I have done in the past is concatenate the 2 translates together. One trans being blank and the other having valid data. You need to make a whole set of dicts that do the double-reads in one file-dict or the other. Then, use it as your primary file for the listing. The only problem is when the data exists in both files. But at least then you will get twice the data. It stands out pretty well if you want to spot problems. HTH R. Bruce Lunt 408.832.1900 cell From: [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: U2 Users Discussion List [EMAIL PROTECTED] Subject: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 16:49:46 +0100 Hi Ed, You could test for the record existing in one file and if it does use the dict for that file otherwise use the dict for the other file: TRANS(FILE.A,ID,1,X); IF @1 '' THEN FILEA.NAME ELSE FILEB.NAME where FILEA.NAME returns the name from FILE.A and FILEB.NAME returns the name for FILE.B this is all off the top of my head, but it should work. hth asvin Burwell, Edward [EMAIL PROTECTED] on 30 Apr 2004 16:33 Please respond to U2 Users Discussion List [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:'U2 Users Discussion List' [EMAIL PROTECTED] cc: bcc: Subject:RE: [UD] Union Query @ID in this case points to the filename mentioned in my statement: LIST ORDER.FILE the @ID's from ORDER.FILE.OTHER are showing up as non-existent: (My merged list contains 10 items from ORDER.FILE and 10 items from ORDER.FILE.OTHER) ORDER.FILE NAME. Name.OTHER.. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 11:18 AM To: [EMAIL PROTECTED] Subject: RE: [UD] Union Query My suggestion is to create a V type dictionary item(s) that with point to your other file. For example, if you want to look at the city name in your file, ORDER.FILE.OTHER, create the following: 001: V 002: TRANS('ORDER.FILE.OTHER', @ID, location of city in ORDER.FILE.OTHER', 'X') 003: 004: City (ORDER.FILE.OTHER) 005: 30L 006: S Also, I recommend that you check out the capabilities of TRANS. There are other return codes. Hope this helps. Grant W. Boice, Jr. Systems Administrator Benchmark Electronics, Inc. Manassas Division 8500 Phoenix Drive Manassas, VA 20110 Phone: (703) 334-0156 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web:www.bench.com http://www.bench.com -Original Message- From: Burwell, Edward [mailto:[EMAIL
RE: Memo: RE: [UD] Union Query
Are you on Universe or Unidata? I'm on Unidata 5.2 - BASICTYPE 'p', ECLTYPE 'p'. This is not working for me. When I get a list (6 items let's say) and type LIST SOME.FILE NAME.LO It seems that Unidata is taking the 6 items in the list and trying to find them in SOME.FILE first - *THEN* runs the NAME.LO dictionary. It looks to me that your example works because your system is running the NAME.LO dictionary *BEFORE* it tries to resolve the key in the list to a record in SOME.FILE. Therefore, you should be able to to something like this: SELECT SOME.FILE SAMPLE 6 SAVE-LIST SOME.LIST GET-LIST SOME.LIST LIST TOTALLY.DIFFERENT.FILE NAME.LO if the NAME.LO dict did a TRANS to SOME.FILE, then the above would work just fine - even though every key in the SOME.LIST did NOT EXIST in TOTALLY.DIFFERENT.FILE. I appreciate your help on this. Thanks. :-) Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Bruce Lunt [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 3:59 PM To: [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query In the dict of FILE.A create a dict called: NAME.LO that has: TRANS(LOCAL,ID,1,X):TRANS(OTHER,ID,1,X) What you get is a boolean result by sticking the two TRANS against each other. Let's look an example. Assume that the 6 records have the following names: LOCAL.1 = Smith LOCAL.2 = Jones LOCAL.3 = Barney OTHER.1= Green OTHER.2= Brown OTHER.3= Jones If you LIST LOCAL NAME NAME.LO you should see the following: FILE.A... NAME... NAME.LO. LOCAL.1 Smith Smith LOCAL.2 Jones Jones LOCAL.3 BarneyBarney OTHER.1 Green OTHER.2 Brown OTHER.3 Jones This is because the NAME.LO dict doesn't care where it is residing. It just gets the direction to retrieve the contents of attribute 1 from the LOCAL and OTHER files. In fact, to keep the confusion (and clutter) to a minimum you could put the dict into a work file dictionary and do: LIST FILE.A USING DICT WORK NAME.LO When I mentioned the data being in both files I meant having the same key. e.g. LOCAL.3 existing in both LOCAL and OTHER files. Does that make sense? R. Bruce Lunt 408.832.1900 cell From: Burwell, Edward [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 13:41:26 -0400 R. Bruce, Could you give an example? I'm having trouble envisioning this. Let's say I have these keys in a list: LOCAL.1 LOCAL.2 LOCAL.3 OTHER.1 OTHER.2 OTHER.3 The first 3 live in FILEA and the last 3 live in FILEB If I go a GET-LIST, then enter: LIST FILEA NAME What would the NAME dictionary look like? Thanks. Ed Burwell [EMAIL PROTECTED] 973.361.5400 x1512 -Original Message- From: Bruce Lunt [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 12:26 PM To: [EMAIL PROTECTED] Subject: RE: Memo: RE: [UD] Union Query What I have done in the past is concatenate the 2 translates together. One trans being blank and the other having valid data. You need to make a whole set of dicts that do the double-reads in one file-dict or the other. Then, use it as your primary file for the listing. The only problem is when the data exists in both files. But at least then you will get twice the data. It stands out pretty well if you want to spot problems. HTH R. Bruce Lunt 408.832.1900 cell From: [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: U2 Users Discussion List [EMAIL PROTECTED] Subject: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 16:49:46 +0100 Hi Ed, You could test for the record existing in one file and if it does use the dict for that file otherwise use the dict for the other file: TRANS(FILE.A,ID,1,X); IF @1 '' THEN FILEA.NAME ELSE FILEB.NAME where FILEA.NAME returns the name from FILE.A and FILEB.NAME returns the name for FILE.B this is all off the top of my head, but it should work. hth asvin Burwell, Edward [EMAIL PROTECTED] on 30 Apr 2004 16:33 Please respond to U2 Users Discussion List [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:'U2 Users Discussion List' [EMAIL PROTECTED] cc: bcc: Subject:RE: [UD] Union Query @ID in this case points to the filename mentioned in my statement: LIST ORDER.FILE the @ID's from ORDER.FILE.OTHER are showing up as non-existent: (My merged list contains 10 items from ORDER.FILE and 10 items from ORDER.FILE.OTHER) ORDER.FILE NAME. Name.OTHER.. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC
Re: Memo: RE: [UD] Union Query
Open mouth, ... insert foot. Or whatever :) I'm sorry, I spoke too quickly. The ID's are needed in the primary file. I would copy all the ID's to a work file and then TRANS-ing the NAME.LO, to ORDER and ORDER.CAN will work OK. Of course, if you do have the same ID in both files you have to decide what do to do with that. Again, sorry for not testing out my solution before I offered it. Regards, Bruce From: Allen Egerton [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: U2 Users Discussion List [EMAIL PROTECTED] Subject: Re: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 14:41:17 -0400 From: Burwell, Edward [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:00 PM Subject: RE: Memo: RE: [UD] Union Query Does that work for you? It doesn't work for me. ORDER.FILE NAME. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Here is the dict: 001: I 002: TRANS(ORDER.FILE,@ID,3,):TRANS(ORDER.FILE.CAN,@ID,3,) 003: 004: NAME 005: 25L 006: S I can't test it right now, I don't have access to a Universe machine until tonight. Try an ID.SUP, 'cause the ids for ORDER.FILE.CAN certainly aren't in ORDER.FILE. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users _ MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://toolbar.msn.com/go/onm00200413ave/direct/01/ -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: Memo: RE: [UD] Union Query
My 2 cents on this topic and maybe it'll trigger a UD/UV response. D3 allows you to GET-LIST A-LIST B-LIST and you get them combined for your next process. Could this work for this person by pre-qualifying the 2 separate SELECT statements into 2 lists, blah. blah, blah. I'm interested to know as well. Thanks. - Original Message - From: Bruce Lunt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 30, 2004 10:11 PM Subject: Re: Memo: RE: [UD] Union Query Open mouth, ... insert foot. Or whatever :) I'm sorry, I spoke too quickly. The ID's are needed in the primary file. I would copy all the ID's to a work file and then TRANS-ing the NAME.LO, to ORDER and ORDER.CAN will work OK. Of course, if you do have the same ID in both files you have to decide what do to do with that. Again, sorry for not testing out my solution before I offered it. Regards, Bruce From: Allen Egerton [EMAIL PROTECTED] Reply-To: U2 Users Discussion List [EMAIL PROTECTED] To: U2 Users Discussion List [EMAIL PROTECTED] Subject: Re: Memo: RE: [UD] Union Query Date: Fri, 30 Apr 2004 14:41:17 -0400 From: Burwell, Edward [EMAIL PROTECTED] To: 'U2 Users Discussion List' [EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:00 PM Subject: RE: Memo: RE: [UD] Union Query Does that work for you? It doesn't work for me. ORDER.FILE NAME. 114061 C.M.O.S. INC. *ASC* 114062 AMERICAN PERFIT XSTAL 18 TIMELAND COMPANY 19 MCCANN ELECTRONICS *STR* 130395 TERMINAL TECH SERVICE 138562 TOWN VISION ELECTRONICS 154897 CASIO SERV CENTER *ASC* 163063 CASIO SERV CENTER *ASC* 171230 AMERICAN PERFIT XSTAL 195731 ELECT MUSIC INSTR *FAS* 10 records listed Enter CR to print non exist record ids 007890 007891 007892 007893 007894 016057 016058 016059 016060 016061 Here is the dict: 001: I 002: TRANS(ORDER.FILE,@ID,3,):TRANS(ORDER.FILE.CAN,@ID,3,) 003: 004: NAME 005: 25L 006: S I can't test it right now, I don't have access to a Universe machine until tonight. Try an ID.SUP, 'cause the ids for ORDER.FILE.CAN certainly aren't in ORDER.FILE. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users _ MSN Toolbar provides one-click access to Hotmail from any Web page - FREE download! http://toolbar.msn.com/go/onm00200413ave/direct/01/ -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: Memo: RE: [UD] Union Query
In a message dated 4/30/2004 10:11:05 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: 001: I 002: TRANS(ORDER.FILE,@ID,3,):TRANS(ORDER.FILE.CAN,@ID,3,) 003: 004: NAME 005: 25L 006: S But could'nt you do something like IF TRANS(ORDER.FILE,@ID,3,) = THEN TRANS(ORDER.FILE.CAN,@ID,3,) or something of that nature? Will -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users