|
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"
|
|
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;
|
