[U2] dynamically disassociate associated fields?
Hello. I have a rather large MV association that contains around 40 or so I- type dicts that all call subroutines. Since this is essentially a subtable to uv/SQL, selecting any field from the subtable causes all the fields to be processed, although only the requested field is returned. As you can imagine this slows things down a ton. Is there a way to tell universe to *just* process the field(s) requested in the SELECT stmt without creating new dicts associations? Thanks. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] UniVerse/SQL: This EVAL field requires a qualifying file name
This worked! Thanks Ray! rayw at mindless.com writes: It does wherever the column reference is or may be ambiguous (or wherever the query parser assumes it may be ambiguous!). Try this. SELECT A.field1, B.field2, EVAL B.'TESTING' AS field3 FROM table1 A, table2 B WHERE A.KeyField = B.KeyField; ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] UniVerse/SQL: This EVAL field requires a qualifying file name
Anyone ever see this error? It's happening with a SQL statement that pulls data from a table and a subtable, which is just an association. Something like this: SELECT field1,B.field2,EVAL 'TESTING' AS field3 FROM table1,table1_wtvr B WHERE table1.KeyField = B.KeyField; Thanks. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Determine i-descriptor name?
Allen Egerton aegerton at pobox.com writes: If it doesn't exist as a file system object, it's still got to exist in VOC as an F or Q pointer. Read the VOC rec, grab 2 and 3 to make decisions?? Not with a UV/SQL SELECT. @FILENAME can be loaded with an alias that doesn't exist in the VOC at all. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Determine i-descriptor name?
Anyone know if there's a way to tell what i-descriptor has called a subroutine without passing the dictionary name as a parameter? Thanks. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Determine i-descriptor name?
Allen Egerton aegerton at pobox.com writes: There's no direct way that I know. If you're lucky, @FILENAME will help you, if not, you might try parsing out @SENTENCE or @PARASENTENCE. Hey Allen. I can't use @FILENAME in any way because if an alias is used in the statement that's what @FILENAME is set to, which could be a file that doesn't exist. I'll check @PARASENTENCE though. I even checked the call stack in system(9001) but nothing useful to me is in there. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] uv SQL INSERT INTO error?
Brian Leach brian at brianleach.co.uk writes: Hi Shane Not sure exactly what the problem is, but what happens if you rewrite this as: INSERT INTO myTable (val1, val2, val2) SELECT col1, col2, col3 FROM UNNEST table ON testAssoc WHERE col2 = '10-23-10'; I know it *should* be the same, but ... Brian Hi Brian, Yes I tried the UNNEST but it produced the same error unfortunately. It's really odd, I'm not sure why it wants to see only 'stored' columns as part of the association. As of right now I'm not too concerned because the requirements have changed a bit so I won't be using that INSERT after all, but it still bugs me when something doesn't work the way I expect it to and I can't figure out why. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] uv SQL INSERT INTO error?
Hi. I'm trying to INSERT INTO a table using results from a subquery that happens to reference multivalued columns that belong to an association. I keep getting the following error: UniVerse/SQL: No 'stored' columns were found defining association Does anyone know what the problem is? The @ASSOC_KEY.name dictionary is defined in the subquery's file like so: @ASSOC_KEY.testAssoc 0001 PH 0002 key col1 col2 Although col1 col2 are both i-descriptors. Here's an example of the SQL: INSERT INTO myTable (val1,val2,val3) SELECT col1,col2,col3 FROM table_testAssoc WHERE col2 = '10-23-10'; Thanks! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] universe SQL sort speed with mv associations?
Boydell, Stuart Stuart.Boydell at spotless.com.au writes: Hmm, I just tried the 2 different selects ... order by f1,f2 ... and order by @id,f1,f2 on one of my files with 27000 items/ 15 exploded rows and there was no appreciable difference between the 2. The select took only about a 2 second delay to start returning rows to screen in both cases. (UV 10.2.4/AIX) So that makes your order by look very slow considering the number of items. Maybe have a look at your file sizing or your SQL environment (SET.SQL). Stuart Actually my SELECT with an ORDER BY also returns records to screen very quickly - the difference is when I capture all the results. For some reason the sorted SELECT will get slower and slower until it comes to a crawl. The unsorted one will return the entire result set very quickly. So however it's working, it figures out what to sort *very quickly* because it begins to return results almost immediately, but it must be performing additional work to display each record because it starts to really slow down. I've checked things like file size, indexes, etc... but nothing seems to help. Yesterday I ran a query that returned the entire result set without an ORDER BY in 12 seconds, but with the ORDER BY on 2 columns took 55 minutes! Both returned the exact same number of records. And like I said, they both return initial results to screen very quickly. Something's not right. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] universe SQL sort speed with mv associations?
John Jenkins u2guru at btinternet.com writes: What's the volume here? Adding the ORDER BY clause will cause the data to be pre-processed before the result set starts to get returned. Also, if you ignore how long it takes to *start* returning the result set, how do the two compare in returning the *complete* result set? Regards JayJay The volume is about 4000 records, that become about 2 once their multivalues are exploded. The difference in time to return the whole result set is huge: about 20 seconds vs. 5 minutes. Both are returning the same records. Just one sorts them and one doesn't. I've tried secondary indexes but there was no difference in speed. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] universe SQL sort speed with mv associations?
Hi. I have a table that contains an association of multi-valued columns. I'm issuing a SELECT against the table and referencing the columns. If I don't ORDER BY on any of the columns, the query returns data quickly (either executing from within a uv basic program and capturing output or via uniObjects command). However, as soon as I include an ORDER BY, the run-time of the query increases substantially. We're talking seconds vs. minutes. Why? Is it because of the multi-valued output data being shuffled to display correctly? Kind of like how WHEN with a BY-EXP works? The query is simple: SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_THE.ASSOC WHERE FIELD3 = '01/01/2010' ORDER BY FIELD1,FIELD2,FIELD3,FIELD4; THE.ASSOC: 0001 PH 0002 FIELD1 FIELD2 FIELD3 FIELD4 Any help is appreciated. Thanks! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users