Re: [U2] [UV] Help with SQL select
Is this a limitation on I-types in SQL selects? It would seem so based on your report. What does support have to say? Has anyone come across this problem before and is there a work around? I haven't, but I haven't tried large orderded SELECTs on four I-types. Alternatively, can someone suggest another way to do this using a single ReVise statement? You said you could do it in RetrieVe; are you sure you can't use TOXML with that? I've used TOXML with LIST in 10.0.4 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Help with SQL select
Is this a limitation on I-types in SQL selects? It would seem so based on your report. What does support have to say? Seems like... VAR support is next port o' call. Has anyone come across this problem before and is there a work around? I haven't, but I haven't tried large orderded SELECTs on four I-types. Alternatively, can someone suggest another way to do this using a single ReVise statement? You said you could do it in RetrieVe; are you sure you can't use TOXML with that? I've used TOXML with LIST in 10.0.4 Yes, sure. TOXML doesn't do BREAK.ON/DET.SUP which is required for SQL DISTINCT equivalence. Ta muchly, S ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this email in error please notify the Spotless IS Support Centre (+61 3 9269 7555) immediately, who will advise further action. This footnote also confirms that this email message has been scanned for the presence of computer related viruses. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [uv] Help with SQL select
Stuart, UniVerse SQL SELECT DISTINCT and RetrieVe SELECT ... SAVING UNIQUE ... (which are semantically identical queries) need the UniVerse parameter ALLOWMARKS=1 to work with medium-large queries. This parameter is set by default = 0. There is an enhancement request at IBM for documenting this behaviour (ECase 7837), as it has been stated to be permanent in UniVerse. The DISTINCT or SAVING UNIQUE clauses make the query engine internally building a B-tree in memory, but when a certain amount of data is to be treated, this memory is flushed into a temporary B-Tree file growing with the rest of the selected field concerned. By design, the Record IDs written into this particularType 25 temporary file are containing marks, thus the problem with the ALLOWMARKS parameter = 0. Hope this will help. Regards, Hervi BALESTRIERI Support Technique Avanci - Produits U2 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [uv] Help with SQL select
Many thanks. Cheers, Stuart __ UniVerse SQL SELECT DISTINCT and RetrieVe SELECT ... SAVING UNIQUE ... (which are semantically identical queries) need the UniVerse parameter ALLOWMARKS=1 to work with medium-large queries. This parameter is set by default = 0. There is an enhancement request at IBM for documenting this behaviour (ECase 7837), as it has been stated to be permanent in UniVerse. The DISTINCT or SAVING UNIQUE clauses make the query engine internally building a B-tree in memory, but when a certain amount of data is to be treated, this memory is flushed into a temporary B-Tree file growing with the rest of the selected field concerned. By design, the Record IDs written into this particularType 25 temporary file are containing marks, thus the problem with the ALLOWMARKS parameter = 0. Hope this will help. Regards, Hervi BALESTRIERI Support Technique Avanci - Produits U2 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this email in error please notify the Spotless IS Support Centre (+61 3 9269 7555) immediately, who will advise further action. This footnote also confirms that this email message has been scanned for the presence of computer related viruses. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] [uv] Help with SQL select
Hi, Ibm trying to get a unique list from a delimited field into an XML document. If I use a SQL bDISTINCTb select it aborts half way through. In ReVise it bwouldb be SORT FILE BY COL1 BREAK.ON COL1 COL2 COL3 COL4 DET.SUP ID.SUP except that I canbt use a TOXML clause with this statement. In SQL itbs b SELECT DISTINCT COL1,COL2,COL3,COL4 FROM FILE ORDER BY COL1 TOXML;b . But Ibm finding when I run this from TCL that this stops halfway through with an b Aborting!b error. All of the COLn fields are I-types which do a field() extract on a single delimitated attribute. (eg FIELD(@RECORD2,'/',2)). If I take out one of the columns or the DISTINCT clause in the SQL statement then it selects to the end of the table. If I add columns it aborts earlier. The ORDER.BY and the TOXML clauses donbt seem to make any difference. Is this a limitation on I-types in SQL selects? Has anyone come across this problem before and is there a work around? Alternatively, can someone suggest another way to do this using a single ReVise statement? This is on UV10.0.7/AIX, in an SB+ 5.0.4 account at real TCL. Thanks, Stuart Boydell ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this email in error please notify the Spotless IS Support Centre (+61 3 9269 7555) immediately, who will advise further action. This footnote also confirms that this email message has been scanned for the presence of computer related viruses. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
