cancel
Showing results for 
Search instead for 
Did you mean: 

How do I refer to the same OPENSTRING FILE twice in one query?

Breck_Carter
Participant
2,418

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"

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member

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;