on 2012 Jul 05 6:47 PM
Update: Frum Dude's workaround is just fine, BUT... I still want confirmation that "-602 Specified database file already in use" is expected.
...and if so, it should be documented because no such restriction exists for other items referenced in a FROM clause. Folks WITHOUT years of experience abusing text file I/O in SQL Anywhere may struggle for a long time trying to "fix" this symptom.
I think the following query is choking because there are two references to the local view "v_property" in the master SELECT, and that causes two references to the same OPENSTRING FILE, and that can't be done... right?
WITH v_property AS ( SELECT * FROM OPENSTRING ( FILE 'C:/projects/foxhound/005b_rroad_master_property_list_v1200.txt' ) WITH ( property_id VARCHAR ( 100 ), property_source VARCHAR ( 100 ), priority_usage VARCHAR ( 100 ), data_type VARCHAR ( 100 ), PropName VARCHAR ( 100 ), available_in VARCHAR ( 100 ), max_id VARCHAR ( 100 ), notes LONG VARCHAR ) OPTION ( DELIMITED BY '\\x09' SKIP 1 ) AS property WHERE priority_usage = 'fast' AND property_source IN ( 'db', 'eng' ) ) SELECT v_property.\\* FROM v_property INNER JOIN ( SELECT PropName, COUNT(*) AS PropName_count FROM v_property GROUP BY PropName HAVING PropName_count >= 2 ) AS multi_property ON v_property.PropName = multi_property.PropName ORDER BY v_property.PropName, v_property.property_source; There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. Cannot access file 'C:/projects/foxhound/005b_rroad_master_property_list_v1200...' -- Specified database file already in use SQLCODE=-602, ODBC 3 State="HY000"
Request clarification before answering.
As the 'master of temp tables' ... I would suggest as follows:
SELECT * into #v_property FROM OPENSTRING ( FILE 'C:/projects/foxhound/005b_rroad_master_property_list_v1200.txt' ) WITH ( property_id VARCHAR ( 100 ), property_source VARCHAR ( 100 ), priority_usage VARCHAR ( 100 ), data_type VARCHAR ( 100 ), PropName VARCHAR ( 100 ), available_in VARCHAR ( 100 ), max_id VARCHAR ( 100 ), notes LONG VARCHAR ) OPTION ( DELIMITED BY '\\x09' SKIP 1 ) AS property WHERE priority_usage = 'fast' AND property_source IN ( 'db', 'eng' ); SELECT v_property.* FROM #v_property v_property INNER JOIN ( SELECT PropName, COUNT(*) AS PropName_count FROM #v_property v_property GROUP BY PropName HAVING PropName_count >= 2 ) AS multi_property ON v_property.PropName = multi_property.PropName ORDER BY v_property.PropName, v_property.property_source;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.